March 16, 2010 at 2:20 pm
I am receiving the following message:
"Transaction context in use by another session"
I know what I'm doing is probably wrong but I'm not sure how to get around it.
Server_A
Database_A
Table_A
Column user_id
Server_B
Database_B
Table_B
Column user_id
When a user is deleted from Table_B, delete the user from Table_A.
I created a trigger to do this.
When a user is deleted from Table_A, delete the user from Table_B.
I created a trigger to do this.
March 16, 2010 at 2:27 pm
These servers are not local to each other? Also how do you not guarantee that they don't loop back to each other until there are issues? Also, does it need to occur immediately. Have you considered either service broker or replication?
CEWII
March 17, 2010 at 2:46 am
Remote server calls from a trigger should be avoided at all costs. The trigger runs inside an implicit transaction, which has to escalate to a distributed transaction if it involves a remote call. The change that caused the trigger to fire will not complete until the trigger does - and if the remote server is unavailable for any reason...well, you get the idea.
If you genuinely need to allow changes on both servers, and consolidate the result to each, use merge replication.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply