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

Execute a remote SP into a local temp table Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 6:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 4:06 AM
Points: 69, Visits: 360
Hello,
I'm working with 2 servers both SQL Server 2008 R2 on the same Domain. One of these servers has a Linked Server to the other.
I can quite happily execute a stored procedure that resides on the linked server from the other however if I try to save the results into a temp table I get the following error.
"
OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xx" 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 "xx.xxx.xx.xx" was unable to begin a distributed transaction.

"
MSDTC is running on both servers. The RPC Out is set to true on the linked server.
Could anyone tell me what the difference is between executing and savign the results to a temp table, and secondly how to work around the problem.

Thanks
Giles
Post #1405378
Posted Thursday, January 10, 2013 6:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 4:06 AM
Points: 69, Visits: 360
Sorry mislead you, the servers are on different Domains.
Post #1405402
Posted Thursday, January 10, 2013 7:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 4:06 AM
Points: 69, Visits: 360
Sorted, as I'm only using the Linked Server for this activity I've configured it to ignore remote proc transactions.

EXEC master.dbo.sp_serveroption @server=N'XX.XXX.XX.XX', @optname=N'remote proc transaction promotion', @optvalue=N'false'
Post #1405431
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse