Need Help..Linked Server Monitor?
-
Just a little introduction. I'm not even a newbie. I'm getting thrown to the wolves on a linked server issue with an iSeries (AS 400). I think I'm having an issue with a linked server connection but I have no idea how to monitor it. Could any of you point me in the right direction? I saw this script from a search I did on the site. Would I just set this up as a job via SQL management studio?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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= 'xxx@xxx.xxx' ,@from_name= @@SERVERNAME ,
@to='xxx@xxx.xxx',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'xxx.xxx.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= 'xxx@xxx.xxx' ,@from_name= @@SERVERNAME ,
@to='xxx@xxx.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'xxx.xxx.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
-
I'd just use a call to sp_testlinkedserver @servername. I think this is lighter weight than the script below. Then I'd also use sp_send_dbmail to send the email.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I agree with Jack. Did you try his recommendation?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply