Testing for existance of Linked Server

Question

Post reply

Testing for existance of Linked Server

Gareth Bowen

SSC Enthusiast

Points: 148
More actions
October 5, 2004 at 10:43 am

#86628

Does anybody know of a method of testing for the existance of a Linked server?? I have a Data Warehouse solution which connects to 40 remote sites using linked servers. On the whole this works fine but sometimes due to network problems I get the dreaded "SQL Server does not exist or access denied " message but I don't know which of the 40 has caused the message.
What I would like to do is to test connectivity to the linked server and log the whether the connection is available. Unfortunatly it doesn't appear to be possible to trap the above error and continue with the process.

Anybody got any ideas how this can be achieved???

Viewing 5 posts - 1 through 4 (of 4 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

What about...

SELECT TOP 1 * FROM [LinkedServer1].DB.Owner.Table

IF @@ERROR <> 0

BEGIN

-- Log error stating Server1 failed

END

Repeat as needed??

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Gareth Bowen SSC Enthusiast Points: 148 More actions · Answer 2

Thanks AJ, but I've tried that and you can't trap the error as it is a level 16 and quits the loop if it finds a server that's not available.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

I faced this issue when I was implementing a "heartbeat check" on our SQL Servers. Pinging is great. Service may show as up. But can you actually log in? What I ended up doing is using a DTS package and an ActiveScriptTask to attempt ADO connections because then I could check the connection status and therefore trap the error.

However, if you're trying to make decisions based on whether individual servers are up, this may not be possible. An option, though I hate to suggest it, is to use the sp_OA stored procedures in a similar fashion.

K. Brian Kelley
@kbriankelley

Gareth Bowen SSC Enthusiast Points: 148 More actions · Answer 4

Cheers Brian,

I did as you suggested and looked at the sp_OA procedures. In the BOL examples there was exactly what I was looking for using the 'connect' method. However when I tested the solution, I got unexpected errors, as an error is returned from the connect method if there is an open connection, so I changed it to use the 'PingSQLServerVersion' method and I only get errors when the server is unavailable.

-- Create a SQLServer object.

-- First, create the object.

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF @hr <> 0

-- Report the error.

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT 'An error has occurred in the first step',hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

GOTO END_ROUTINE

END

ELSE

BEGIN

-- An object is successfully created.

-- Call a method.

-- SECURITY NOTE - When possible, use Windows Authentication.

-- Replace @serv - server name

-- @user - user name

-- @pw - password

EXEC @hr = sp_OAMethod @object, 'PingSQLServerVersion', NULL, @serv, @user, @PW

IF @hr <> 0

begin

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

-- Either insert into a table or output a message

Insert into RemoteServerCheck

(DateFailed,Server,src,descp)

Values (getdate(),@serv,@src,@desc)

-- SELECT 'An error has occurred',hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

END

CLEANUP:

-- Destroy the object.

BEGIN

EXEC @hr = sp_OADestroy @object

-- Check if an error occurred.

IF @hr <> 0

BEGIN

-- Report the error.

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT 'An error has occurred',hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

END

END_ROUTINE:

RETURN