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

  • sql-lover

    SSCoach

    Points: 18531

    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

    SSCoach

    Points: 18531

    bump... anyone?

  • Lowell

    SSC Guru

    Points: 323345

    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

    SSCoach

    Points: 18531

    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

    SSCoach

    Points: 18531

    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

    SSC Guru

    Points: 323345

    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

    SSCoach

    Points: 18531

    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

    SSC Guru

    Points: 323345

    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

    SSC Guru

    Points: 442098

    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?

  • felixms

    SSC Enthusiast

    Points: 138

    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

  • VikasSQL

    Grasshopper

    Points: 11

    felixms - Wednesday, July 6, 2016 5:05 PM

    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 20142. 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

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply