Trigger problem

[Sylvor]

Hi anyone

I have something interesting (annoying) going on with my trigger on SQL2K, I've noticed several threads on this in the past, but no resolution for the posters.

I have a simple insert statement which puts a few fields into a table on a linked server.

INSERT OPENQUERY(DBODBC, 'SELECT * FROM STOCK') values ('StockCode', 111, 'Reference')

This code works fine from SQL query analyzer but fails when run as a trigger.

CREATE TRIGGER HN_TRAN_STKMOVE
ON STK_MOVEMENTS
AFTER INSERT AS
INSERT OPENQUERY(DBODBC, 'SELECT * FROM STOCK') values ('StockCode', 111, 'Reference')

The error I get is "The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction"

Anybody got any ideas?
Thanks as always!
David

Dave Bennett

 

[dbomrrsm]

hope this helps:

http://www.dbforums.com/t953439.html

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Sylvor]

Hiya DB,

Thanks for the link, I already have the reg key that the final poster suggests, although to be honest I have no idea of the relevence of that key.

I also have the DTC service running as I should have, I simply cannot understand the difference between running the query in QA and running it via the trigger.

I am searching the net / other forums / awaiting a response from support (on the other db) but can't find anything positive yet

Thanks for replying though - if you have any other ideas let me know!

Many thanks,
David

Dave Bennett