server Unable to insert result into a table

I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.

When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.

I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.

Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.

DECLARE @.retval AS INT

DECLARE @.value AS INT

SET @.value = 4

CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)

INSERT INTO #TempTable

EXEC @.RetVal = Server.Database.dbo.testproc @.value

SELECT * FROM #TempTable

DROP TABLE #TempTable

You need to turn the MSDTC service on.

START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

|||THE MSDTC is already started on both servers. I also tried restarting it on both servers with no luck.|||Do your servers have instance names? If so you would have to add these when you link the servers.

eg

Code Snippet

exec sp_addlinkedserver 'ServerName\InstanceName'

Try a simple select from one server to another, this should tell you if it is a linked server problem, or then we know there is something amok in your query.

Post back your findings |||No instance names. I can get the data back but I can't insert it into a table to the calling server but I can't insert it into a table when I get it back. If I comment out the insert line in the code provided I can see the data but I need it in a table so I can process.|||

Are you getting an error?

I don't know which OS you're using but this link may be useful...

http://support.microsoft.com/kb/899191

|||

That helped at least I can get an error message now instead of query analyzer running forever.

Server: Msg 7390, Level 16, State 2, Line 26
The requested operation could not be performed because OLE DB provider "INSQL" for linked server "INSQL" does not support the required transaction interface.