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)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 ??

Linked server

Hi
I want to use the linked server. Can anyone write me the
step by step method to configure the Linked server.
Scenario:
PC1:
Server Name :DBServer1
DB Name : Customer (MS SQL 2000)
userid: guest
Pwd: guest
PC2:
Server Name :DBServer2
DB Name : Vendors (MS SQL 2000)
userid: guest
Pwd: guest
1. When I use th Enterprise Manager what are the settings
to be done in PC1 and PC2 for configuring the Linked
server.
2. Any pointer for the good web site ..?
3. Also is there a way that I can avoid using the four
part name like
linked_server_name.catalog.schema.object_name using any
global variable or namespace kind of thing....'?
Please help...
Thanks in advance.
Anand.Hi Hari
Can you send it to gurusanand1@.sifymail.com and not to the
news group.
B'coz till now I can't find your attachment.
Thanks in advance.
Anand.
>--Original Message--
>Hi Anand,
>Attached the step by step method to configure linked
server.
>Thanks
>Hari - MCDBA
>US Software
>Trivandrum
>
>"Anand" <gurusanand1@.sifymail.com> wrote in message
>news:0b6501c3567f$a691e380$a101280a@.phx.gbl...
>> Hi
>> I want to use the linked server. Can anyone write me the
>> step by step method to configure the Linked server.
>> Scenario:
>> PC1:
>> Server Name :DBServer1
>> DB Name : Customer (MS SQL 2000)
>> userid: guest
>> Pwd: guest
>> PC2:
>> Server Name :DBServer2
>> DB Name : Vendors (MS SQL 2000)
>> userid: guest
>> Pwd: guest
>> 1. When I use th Enterprise Manager what are the
settings
>> to be done in PC1 and PC2 for configuring the Linked
>> server.
>> 2. Any pointer for the good web site ..?
>> 3. Also is there a way that I can avoid using the four
>> part name like
>> linked_server_name.catalog.schema.object_name using any
>> global variable or namespace kind of thing....'?
>>
>> Please help...
>> Thanks in advance.
>> Anand.
>>
>
>