November 1, 2011

Check linked server status in SQL Server

We can ping server from SQL Server in other word we can check current
status of linked server.I have tried to write a T-SQL for this.

DECLARE @ServerStatus TABLE
(IPStaus VARCHAR(500))
DECLARE @PingIP varchar(1000)
SELECT @PingIP = 'ping ' + 'xx.xx.xx.xx'
SET NOCOUNT ON;
INSERT INTO @ServerStatus
EXEC master.dbo.xp_cmdshell @PingIP
IF EXISTS (SELECT 1 FROM @ServerStatus WHERE IPStaus LIKE  '%Request timed out%' )
BEGIN
    PRINT 'Server does not exist'
END 
ELSE
BEGIN
    PRINT 'Server available.'    
END 

That’s It
Enjoy Learning.

No comments:

Post a Comment