Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stoal
Starting Member
2 Posts |
 Posted - 2004-09-14 : 06:07:45
|
| Hi all,I have a very strange problem(at least to me it is) using linked servers:I want every insert in one table(tab_A_on_A) to trigger a stored procedure in another db. When using Query Analyzer I can start the remote stored procedure but as soon as I make the call in a trigger I get the following err:Server: Msg 7391, Level 16, State 1, Procedure tr_on_tab_A_on_A, Line 5The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].On the second table(tab_B_on_B) there is a trigger that perfoms some input-checking but I tried removing it with the same result as before.One thing I noticed was that the B-db returns NULL for SELECT @@SERVERNAME and EXEC sp_addserver @server='B' ,@local='LOCAL' ,@duplicate_ok='duplicate_OK' doesn't help.I tried adding the linked server in two different ways: through Enterprise Manager and by the sp_addlinkedserver stored procedure.Another thing that seemed pretty strange is that after linking the servers(sp_addlinkedserver) I can perform SELECT, UPDATE and DELETE queries but no INSERTS(a cursor conflict). I fixed this by making a stored procedure that does the INSERT and I can call this proc from Query Analyzer but not from a trigger.What can be the problem here? |
|
|
n/a
deleted
35 Posts |
Posted - 2004-09-14 : 09:06:29
|
| Is your Distributed Transaction Coordinator started on both servers? |
 |
|
|
n/a
deleted
35 Posts |
Posted - 2004-09-14 : 09:06:34
|
| Is your Distributed Transaction Coordinator started on both servers? |
|
|
|
stoal
Starting Member
2 Posts |
Posted - 2004-09-14 : 09:49:30
|
| They are, double-checked.I don't really see why this is a distributed transaction because calling the stored procedure directly(not from trigger-code) works fine. |
|
|
|
|
|
|
Linked servers, trigger problem (err 7391)