Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Copenhagen, Denmark

    Post Unanswered: Linked Server - Trigger problem

    Hi !

    I have a problem, which i have dealt with a long time now. The scenario is that I have 2 SQL Server's (one 2K, the other 7). There are on both Servers a table (with users in it). In both tables on both servers, there are triggers (which fires on respectively : INSERT, DELETE and UPDATE).
    If for example I add a new user on the SQL Server 2K, a new user with same properties are to be added on the other SQL Server. Very straight forward ! The 2 Servers are connected via "Linked Servers". In that way they each hold a reference to the other SQL Server. I have turned off both "recursive triggers" and "indirect recursive triggers". BUT, when I insert a new user, I get an error from MS DTC, that "Transaction context in use by another session. [OLE/DB provider returned message: Unspecified error]". I cannot get by this error. It is because the first trigger insert on the other table that again fires a trigger. This I can handle, but MS DTC cannot, because it fires a new transaction with the first one..... No can do, Microsoft says !!!!

    Is there a bright head that have a solution for this one. And yes I know it not a very clever solution the one stated above, but under the circumstances it is the only thing we can do, without getting out in the middleware forest !!! (not a nice thing)
    Last edited by cynosure; 08-19-02 at 10:02.

  2. #2
    Join Date
    Aug 2002
    what a night mare!!

    Don't know if this will help but could both triggers call just one stored procedure that sits on one machine that does the actual insert ??

    Alternativley can you use replication ??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts