SQL Server 2000-2005-2008 Distributed Query Problem

Question

Hello,

Just set up a new server. Trying to transfer some workload from one to the other. Everything looks great, except when I come to run this code:

CREATE TABLE #KEYWORD5    
(ITEM_MASTER_ID NUMERIC(25) NULL)    

INSERT INTO #KEYWORD5
exec SQL2K801.soupftidx.dbo.P_REMOTE_SQL_EXEC 'SELECT IM.ITEM_MASTER_ID FROM
 ITEM_MASTER IM WHERE IM.BUYER_ORGANIZATION_ID IN (5970,5230) 

AND IM.ACTIVE_FLAG = ''Y'' 
AND CONTAINS(IM.*, '' ("SCREW*" OR FORMSOF (INFLECTIONAL, "SCREW")) '')' 

SELECT * FROM #KEYWORD5

DROP TABLE #KEYWORD5

By the way, the following works just fine:

CREATE TABLE #KEYWORD5    
(ITEM_MASTER_ID NUMERIC(25) NULL)    

INSERT INTO #KEYWORD5
exec P2PINDEXDA.soupftidx.dbo.P_REMOTE_SQL_EXEC 'SELECT IM.ITEM_MASTER_ID FROM
 ITEM_MASTER IM WHERE IM.BUYER_ORGANIZATION_ID IN (5970,5230) 
AND IM.ACTIVE_FLAG = ''Y'' 
AND CONTAINS(IM.*, '' ("SCREW*" OR FORMSOF (INFLECTIONAL, "SCREW")) '')' 


SELECT * FROM #KEYWORD5

DROP TABLE #KEYWORD5

The only difference between the two is the servername prefix (p2pindexda vs sql2k801). I've verified that the linked servers have the same options set. Also, I've verified that the exec statement works fine...ie: It returns results as expected.

Where it goes bad is when I try to insert the results into a local Temp Table.

What gives?

Answer 1

At first glance I would assume that MSDTC isn't setup correctly on the new server.

However without the text of the error I'm just guessing.

Answer 2

Quote -I've verified that the linked servers have the same options set - Have you verified the rights on the 2 servers you are linking to - while the linked server connection may work from this server - the rights the connection has on the other server/s may may not be correct Hope that makes some sort of sense, as the others have said hard to know when you don't know the actual error