July 28, 2008 at 2:34 am
On a SQL2005 server I intend to make a procedure to monitor free diskspace on several linked SQL2K servers. When I run
EXEC LinkedServerName.master.dbo.xp_fixeddrives
I get the desired result.
When I run
INSERT INTO tmpTable
EXEC LinkedServerName.master.dbo.xp_fixeddrives
I get the message:
OLE DB provider "SQLNCLI" for linked server "starscream" returned message "The transaction manager has disabled its support for remote/network transactions.".
Why does an attempt to insert the result into a table give me this message?
Greetz,
Hans Brouwer
July 28, 2008 at 6:03 am
The code is fine. there is something with the configuration of the linked server.
Check the Linked Server properties --> Options --> RPC = True
This will enable the Remote Procedure Calls to the linked server.
I think it will help you...
Atif Sheikh
July 28, 2008 at 10:38 pm
Tnx for answering.
I guess I did not make myself clear. I understand the problem, I am curious about the behaviour: calling the remote procedure works OK, attempting to insert the output of the remote procedure into a table causes trouble.
I woud think that just calling the procedure would cause the same message. What is the difference between just calling or caling AND inserting into a table?
Greetz,
Hans Brouwer
July 29, 2008 at 1:47 am
The difference is that you are modifying data by inserting the results into a table. Doing any data modification causes an implicit transaction to be wrapped around the statement, and since you're referencing a linked server the DTC is trying to enlist it in a distributed transaction involving the remote server. The remote server isn't configured to accept distributed transactions from your local server - thus your error.
Check your MSDTC settings on the remote server - open up dcomcnfg.exe and check the local DTC properties under "Distributed Transaction Coordinator", security tab. Check that Remote Clients and Inbound Transactions are enabled, and that the apporpriate authentication settings are used.
Regards,
Jacob
July 29, 2008 at 2:23 am
Hi,
I had a similar problem in the past and creating a linked server on the remote server pointing to this server solved the problem and could insert data into tables from a remote procedure call. I am not sure if I can explain the behavior. 🙂
Regards
pra
---------------------------------------------------------------------------------
September 11, 2008 at 12:12 am
Hi,
I had a similar problem when using begin transaction method in linked server.I checked all the settings all are fine.
But i m not able to get any solution.
What should be the Authentication Settings on linked server.
Regards,
Viresh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply