March 13, 2009 at 10:26 am
We have implemented logic in our programs schedules table that when we add any schedule, a trigger fires on that table and add a record on another server's database table. I am getting following error. I tried all microsoft links but not working.
Server: Msg 7391, Level 16, State 1, Line 2
The 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].
Few months ago i have implemented this trigger and it was working fine. After windows update its not working. Don't know wether this is good idea to use it in trigger or find some other solution for that. because i found trigger the only bet place for real time add and updates to other server's database table.
I have all options tried as mentioned in:
http://support.microsoft.com/kb/839279.
http://support.microsoft.com/kb/329332
http://support.microsoft.com/?kbid=873160
All i tried but no luck
I have windows Server 2003 R2 - SP2 with SQL Server 2000 SP4 (windows up to date) on both servers.
Please help. also if there is any other way to add other server's database table with some other logic - let me know.
Shamshad Ali
March 13, 2009 at 4:06 pm
Have a read at this ...
http://sqllearnings.blogspot.com/search/label/Distributed%20Transaction%20Coordinator%20(MSDTC)
Might be similar solution ...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 16, 2009 at 6:44 am
I would recommend against doing the cross-server update in a trigger because triggers fire within the scope of the initial transaction so a failed trigger operation rolls back the entire transaction.
While not exactly real-time, I'd look into a service broker solution to this problem. This way you are using an asynchronous process that you can control and insure success.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply