Ping Linked servers

  • Comments posted to this topic are about the item Ping Linked servers

  • Cannot get it to work. Keep getting syntax errors.

  • Sorry about that. Can you tell me a bit about what your seeing. A screen shot or the error messages your seeing and I can see what your seeing. I know this is not helpfull it run every 5 minites without issue so I would love to see what your seeing so I can help.

    Mark H

  • I just tried to create the function and got all the errors below.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 24

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 25

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 31

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 32

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 36

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 37

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 38

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 39

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 40

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 41

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 42

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 44

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 1, Procedure usp_lsping, Line 44

    Must declare the scalar variable "@Clr".

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 46

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 47

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 1, Procedure usp_lsping, Line 47

    Must declare the scalar variable "@msg".

    Msg 137, Level 15, State 2, Procedure usp_lsping, Line 48

    Must declare the scalar variable "@msg".

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 50

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 52

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 55

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 58

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 59

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Procedure usp_lsping, Line 61

    Must declare the scalar variable "@msg".

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 63

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Procedure usp_lsping, Line 64

    Must declare the scalar variable "@msg".

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 66

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 72

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 75

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure usp_lsping, Line 91

    Incorrect syntax near 'off'.

  • I found that the code I submitted had some extra characthers attached to most of the lines. I have submitted an update and when it is published this issue will go away. In the mean time you can open it in a text editor and get rid of the extra characthers. And it will compile cleanly. I wll check that better next time. Sorry about that I am not sure how they got in there. But at any rate they are fixed now. I will add the fixed code below

  • Create procedure usp_lsping(@nf nchar = 'N', @wtn nchar = 'E')

    WITH ENCRYPTION

    as

    -- ===========================================================================

    -- Author:Mark Huber

    -- Version:1.0

    -- Create date: Oct 19, 2011

    -- Description:Description to ping the linked server and see if there alive

    -- ===========================================================================

    -- Parameters

    -- @NF - did you want to be notified Y - yes N - no

    -- @wtn - when to notofiy E - on error S - on Succes B - on Both

    --

    -- EXAMPLE USE

    --DECLARE@return_value int

    --EXEC@return_value = [dbo].[usp_lsping]

    --@nf = N'Y',

    --@wtn = N'E'

    --SELECT'Return Value' = @return_value

    -- ===========================================================================

    --

    set nocount on

    BEGIN

    DECLARE @LSrvrs TABLE

    (

    SrvrID int IDENTITY(1,1) PRIMARY KEY,

    SrvName nvarchar(128)

    )

    insert into @LSrvrs

    select srvname from sys.sysservers

    where srvname != CONVERT(nvarchar(128), SERVERPROPERTY('servername'));

    ;

    --

    declare @maxloocnt int;

    declare @loopcnt int;

    declare @srvr nvarchar(128);

    declare @retval int;

    declare @CLR nvarchar(4)

    declare @msg nvarchar(MAX)

    declare @errchk int;

    --

    set @errchk = 0;

    set @CLR = char(13)+char(10);

    select @maxloocnt = count(*) from @LSrvrs;

    set @loopcnt = 1;

    set @msg = '';

    set @msg = @msg + '<Start>'+@Clr

    while @loopcnt <= @maxloocnt

    begin

    select @srvr = srvname from @LSrvrs where SrvrID = @loopcnt;

    --select @srvr

    begin try

    exec @retval = sys.sp_testlinkedserver @srvr;

    end try

    begin catch

    set @retval = sign(@@error);

    set @errchk = 99;

    end catch;

    --if @retval =qwqw 0

    set @msg = @msg + @CLR+@srvr + ':' + cast(@retval as nvarchar(100));

    set @loopcnt = @loopcnt + 1;

    end;

    set @msg = @msg + @CLR+'<end>'+@Clr

    if @NF = 'Y'

    begin

    --if @wtn = 'E' or @wtn = 'S' or @wtn = 'B'

    --BEGIN

    --print 'WTN:'+@wtn

    --print '@errchk:'+ CONVERT(nvarchar(128),@errchk)

    if (@wtn = 'S' and @errchk = 0) or (@wtn = 'E' and @errchk > 0) or(@wtn = 'B' )

    BEGIN

    --select @msg;

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'mark.huber@bp.com',@body=@msg,

    @subject ='Linked Server Ping'--,@query ='select @msg;',

    --@attach_query_result_as_file = 1,@query_attachment_filename ='PingResults.txt'

    END;

    --else

    -- BEGIN

    --

    -- END

    END

    --end

    --else

    --BEGIN

    -- '';

    --END;

    END

    set nocount off

  • That worked.:-)

  • Your Welcome, I am glad. 😀

  • Thanks for the script.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply