Monitor Linked Server in SQL 2000

Since there are no scripts available in net for monitoring Linked Servers, I wrote a script and published in SQLServerCentral.com. You can check the article from this link for detailed report on the script. Also I have published the script below for immediate usage!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

------------------------------------------------------------------------------------------------------------------
--- Name : uspMonitor_LinkedServer
--- Author : Sakthi
--- Release Date : 05/03/2008
--- Revision : O
--- Revision History:
------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.[uspMonitor_LinkedServer]
AS
BEGIN


DECLARE @ServerName VARCHAR(255)
DECLARE @ServerType VARCHAR(50)
DECLARE @Dummy VARCHAR(100)
DECLARE @msg VARCHAR(500)
DECLARE @hr INT
DECLARE @sql INT
DECLARE @Error bigint
DECLARE @status INT ;SET @status = 0

-- Get the list of Excel and Access Linked Servers Since the Testing Method is different here
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT srvname FROM master..sysservers WHERE isremote=1 AND srvproduct NOT LIKE 'SQL Server%' AND srvname NOT IN ('')

OPEN Lnkd_srvr_Crsr
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Reading a sample Table Name to Dummy Variable from Excel sheet to ensure Connectivity
SELECT TOP 1 @Dummy = TABLE_NAME from master.dbo.SYSREMOTE_TABLES (@ServerName)

-- Checking Status and Sending Alert Mail to Team
IF @@ROWCOUNT = 0 BEGIN
SET @msg= 'Error in Getting Data from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME

Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr

-- Get the list of SQL Linked Servers Since the Testing Method is different here
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT srvname FROM master..sysservers WHERE isremote=1 AND srvproduct LIKE 'SQL Server%' AND srvname NOT IN ('')
OPEN Lnkd_srvr_Crsr

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Performing a login and check the status of Server(SQL) to ensure Connectivity

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sql OUTPUT
---IF @hr<>0 EXEC sp_OAGetErrorInfo @sql -- Used while testing procedure to check error msg
EXEC @hr = sp_OASetProperty @sql ,'LoginSecure','True'
EXEC @hr = sp_OASetProperty @sql ,'LoginTimeout',10
EXEC @hr = sp_OAMethod @sql,'Connect',null,@ServerName
EXEC @hr = sp_OAGetProperty @sql ,'Status',@status OUTPUT
EXEC @hr = sp_OAMethod @sql,'DisConnect',null
EXEC @hr = sp_OADestroy @sql

-- Checking Status and Sending Alert Mail to Team
IF @status <> 1 BEGIN
SET @msg= 'Timeout in Getting Data from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
print @status
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr


END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO