November 2, 2011 at 3:12 am
Comments posted to this topic are about the item Ping Linked servers
November 2, 2011 at 6:55 am
Cannot get it to work. Keep getting syntax errors.
November 2, 2011 at 7:44 am
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
November 2, 2011 at 7:49 am
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'.
November 2, 2011 at 8:05 am
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
November 2, 2011 at 8:08 am
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
November 2, 2011 at 8:52 am
That worked.:-)
November 2, 2011 at 9:00 am
Your Welcome, I am glad. 😀
May 12, 2016 at 6:31 am
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply