November 14, 2002 at 3:57 am
Anyone know of a quick and robust way to check if a linked server exists and is available.
I know sp_linkedservers will give me the known servers, and doing a select on a known table will tell me that the server is up and available, i.e. I'll get an error if it isn't.
The problem is performance, using a failed select can mean a lag (in our environment) of up to 7 seconds before the error gets raised. What I'd like is a quicker method of identifying if the Server is available.
Any ideas?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
November 14, 2002 at 5:12 am
You can use this SP
DROP PROCEDURE dbo.sp_ServerStatus
go
CREATE PROCEDURE dbo.sp_ServerStatus
/*******************************************************************************
Written By : Simon Sabin
Date : 14 November 2002
Description : Returns the status of a SQL Server service
History
Date Change
------------------------------------------------------------------------------
14/11/2002 Created
*******************************************************************************/
(
@servername sysname
,@status int OUTPUT
,@statusText varchar(20) OUTPUT
)
AS
SET XACT_ABORT ON
declare @hr int , @object int
exec @hr = sp_OACreate 'sqldmo.sqlserver', @object OUTPUT
IF @hr<> 0
RAISERROR ('Cannot create sqldmo.sqlserver object',15,1)
exec @hr = sp_OASetProperty @object, 'Name', @servername
exec @hr = sp_OAGetProperty @object, 'Status', @status OUTPUT
SET @statusText = CASE @HR WHEN -2147221499 THEN 'Access Denied'
WHEN -2147219782 THEN 'Server does not exist'
WHEN 0 THEN CASE @status WHEN 0 THEN 'Unknown'
WHEN 5 THEN 'Stopping'
WHEN 3 THEN 'Stopped'
WHEN 4 THEN 'Starting'
WHEN 1 THEN 'Running'
WHEN 7 THEN 'Pausing'
WHEN 2 THEN 'Paused'
WHEN 6 THEN 'Continuing'
ELSE 'Unknown' END
ELSE 'Unknown error occurred' END
EXEC sp_OADestroy @object
GO
DECLARE @s-2 int, @t varchar(100)
exec sp_ServerStatus 'ins007-dotnet\Golden_eye',@s output, @t output
select @s-2, @t
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 18, 2002 at 4:55 am
Hi Simon
Thanks for that, we'll give it a whirl, hopefully if it cuts the 7 second delay time down we're in your debt.
Graham
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
November 18, 2002 at 6:09 am
The script is at http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=588
if the server does not exist on the network then it will still take some time, as it tries to find the server.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply