Execute a remote SP into a local temp table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Execute a remote SP into a local temp table

Execute a remote SP into a local temp table

Rate Topic

Display Mode

Topic Options

Author

Message

giles.clapham

Posted Thursday, January 10, 2013 6:20 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 5:26 AM
Points: 57, Visits: 246

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

giles.clapham

Posted Thursday, January 10, 2013 6:50 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 5:26 AM
Points: 57, Visits: 246

Sorry mislead you, the servers are on different Domains.

Post #1405402

giles.clapham

Posted Thursday, January 10, 2013 7:22 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 5:26 AM
Points: 57, Visits: 246

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 »

Permissions