Linked Server query

Question

Post reply

Linked Server query

Gags

SSC Veteran

Points: 280
More actions
June 28, 2005 at 8:09 am

#87934

Hi,
I have a simple query that runs across linked servers. I can run the query however I can not insert the results into a table.
There is an exec proc that sits on the linked server. The code I am running is as follows:
---------------------------
insert into #sql_server(
SERVER_NAME ,
VERSION_TEXT)
exec "<LinkedServer>".<databasename>.dbo.exec_sql '
select @@SERVERNAME, left(@@VERSION, 50)'
---------------------------
If I just run the exec part of the query the results are returned fine. If I run with the insert portion I get the error message:
MSDTC on server '<Servername>' is unavailable.
I have checked and the DTC service is in fact running on both servers. I am running SQL 7.0 on both machines.
Does any one know what the problem is???

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

What version of Windows are you running and are both machines in the same domain?

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Gags SSC Veteran Points: 280 More actions · Answer 2

I am running XP on one machine and Windows 2000 server on the other. Both machines are in different domains.

Do either of these factors make a difference?

DCPeterson SSCoach Points: 15266 More actions · Answer 3

Yes, it does. XP disables distributed transactions across the network by default. See http://support.microsoft.com/?kbid=839279 for details and how to fix the problem.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

DCPeterson SSCoach Points: 15266 More actions · Answer 4

I would also add that Windows 2003 has essentially the same issue. Here is an article explaining the details: http://support.microsoft.com/default.aspx?scid=kb;en-us;817064

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Gags SSC Veteran Points: 280 More actions · Answer 5

Thanks dc.

I haven't had a chance to test this out yet but i think have diagnosed the problem. Will post another reply to thank you and let you know what i did when i've got this working.

Gags SSC Veteran Points: 280 More actions · Answer 6

Mr Peterson,

You indeed are an old hand and if i could, i would like to shake you by the hand.

I have now managed to get my linked server queries working. All i did was to change the log on account of the DTC service to that of an account with admin rights on both machines.

Thank you very much!