Storing results of stored proc from linked server in temp table

  • Hi all - got a problem which I've been struggling with for days, and have yet to come up with a solution.

    Basically, I have a stored proc on a linked server, which I want to use. I want to put the results into a temp table, and then use that temp table for other purposes.

    The way I would normally do this is to create a temp table, and then do an INSERT INTO - EXEC SP. However, if I try this with a stored proc on a linked server, I get an error:

    OLE DB provider "SQLNCLI" for linked server "" 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 "SQLNCLI" for linked server "" was unable to begin a distributed transaction.

    Every solution that I've attempted to work around this has failed. Using a table variable instead of a temp table has the same effect. Another solution I tried instead was to use the OPENQUERY approach, however, this approach simply does not work, probably due to the functionality of the stored proc - my theory on that one is because the stored proc is using a table-valued function.

    The only solution I've been able to come up with, is to have a physical table on the remote server, and then from the main server, run a stored proc that exists on the remote server, which dumps the results of the stored proc into the physical table, and then from the main server, run a query against that table for the results. But this is messy.

    Does anyone have any other solutions?

  • Have you checked the MSDTC is set up so each box can run distributed transactions?

  • Hi! I was in the same boat.. Try my example!

    DECLARE @TempTable TABLE (

    TagID INT,

    SampleDateTime BIGINT,

    SampleValue FLOAT,

    QualityID INT

    )

    INSERT INTO @TempTable Select * from OPENQUERY([000-SER-003\VIJEOHISTORIAN],

    'EXEC Historique.dbo.GetLastKnownNumericSampleByID @tagID = 1274, @sampleDateTime = 634004715514990000, @specifyCurrentTimestamp = 1')

    /*

    First param = [000-SER-003\VIJEOHISTORIAN] (linked server name, Don't send it as a string)

    Second param = Your Stored proc, send it as a string!)

    */

    Bye!

  • Does sound like a DTS problem. These can be a PITA to deal with, but there are online resources you can find on how to properly configure.

    I also recommend you check out replicating the data you hit over the linked server. I have had several clients get orders of magnitude better query performance from that effort.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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