Linked Server - insert data using Stored procedure

• balasach82

  SSCrazy Eights

  Points: 9653

  More actions

  August 17, 2012 at 3:29 am

  #266195

  I have an stored proc which i have to run in multiple servers. my query

  insert into table

  exec serverB.database.schema.Storedproc

  Procedure executes fine, but when included with insert it gives error:

  OLE DB provider "SQLNCLI10" for linked server "ussbycsql302" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

  Msg 7391, Level 16, State 2, Line 1

  The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ussbycsql302" was unable to begin a distributed transaction.

  setting "remote proc transaction promotion" as false resolves that. At the end of execution, i set the option to "true". But I wnat to understand if changing this option like this would cause any issues.

  Procedure is in ServerA (SQL 2008)