August 30, 2011 at 12:38 am
A SQL 2005 server has 2 instances. I am using an Stored proc which has an INSERT query (with trigger, which inserts into 2nd instance). Now, am getting the below message. This query was running fine yesterday.
(1 row affected)
Msg 8525, Level 16, State 1, Line 1
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
When i run the individual queries inside the trigger (i.e., an insert query) from 1st instance, it runs fine.
any ideas?
August 30, 2011 at 3:23 pm
August 31, 2011 at 1:58 pm
This is a good example of whey it isn't a good idea to do any sort of linked server activities inside a trigger. It would be easier to maintain if the trigger inserted into some sort of queue table that was then processed by another job. You could also check out the Service Broker.
Todd Fifield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply