SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


enable promotion of Distributed Transactions for RPC: should be set to TRUE or FALSE


enable promotion of Distributed Transactions for RPC: should be set to TRUE or FALSE

Author
Message
sql-lover
sql-lover
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 1930
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.
sql-lover
sql-lover
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 1930
bump... anyone?
Lowell
Lowell
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138755 Visits: 41519
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!
sql-lover
sql-lover
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 1930
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.
sql-lover
sql-lover
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 1930
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?
Lowell
Lowell
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138755 Visits: 41519
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!
sql-lover
sql-lover
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 1930
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.
Lowell
Lowell
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138755 Visits: 41519
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!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168242 Visits: 39527
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
felixms
felixms
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 6
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search