Linked server "1.1.1.1" returned message "No transaction is active

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have a local server (i.e. MYSERVER) and a local database (i.e. MYDATABASE)
here in our location.

I have set up a "Linked Server" entry on MYSERVER which links to the HMS
Server. I test the connection (using username and password above) and the
connection works.

I set up a stored procedure on MYSERVER. I execute the following command in
my stored procedure:
SELECT * FROM [65.36.25.15].world.dbo.po_order

That works. That tells me that the login is correct. The linked server is
correct. All seems perfect.

I then try to execute this command:
BEGIN TRANSACTION xxx
INSERT INTO [65.36.25.15].world.dbo.po_order(po_id) values (23)
COMMIT TRANSACTION

That command fails with the folowing error message:
OLE DB provider "SQLNCLI" for linked server "65.36.251.156" returned message
"No transaction is active.".

It also fails with the same error message when I do not add the BEGIN
TRANSACTION and COMMIT TRANSACTION statements.

Note: MSDTC is running on both MYSERVER and the HMS server. MSDTC is
configured to allow both incoming and outgoing traffic.

The best clue is that the SELECT works but the INSERT does not. My research
leads me to believe that DTC is somehow messed up, but that is just a guess.
As I understand it, the SELECT only does a transaction on MYSERVER. But the
INSERT tries to do a distributed transaction on both servers. That is the
only difference that I could find between the SELECT AND INSERT statements.

Thank you for any assistance that you can provide.

- Paul


.

Relevant Pages