Monitor Any Linked Server From SQL 2000

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

------------------------------------------------------------------------------------------------------------------
---Name: uspMonitor_LinkedServer
---Author: Sakthivel C
---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