August 10, 2010 at 1:35 pm
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?
August 10, 2010 at 3:14 pm
Have you checked the MSDTC is set up so each box can run distributed transactions?
August 25, 2010 at 12:09 pm
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!
August 26, 2010 at 6:38 am
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