Hello guys, I am having a bit a challenge with some distributed transactions. This particular error is quite well documented on the Web, but nothing appears to help me with my particular issue.
I am attempting to run the following bit of code within a trigger from Server A, to insert a row into a table on Server B. Server A and Server B are both running SQL Server 2000 on Windows Server 2000. It is important to know that there is a transparent connection between the two servers, so I don't need to punch a hole through any firewalls (because there aren't any).
begin distributed transaction
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=99.99.99.99;User ID=sa;Password=password'
).Database1.dbo.Table1 (Field1, Field2)
select
Field1, Field2
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=99.99.99.99;UserID=sa;Password=password'
).Database1.dbo.Table2
where Field1 = @Field1
commit transaction
Run I run an update statement on Server A, and the trigger fires, the following error message is returned.
Server: Msg 7391, Level 16, State 1, Procedure SuspendTest, Line 73
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].
When run as a script with the '@field1’ variable explicitly set, the code runs fine !
Pretty much all of the KB articles refer to problems either with XP SP2, Windows 2003 Server or MS DTC vs Firewall issues... none of these are relevant here.
Can anyone help ?
I am attempting to run the following bit of code within a trigger from Server A, to insert a row into a table on Server B. Server A and Server B are both running SQL Server 2000 on Windows Server 2000. It is important to know that there is a transparent connection between the two servers, so I don't need to punch a hole through any firewalls (because there aren't any).
begin distributed transaction
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=99.99.99.99;User ID=sa;Password=password'
).Database1.dbo.Table1 (Field1, Field2)
select
Field1, Field2
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=99.99.99.99;UserID=sa;Password=password'
).Database1.dbo.Table2
where Field1 = @Field1
commit transaction
Run I run an update statement on Server A, and the trigger fires, the following error message is returned.
Server: Msg 7391, Level 16, State 1, Procedure SuspendTest, Line 73
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].
When run as a script with the '@field1’ variable explicitly set, the code runs fine !
Pretty much all of the KB articles refer to problems either with XP SP2, Windows 2003 Server or MS DTC vs Firewall issues... none of these are relevant here.
Can anyone help ?
