Problem with linked server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with linked server

I’m having a problem with a linked server. Both are running sql 2000 (below i’ll attach the relevant info). Problem is, I have two server A and B. I’ve defined B as a linked server in A. so, for example I can do (from A ) select * from B.northwind.dbo.test with no problem. but if I do the same inside a transaction : begin tran
select * from B.northwind.dbo.test
commit tran the remote query (in B) keeps running forever.
If i take a look at the process in the remote server (B) it’s not blocked.
It just keep running forever until killed. And of course the local process in A is waiting forever to the remote for finish. I don’t know much about DTC o ditributed transaction, but how can I trace the problem? Does anyone know what’s going on? thanx.
SERVER A.
xp_msver
1ProductNameNULLMicrosoft SQL Server
2ProductVersion5242888.00.760
3Language1033English (United States)
4PlatformNULLNT INTEL X86
5CommentsNULLNT INTEL X86
6CompanyNameNULLMicrosoft Corporation
7FileDescriptionNULLSQL Server Windows NT
8FileVersionNULL2000.080.0760.00
9InternalNameNULLSQLSERVR
10LegalCopyrightNULL© 1988-2003 Microsoft Corp. All rights reserved.
11LegalTrademarksNULLMicrosoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12OriginalFilenameNULLSQLSERVR.EXE
13PrivateBuildNULLNULL
14SpecialBuild49807360NULL
15WindowsVersion1438515255.0 (2195)
16ProcessorCount11
17ProcessorActiveMask100000001
18ProcessorType586PROCESSOR_INTEL_PENTIUM
19PhysicalMemory640640 (670597120)
20Product IDNULLNULL SERVER B.
1ProductNameNULLMicrosoft SQL Server
2ProductVersion5242888.00.760
3Language1033English (United States)
4PlatformNULLNT INTEL X86
5CommentsNULLNT INTEL X86
6CompanyNameNULLMicrosoft Corporation
7FileDescriptionNULLSQL Server Windows NT
8FileVersionNULL2000.080.0760.00
9InternalNameNULLSQLSERVR
10LegalCopyrightNULL© 1988-2003 Microsoft Corp. All rights reserved.
11LegalTrademarksNULLMicrosoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12OriginalFilenameNULLSQLSERVR.EXE
13PrivateBuildNULLNULL
14SpecialBuild49807360NULL
15WindowsVersion2483819575.2 (3790)
16ProcessorCount44
17ProcessorActiveMask150000000f
18ProcessorType586PROCESSOR_INTEL_PENTIUM
19PhysicalMemory10241024 (1073242112)
20Product IDNULLNULL
How about openquery funtion, see BOL for details.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
The same happens. If i do : select * from
openquery ( B , ‘select * from northwind.dbo.test ‘) everything OK. But, If I include it inside a transaction : begin tran select * from
openquery ( B , ‘select * from northwind.dbo.test ‘) commit tran It justs keeps running.
Refer the following articles :
http://www.sql-server-performance.com/linked_server.asp
http://support.microsoft.com/default.aspx?kbid=326839
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I think BEGIN DISTRIBUTED TRANSACTION should be used here and make sure DTC service is running. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Ok.
I’ve checked that DTC was running on both machines.
And just in case I’ve restarted the DTC services on both. And also I changed the BEGIN / COMMIT to begin distributed transaction select * from B.northwind.dbo.test commit transaction
Nothing changed. the remote process keeps running forever. I if take a look at current activity in the remote server, the "last TSQL command batch" says sp_cursoropen;1 If anyone can think of something, please let me know.

Search the forum for dtcping utility and you will get more details on how to troubleshoot distributed transactions.
Have you gone thru the tips section listed on above links. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Finally I solved the problem (as always googling around). Just for the record if anyone wants to add it to the tips or faqs or whatever, this is the reason. On Windows Server 2003, DTC has be default network access disabled, so in
order to communicate from a client to a remote DTC or to communicate
between two DTC instance you need to enable network access. Seehttp://support.microsoft.com/default.aspx?scid=kb;en-us;817064 for details. I’ve learned things about DTC I didn’t even know existed. Thanx a lot all of ya.
Glad it solved, but in your initial post you should’ve mentioned it to get quicker turnaround.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I did mention it.<br />If you take a look at the xp_msver output of B server, you can see :<br /><br />15 WindowsVersion 248381957 5.2 (3790)<br /><br />Pretty easy to figure it out? huh ? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Sorry I forget to mention it.<br /><br />Damn Win2003 and its default security settings… sometimes it drives me nuts.<br />
Sorry, my omission. [8D] Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>