How to test for network connection to linked server?

[bcouse]

Hi All,

Does anyone know how to test to see if a linked server really exists? Not that the link exists, rather that the actual server can be reached over the network. For example, if I execute the following query:

select count(*) from server99.testdb.dbo.user_accounts

I am returned the following
Server: Msg 6, Level 16.... Specified SQL server not found

This occurs when the server happens to be down or the network connection has been disrupted. Does anyone know how a connection test can be performed in a stored proc?

Appreciate the assistance...
Brad

 

[MaffewW]

If your dba hasnt disable or you could use xp_cmdshell to see if you can ping the server

DECLARE @pingserver int
EXEC @pingserver = xp_cmdshell 'ping 192.168.x.x'

 

[tlbroadbent]

Pinging the server will not verify that SQL Server is running. It only verifies the the Server is running.

Check this link for a couple of methods that may work.

http://groups.google.com/groups?threadm=#hYfOV3pCHA.1644@TK2MSFTNGP10

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.