Check Status of Linked Server

Question

Check Status of Linked Server

bigdave

SSC Eights!

Points: 853
More actions
May 31, 2004 at 5:52 pm

#108345

Does anyone know if it is possible to check the status of a linked server without causing an error. At present when I try to query a linked server that is not available my sqltransaction (ADO.Net) is automatically ended. I really need to avoid this behaviour and I can't afford another round trip.
Any advice much appreciated.

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

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

T-SQL does not have a function for this!

You mentioned ADO.NET, if you are trying to access a linked server through an application, then I would just place a TRY, CATCH & Exception for the connection to the linked server. By doing this, you will produce an error to the ueser that the server is unavailable.

Kindest Regards,

Mike Good SSCertifiable Points: 7415 More actions · Answer 2

I don't have working example, but have done this before in TSQL.

Use sp_OACreate 'SQLDMO.SQLServer', followed by sp_OAMethod with 'Connect' to attempt to connect to the remote/linked server. If the 'Connect' attempt fails you get error message but control does not cease. You can write an ServerIsAvailable utility proc/function that you can call prior to RPC/LinkedServer activity, then your code will never fail.

jordanac Hall of Fame Points: 3653 More actions · Answer 3

Try this.....

DECLARE @object int

DECLARE @hr int

DECLARE @property varchar(255)

DECLARE @return varchar(255)

DECLARE @src varchar(255), @desc varchar(255)

-- Create a SQLServer object.

SET NOCOUNT ON

-- 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 hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

GOTO END_ROUTINE

end

else

BEGIN

-- Set a property.

-- Call a method.

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

EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'servername', 'user', 'password'

IF @hr <> 0 GOTO CLEANUP

-- Call a method that returns a value.

EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT

IF @hr <> 0

GOTO CLEANUP

ELSE

PRINT @return

END

CLEANUP:

-- Check whether an error occurred.

IF @hr <> 0

BEGIN

-- Report the error.

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

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

END

-- 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 hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

END

END_ROUTINE:

RETURN

Andy.