hankjones11@yahoo.com (Hank) wrote in message news:<39d0f2b4.0311061539.42f7a569@posting.google. com>...
> I have two SQL Server 2000 machines (server_A and server_B). I've
> used sp_addlinkedserver to link them both, the link seems to behave
> fine. I can execute remote queries and do all types of neat things
> from one while logged onto the other.
>
> I'm working on a project to keep the data in the two systems
> synchronized, so I'm using triggers on both sides to update each
> other. For testing, I've created a simple, one-column table on both
> servers, and also created a trigger on both tables. Consider the
> following trigger code on server_A:
>
> CREATE TRIGGER myTrigger
> ON myTable
> FOR INSERT
> AS
> SET XACT_ABORT ON
> SET NOCOUNT ON
> INSERT INTO server_B.myDB.dbo.myTable SELECT * FROM inserted
> GO
>
> And also the following trigger code on server_B:
>
> CREATE TRIGGER myTrigger
> ON myTable
> FOR INSERT
> AS
> SET XACT_ABORT ON
> SET NOCOUNT ON
> INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM inserted
> GO
>
> Before you start screaming about the recursive relationship between
> these triggers, I'm well aware of that issue, so I'm wrapping the
> trigger logic with a login ID test. The servers are linked using a
> special login account, I'll call it 'trigger_bypass_login', so the
> triggers look like this:
>
> CREATE TRIGGER myTrigger
> ON myTable
> FOR INSERT
> AS
> SET XACT_ABORT ON
> SET NOCOUNT ON
> IF SUSER_SNAME() <> 'trigger_bypass_login'
> INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM inserted
> GO
>
> Although this logically works fine, there seems to be a compile issue,
> because I'm running into the error:
>
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> What is strange is that I CONTINUE TO GET THE ERROR if I change the
> trigger code to the following:
>
> CREATE TRIGGER myTrigger
> ON myTable
> FOR INSERT
> AS
> SET XACT_ABORT ON
> SET NOCOUNT ON
> IF 1=0
> INSERT INTO server_A.myDB.dbo.myTable SELECT * FROM inserted
> GO
>
> So obviously, it has nothing to do with the actual inserting that the
> INSERT performs, but rather the fact that the trigger INSERT
> references the linked server/table.
>
> So, I moved the INSERT statement to a stored procedure, and it works
> and I no longer get the error:
>
> CREATE TRIGGER myTrigger
> ON myTable
> FOR INSERT
> AS
> SET XACT_ABORT ON
> SET NOCOUNT ON
> IF SUSER_SNAME() <> 'trigger_bypass_login'
> EXEC myStoredProcedure
> GO
>
> It works.. BUT, the stored procedure does not have access to the SQL
> Server 'inserted' trigger table. I've tried using
>
> DECLARE CURSOR myCursor GLOBAL FOR SELECT * FROM inserted
>
> and then letting the stored procedure reference the cursor, but then I
> have to deal with the cursor data on a column-level basis, which is
> not an option in this project because there are 100's of tables with
> many columns, which might change over time.
>
> So it is of extreme importance that I use INSERT INTO ... SELECT to
> move the row data in a generic fashion.
>
> I hope I have provided enough, yet not too much, information.
>
> I would really appreciate any suggestions anyone might have as to how
> I might handle this situation. Thanks.
>
> Hank

Check this KB article, which gives a lot of detail on troubleshooting
that error. The most common issues seem to be that MSDTC isn't
running, or that there is a firewall between the servers.

http://support.microsoft.com/default...&Product=sql2k

Simon