Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ping Linked servers Expand / Collapse
Author
Message
Posted Wednesday, November 2, 2011 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:00 AM
Points: 7, Visits: 292
Comments posted to this topic are about the item Ping Linked servers
Post #1199140
Posted Wednesday, November 2, 2011 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:46 PM
Points: 8, Visits: 65
Cannot get it to work. Keep getting syntax errors.
Post #1199260
Posted Wednesday, November 2, 2011 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:00 AM
Points: 7, Visits: 292
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
Posted Wednesday, November 2, 2011 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:46 PM
Points: 8, Visits: 65
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
Posted Wednesday, November 2, 2011 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:00 AM
Points: 7, Visits: 292
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
Posted Wednesday, November 2, 2011 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:00 AM
Points: 7, Visits: 292
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
Posted Wednesday, November 2, 2011 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:46 PM
Points: 8, Visits: 65
That worked.
Post #1199352
Posted Wednesday, November 2, 2011 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:00 AM
Points: 7, Visits: 292
Your Welcome, I am glad.
Post #1199360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse