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

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

sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0311070236.7bb3adc2@posting.google. com>...
>
> 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


Thanks for your input Simon.

MSDTC does not appear to be a problem. It is running on both
machines, and I can perform distributed transactions with no problem.
I can insert/update/delete linked server tables using four-part names
with no problem. Even the triggers between linked servers work fine,
as long as there are no recursive references in the trigger code,
regardless of whether the statement that causes the recursion gets
executed.

I also checked the troubleshooting link you sent.
- pingtest.bat runs fine on both machines
- both machines are using TCP/IP only
- transactions are not timing out because I can modify data using DTC
and linked servers with no problem when I'm not using triggers that
contain recursive references

The point in my original post that brought me here is the fact that I
get the error even if the recursive statement (the INSERT) doesn't get
executed. I thought this wouldn't be a problem, seeing as SQL Server
supports a server setting for 'nested triggers' which I've set to 1 on
both machines (and RECONFIGURE'd).

I first need to determine if what I'm doing is even supported. I've
searched BOL fairly extensively and all indications are that there
should be no problem. Additionally, I was hoping someone might
understand how SQL Server is compiling/executing this logic at the low
level, to better understand why the server, when executing a trigger,
even bothers looking at statements that don't get executed (i.e. the
false conditions of IF blocks).

Thanks again,
-Hank

I am having the SAME EXACT PROBLEM. Just implemented a new SQL FARM
and moved the databases over. Unable to perform an insert on linked
database...actually the fact that the statement is in the trigger and
not even called causes the error.

Did you ever get an answer/discover a resolution?
Your help in this matter would be greatly appreciated!

Thanks,
Reena

[posted and mailed, please reply in news]

(reena@cottermangroup.net) writes:
> I am having the SAME EXACT PROBLEM. Just implemented a new SQL FARM
> and moved the databases over. Unable to perform an insert on linked
> database...actually the fact that the statement is in the trigger and
> not even called causes the error.
>
> Did you ever get an answer/discover a resolution?
> Your help in this matter would be greatly appreciated!

In the thread there was a link to
http://support.microsoft.com/default...&Product=sql2k
Did you check whether there was anything that applies to you?

Else, it would helpful, if you gave the complete scenario with triggers
and statements. Preferrably simplified with only some few columns in
the table, but enough so anyone can run the example.

I reviewed the posting you refer to, but it wasn't clear to me how
his initial statement looked like, or at which point he got the error.
And in any case, your triggers may not look like his.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp