Linked server will hang on INNER JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked server will hang on INNER JOIN

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-27 : 12:01:34

Sylvain writes "Hi all,

I have the weirdest problem on a linked server.

I have 2 servers linked with a linked server here's some details about the linked server:

EXEC sp_helplinkedsrvlogin
Linked server Local Login Is Self Mapping Remote Login
---------------- -------------- --------------- ----------------
MyLinkedSVR NULL 0 MyGenericUser

EXEC sp_linkedservers
SRV_NAME SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE SRV_PROVIDERSTRING SRV_LOCATION SRV_CAT
--------------- ---------------------------- ------------------ --------------- ----------------------- --------------- ---------

MyLinkedSvr SQLOLEDB SQL server ServerA null null null

The problem is that for some reason I cannot query 2 tables on server A using a connection from server B that uses the linked server.

I can query on server A if I use only one table. But when I try to use 2 or more table then the query will hang until I kill it.

I have created 2 tests table on server A to rule out any locking issues. I can query test table 1 and test table 2 but when I try to join the 2 together it will hang ( both test table had 3 rows in them ).

Any one that has an idea I will greatly appreciate.

So far my only resolution to this is to restart both services.

Thank you for any ideas.

Sylvain"

Jhalmans
Microsoft SQL Server Product Team

12 Posts

Posted - 2006-03-02 : 01:16:53

can you get the query plan by using set showplan_all on? Also are you using 4 part name, which would cause statistics to be shipped between the servers, or OpenQuery? If you're not using open query does that work?

This posting is provided "AS IS" with no warranties, and confers no rights

mr_mist
Grunnio

1870 Posts

Posted - 2006-03-02 : 03:53:25

Are both servers patched?

Are the tables large?

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources