Post reply

Execute a remote SP into a local temp table

  • January 10, 2013 at 6:20 am

    #266930

    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

  • January 10, 2013 at 6:50 am

    #1575761

    Sorry mislead you, the servers are on different Domains.

  • January 10, 2013 at 7:22 am

    #1575785

    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'

Viewing 3 posts - 1 through 2 (of 2 total)

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