September 11, 2006 at 5:59 am
Hi All
We have 4 servers. One with SQL Server 2005 and other 3 with SQL Server 2000 machines. When ever there is a trigger between SQL server 2000 and 2005 the SQL transact statement never commits and the database objects in SQL server 2000 gets locked because of this trigger is anyone aware of why there is such a problem and whats the solution to get rid of this.
Many thanks in advance.
Thanks
Sugesh Kumar.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
September 11, 2006 at 10:18 am
Hi Sugesh..
It sounds as though you're making a linked server call in your trigger. This is very dangerous, as the local DML action that fired the trigger (INSERT or UPDATE) will not finish until the trigger code has completed.
A preferable approach might be to have the trigger populate a local table, and have a scheduled job run against the table and make the linked server call.
Hope this helps!
- Ward Pond
blogs.technet.com/wardpond
October 17, 2006 at 8:26 am
Hi all
I have solved this problem. The problem was not with SQL Server but was with WINDOWS 2003 DISTRIBUTED TRANSACTION CO-ORDINATOR. It was set to required password more and onmce i changed it to no authentication mode everything started to work fine.
Thanks
Sugesh
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply