Monitor Any Linked Server From SQL 2005

Though SQL Server 2005 has a sp_TestLinkedServer stored procedure to test linked server, It will not work out when the linked server is excel or access, so for this I have written a script which will work perfectly for any type of linked Server... Check this link for complete article else take the below code for immediate solution!


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------------------------------------------------------------------------
--- Name : uspMonitor_LinkedServer_2005
--- Author : Sakthi
--- Release Date : 05/03/2008
--- Revision : O
--- Revision History:
------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[uspMonitor_LinkedServer_2005]
AS
BEGIN
DECLARE @ServerName VARCHAR(255)
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;SET @status = 0

-- Get the list of Linked Servers
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers WHERE is_linked=1 AND name NOT IN ('') --Not in Clause can be used to exclude some Test Linked Servers

OPEN Lnkd_srvr_Crsr
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
-- The Below query will list the tables available in the linked server. Even it works for Excel
BEGIN TRY
EXEC master..sp_tables_ex @ServerName
SET @status = @@ROWCOUNT
END TRY

-- Checking Status and Sending Alert Mail to Team
BEGIN CATCH
SET @msg= 'Error from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME + ' ' + error_message()
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
SET @status = 1
END CATCH

IF @status = 0 BEGIN
SET @msg= 'No Recordset Returned 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

END