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.