Thursday, August 27, 2009

SP : Testing Linked Server Availability

SQL Server comes with a system stored procedure sys.sp_testlinkedserver to test linked server availability.
Here I simply put that inside by own procedure to generate an email too.

CREATE PROCEDURE utils.[LinkedServerTest] @ServerName SYSNAME
AS 
 BEGIN
 DECLARE @Test BIT

 BEGIN TRY
 EXEC @Test= sys.sp_testlinkedserver @servername 

 PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))
 END TRY

 BEGIN CATCH
 PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

 DECLARE @chvFrom VARCHAR(255)
 DECLARE @chvTo VARCHAR(255)
 DECLARE @chvSubject VARCHAR(255)
 DECLARE @chvBody VARCHAR(8000)

 SET @chvFrom = 'sql.admin@domain.co.uk'
 SET @chvTo = 'sql.admin@domain.co.uk'
 SET @chvSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
 SET @chvBody  =  @chvSubject

 EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Mail Profile'
  , @recipients=@chvTo
  , @body=@chvBody
  , @subject=@chvSubject
  , @importance='High'
  
 RAISERROR ('Linked Server Failure', 16, 1, @chvSubject) WITH LOG
 
 END CATCH

 END
GO

Usage :
exec  utils.LinkedServerTest @ServerName = 'my linked server'

No comments: