I am facing a problem while executing a trigger between 2 Linked SQL Servers. I am able to do Select, Insert, call stored procedures in a linked server. But triggers are not getting executed.
The scenario is as follows:
There are 2 servers (A,
(with win 2003) both having SQL Servers 2000.
Server A has a database called TestDBA with a table called TestA. This contains a trigger on insert, which inserts a record to the table TestB.
Server B has a database called TestDBB with a table called TestB.
I have created a link between these 2 servers using sp_addlinkedserver stored procedure. So the connection is established.
But the trigger is not getting executed. I am getting an error like The operation cannot be performed because the OLE DB Provider MSDASQL was unable to begin a distributed transaction.
But i am able to run a select, insert,.. statements between these 2 servers. Also was able to execute a stored procedure of TestB from TestA.
Thanks for any help