Ping Linked servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mark Huber » Ping Linked servers

Ping Linked servers

Rate Topic

Display Mode

Topic Options

Author

Message

mark.the.dba

Posted Wednesday, November 02, 2011 3:12 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:00 PM
Points: 7, Visits: 233

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

Post #1199140

dlchase

Posted Wednesday, November 02, 2011 6:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 6:07 AM
Points: 7, Visits: 47

Cannot get it to work. Keep getting syntax errors.

Post #1199260

mark.the.dba

Posted Wednesday, November 02, 2011 7:44 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:00 PM
Points: 7, Visits: 233

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

Post #1199283

dlchase

Posted Wednesday, November 02, 2011 7:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 6:07 AM
Points: 7, Visits: 47

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'.

Post #1199288

mark.the.dba

Posted Wednesday, November 02, 2011 8:05 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:00 PM
Points: 7, Visits: 233

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

Post #1199295

mark.the.dba

Posted Wednesday, November 02, 2011 8:08 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:00 PM
Points: 7, Visits: 233

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

Post #1199298