enable promotion of Distributed Transactions for RPC: should be set to TRUE or FALSE
-
It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.
Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:
OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.
... when set to off, the error goes away.
Can someone help me to understanding this behaviour? Please don't send me to BOL. I want real life answer from other DBAs. I don't want to change to off just to fix this, if it will potentially affect distributed transactions or , if there is a TSQL workaround that is is easy to implement.
-
bump... anyone?
-
pretty sure the misisng pice is the service, if you've toggled both rpc settings in your linked server.
On the Target/linked server, Remote desktop over to it andopen services....it has a service named "Distributed Transaction Coordinator", and that service must be started.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (5/15/2015)
pretty sure the misisng pice is the service, if you've toggled both rpc settings in your linked server.On the Target/linked server, Remote desktop over to it andopen services....it has a service named "Distributed Transaction Coordinator", and that service must be started.
Thanks... checking... the thing is, turning it to OFF, fixes the query error.
-
It is running on all the servers. Definitely, the problem is that is TRUE on all but one.
Now, just found this Thread where Paul explains how to turn it on or off, which I know already.
http://www.sqlservercentral.com/Forums/Topic861249-392-1.aspx
... but not much else about it.
Also, what would be the security implications, if any, of keeping it set to TRUE?
-
i often get a similar error message when i create a new linked server, where i forgot to set the rpc and rpc out to true;
Executed as user: MyDomain\sqlmaster. Server 'SSASLocal' is not configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
so in my case, i have to make sure both rpc and rpc out are set to true in my linked server, and that the distributed trnasaction coordinator service was running.
I read that you are leaving it to false, and disabling the DTC,a dn it works then?
that's kind of opposite of my experience.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (5/15/2015)
i often get a similar error message when i create a new linked server, where i forgot to set the rpc and rpc out to true;Executed as user: MyDomain\sqlmaster. Server 'SSASLocal' is not configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
so in my case, i have to make sure both rpc and rpc out are set to true in my linked server, and that the distributed trnasaction coordinator service was running.
I read that you are leaving it to false, and disabling the DTC,a dn it works then?
that's kind of opposite of my experience.
Let's focus on the LinkedServer itself 😉 ... and that option.
It is set to TRUE on all of my servers but one. The following query does not run when set to TRUE
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
It only runs on the server where 'remote proc transaction promotion' is set to FALSE.
rpc and rpc out are set to true, all servers, that's not the issue.
-
does the storedprocedure ServerB.MyDatabase.MyStoreProcedure itself use an additional call to a linked server, by chance? so it double hops?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
sql-lover (5/15/2015)
Lowell (5/15/2015)
i often get a similar error message when i create a new linked server, where i forgot to set the rpc and rpc out to true;Executed as user: MyDomain\sqlmaster. Server 'SSASLocal' is not configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
so in my case, i have to make sure both rpc and rpc out are set to true in my linked server, and that the distributed trnasaction coordinator service was running.
I read that you are leaving it to false, and disabling the DTC,a dn it works then?
that's kind of opposite of my experience.
Let's focus on the LinkedServer itself 😉 ... and that option.
It is set to TRUE on all of my servers but one. The following query does not run when set to TRUE
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
It only runs on the server where 'remote proc transaction promotion' is set to FALSE.
rpc and rpc out are set to true, all servers, that's not the issue.
The setting is TRUE on the server that you run the query from and the setting is FALSE on the server where the query actually runs, correct?
What does the procedure ServerB.MyDatabase.MyStoreProcedure (are you missing the schema here?) do? Is it accessing any linkedservers?
-
Hi SQL-lover,
I run into the same issue and error described on store procedures that try to insert data from a link server into a ##tempTable. Selects are fine.
The solution by turning the linked server property “Enable Promotion of Distributed Transactions” to false worked, and I trying to figure out if I can go along with that.
More Info on this:
1. I am migrating a DB from SQL server 2008 R2 to SQL server 2014
2. SQL server 2008 R2 this property is set to True and not problems or errors.
3. There are several SPROCs using linked servers, this error is only happening on Oracle linked servers.
Are you going along still this solution?
Thanks, Felix
- felixms - Wednesday, July 6, 2016 5:05 PM
Felix - Did you get solution for this issue. I am facing similar problem after migrating from SQL server 2008 to 2014. The SQL server service crashes after executing query using linked server - EXEC (<query>) AT <linkedservername>
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply