Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL - OLE DB provider "SQLNCLI10" for linked server "srvname" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2,

Posted on 2012-09-12
14
Medium Priority
?
4,335 Views
Last Modified: 2012-09-18
I have run into this problem. I have read few article, problem still not resolved. Is there anyone have this problem and have a good solution for it?   Would any one able to explain or  guide me with some reading material so I can understand the problem better or what is partner transaction manager all about?
0
Comment
Question by:tommym121
  • 7
  • 4
12 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38390807
If the problem is with linked server - Is your DTC configured correctly, and your DTC is running in both server?
0
 

Author Comment

by:tommym121
ID: 38390861
I should add a comment what I am doing.

 I am calling a store procedure from  Link server as below. it will return me a table of information

EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0


I works fine.  However when I try to insert the result to a local temporary table, I got this error.

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0


Prior to this I have tested this procedure when I connect directly to the database engine without the linkserver configuration. That works fine.

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

It looks like I can not insert data to a table from a remote procedure call. How do I get around this problem.
0
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 1000 total points
ID: 38390883
Okay, the issue must be related to MSDTC config, please follow these steps in BOTH SERVER

Go to Control Panel\System and Security\Administrative Tools -> Component Services
From Left side Consol Root, Expand Component Services -> Computers -> My Computer, Right click, select Properties
Select MSDTC tab, Click Security Configuration
Make sure  Network DTC Access, Allow Remote Client,
Allow Inbound/Outbound, Enable TIP  is checked
Restart, Distribution Tranasction Coordinator Service.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tommym121
ID: 38390976
anujnb,

I was able to run the stored procedure of the Linked Server
I got output table of calling stored procedure in the result tab.

I was only unable to insert the result into a local tmp table.

If security is not set probably, would it means I will not even able to execute the stored procedure and get back the output in the result tab?
0
 

Author Comment

by:tommym121
ID: 38391012
anujnb,

I have attached the MSDTC config from the client side.  I am waiting from my IT to give me the server side configuration
Capture1.JPG
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38391023
This happens when you have a distributed transaction involving more than one SQL Server, so for this you need to configure MSDTC settings. Most of the time the above steps will solve this issue.
0
 

Author Comment

by:tommym121
ID: 38394474
After I modify both client and server,  I got this error.


OLE DB provider "SQLNCLI10" for linked server "WINFUND" returned message "No transaction is active.".

This is what I try to execute
INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

it is still fine if I execute
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

I still do not understand the difference this two execute.
the 2nd execute send the output to result tab
and the first execute attemp to send the result to the table,
Logically, I would expect the output data do arrive to my local machine running SMSS.  What stop me to insert the data to my table but allow the same data appear in the result tab?
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38394517
Did you configured the MSDTC both at linked server and the Server?

I still do not understand the difference this two execute.
the 2nd execute send the output to result tab
and the first execute attemp to send the result to the table,

When you do EXEC only this will works as this is simply returning rows, if yous SP has any DML or DDL that modifies any objects in the linked server then it may fail, to avoid this you must configure the MSDTC properly. When you do INSERT INTO with EXEC, its a cross server transaction.

The error you got is a known problem, that explains it here
0
 

Author Comment

by:tommym121
ID: 38394637
anujnb

Thanks for your explanation.  I will check again the setting and reboot the server.
0
 

Author Comment

by:tommym121
ID: 38403065
anujnb,

what is the difference between

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

and

INSERT INTO #MyLocalTempTable (Label , Amount )
select *  FROM [LINKSERVERNAME].[database].[dbo].[TABLE1]

Are both consider to be a cross server transaction
The reason why I asked,  the insert and  select work but not insert and exce.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 1000 total points
ID: 38403215
If you found that select/insert works, but exec/insert does not, then the next logical step is to look into the stored procedure, whether it has any transaction-related statements, such as BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION.

Another way is to run Profiler, also looking for the signs of transactions in both cases.

http://msdn.microsoft.com/en-us/library/aa561924%28v=bts.20%29.aspx has extensive troubleshooting steps and mentions this error message. I would start by looking at the logs at the remote server: the message says that the initiative to disable remote transactions came from there, so perhaps remote server has something in its logs regarding why he did it. But your step #1 is to examine the stored procedure itself, maybe it has something obviously impossible.
0
 

Author Closing Comment

by:tommym121
ID: 38412090
Thanks.  I manage to reverse engineer the stored procedure I want ocally.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question