set ANSI_NULLSON
set QUOTED_IDENTIFIERON
go
— =============================================

— Author: Clive Roberts

— Create date: 30 november 2007

— Description: pings the ip in the table tbl_ip_ping_domain

— and set to success or fail

— =============================================

CREATEPROCEDURE [dbo].[sp_PingServer]

— Add the parameters for the stored procedure here

AS

DECLARE @strCmd VARCHAR(60)

DECLARE @strServerIP VARCHAR(100)

DECLARE @counter int

DECLARE @result VARCHAR(15)

set @counter = 0

— To start set all IP’s to be pinged

UPDATE dbo.tbl_ip_ping_domain

SET Stop= 0

WHERE stop=1

— ping all the IP’s

WHILE @counter <(SELECTCount(DISTINCT customerwebsite)

FROM [PingService].[dbo].[tbl_ip_ping_domain])
BEGIN

SET @counter = @counter + 1

SELECTDistincttop 1

@strServerIP =customerwebsite

FROM dbo.tbl_ip_ping_domain

WHERE stop=0

SELECT @strCmd =’ping ‘+ @strServerIP

EXEC @result=Master..xp_cmdShell @strCmd

IF(@result = 0)

UPDATE dbo.tbl_ip_ping_domain

SET stop=1

WHERE customerwebsite IN

(SELECTTOP 1 customerwebsite

FROM dbo.tbl_ip_ping_domain

WHERE stop=0)

ELSE

UPDATE dbo.tbl_ip_ping_domain

SET Stop= 2,

stoppeddate=GETDATE()

WHERE customerwebsite IN

(SELECTTOP 1 customerwebsite

FROM dbo.tbl_ip_ping_domain

WHERE stop=0)

END