Storing results of stored proc from linked server in temp table

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  August 10, 2010 at 1:35 pm

  #225275

  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?

• andrewkane17

  SSCertifiable

  Points: 7529

  More actions

  August 10, 2010 at 3:14 pm

  #1205061

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

• steph.lessard

  Grasshopper

  Points: 19

  More actions

  August 25, 2010 at 12:09 pm

  #1211834

  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!

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 26, 2010 at 6:38 am

  #1212243

  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