Linked Server Connectivity Test

,

Introduction :

This is a procedure to check Linked Server Connectivity

Usage :

The procedure has 1 mandatory parameter (@ServerName) and 2 optional (@emailProfile and @emailTo)

EXEC [Utils].[LinkedServerTest] 
@ServerName = 'mylinkedserver'
,@emailProfile = 'myemailprofile'
,@emailTo = 'me@mydomain.co.uk'

Output :

The output is simple.

'Sucessfully connected to SERVERNAME'

or

'Failed to connect to SERVERNAME'

 

If the optional parameters are supplied, the procedure emails the address given should the test fail.

You can place this code in a SQL Agent Job and schedule it to periodically check your linked servers.

 

Let me know what you think,

r

 

Richard Doering

http://sqlsolace.blogspot.com

/*
Script  : Linked Server Connectivity Checker
Version : 1.0 (March 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
CREATE SCHEMA [Utils] AUTHORIZATION [dbo]
GO

CREATE PROCEDURE [Utils].[LinkedServerTest] 
     @ServerName SYSNAME
    ,@emailProfile VARCHAR(255) = ''
    ,@emailTo VARCHAR(255) = ''
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))

 IF (@emailProfile <> '') AND (@emailTo <> '')
BEGIN
    DECLARE @emailSubject VARCHAR(255)
    DECLARE @emailBody VARCHAR(8000)

    SET @emailSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
    SET @emailBody = @emailSubject

    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name= @emailProfile
        , @recipients=@emailTo
        , @body=@emailBody
        , @subject=@emailSubject
        , @importance='High'
END        
        
RAISERROR ('Linked Server Failure', 16, 1, @emailSubject) WITH LOG

END CATCH

END

GO

Rate

4.5 (4)

Share

Share

Rate

4.5 (4)