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)