Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server - insert data using Stored procedure Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 3:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:14 AM
Points: 374, Visits: 911
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)
Post #1346421
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse