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
