Problem populating temp table from linked server.

Hello,

I have a 2000 sql server linked to a 2005 sql server an I am trying to return data across the link. If I just run the sp I get the data back fine but if I try to Insert the data into a temp table the process just hangs and has to be killed.

This works fine:

EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (returns 156k records in about 2 sec.)

However inserting the results into a local temp table never returns. In fact the process never really runs.

CREATE TABLE #tmpOrgResult
(
intObjectID INT NOT NULL,
intObjectTypeCodeID INT NOT NULL

)
GO

-- Insert org records that match the search.
INSERT INTO #tmpOrgResult
(
intObjectID,
intObjectTypeCodeID
)
EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (This statement just hangs)

Try with

SET REMOTE_PROC_TRANSACTIONS OFF

before the INSERT statement. What I suspect happens is the local transaction is promoted to a distributed one and something gets messed up.

Zlatko

|||

You are correct it is getting promoted to a distributed transaction. Turns out MSDTC was off by default on the server. The following article showed me how to enable it. Works like a champ now.

http://support.microsoft.com/?kbid=873160

|||You are welcome.