Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TCP Provider: The semaphore timeout period has expired has started happening Expand / Collapse
Author
Message
Posted Thursday, July 21, 2011 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:56 AM
Points: 128, Visits: 421
We have a linked server named 'testls' and this query fails
select * from [testls].temp_test.dbo.table_1
It fails with the error
OLE DB provider "SQLNCLI" for linked server "testls" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "testls" returned message "Communication link failure".
OLE DB provider "SQLNCLI" for linked server "testls" returned message "Communication link failure".
Msg 121, Level 16, State 1, Line 0
TCP Provider: The semaphore timeout period has expired.

Whereas this query,
select * from openquery([testls],'select * from temp_test.dbo.table_1')
Runs perfectly fine.

For the first query I can see the connection on the linked server end.
Also, via SSMS, when expanding the linked server I can see all the databases, but when I try to expand to see tables, I get this TCP error.

This has been working fine for months, but in the middle of today our jobs started failing.
I can run the same linked server create script and it works from a different server to the same destination server.
The problem is just from A-B and not B-A and not C-B

Given the error, I think it must be a network issue but connections actually exist on the linked server and using an openquery works, so I'm confused.

Both servers are SQL 2005 the source is 9.0.3054 and the remote one is 9.0.3353
There are no entries in Event viewer on either machine.

For this test the table_1 has 1 column and no data, so it's just to prove the communcation issue.

Can anybody suggest where to investigate futher?

Thanks.
Post #1145992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse