CODE: get ip address

February 22, 2013
| No Comments

USE  [leads]

GO

/****** Object:  StoredProcedure [dbo].[sp_get_ip_address]    Script Date: 02/22/2013 17:08:45 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATE Procedure [dbo].[sp_get_ip_address](@web varchar(100), @ip varchar(15) out)

as

begin

Declare @ipLine varchar(200)
Declare @pos int
DECLARE @strCmd VARCHAR(60)
DECLARE @strServerIP VARCHAR(100)
DECLARE @result VARCHAR(15)

set nocounton       

set @ip =null   

set @strServerIP =@web

SELECT @strCmd =‘ping ‘+ @strServerIP

Insert temp2   EXEC Master..xp_cmdShell @strCmd

select @ipLine = ipLine from temp2
WHERE     (ipLine LIKE‘Ping statistics for %’)

if(isnull(@ipLine,‘666’)!=‘666’)

begin

set @pos =CharIndex(‘r ‘,@ipLine,1)

set @ip =rtrim(ltrim(substring(REPLACE(@ipLine,‘:’,”),@pos+ 1 len(@ipLine)– @pos)) )

end

else

set @ip =‘666’

delete temp2

set nocountoff

end

——————————————-

USE  [leads]

GO

/****** Object:  StoredProcedure [dbo].[sp_PingBLSWebsites]    Script Date: 02/22/2013 17:20:50 ******/

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

CREATE PROCEDURE [dbo].[sp_PingBLSWebsites]

— Add the parameters for the stored procedure here

AS

 

DECLARE @counter int
DECLARE @result VARCHAR(15)
DECLARE @web varchar(100)
DECLARE @webs varchar(100)
DECLARE @ip varchar(15)

set @counter = 0

— To start set all IP’s to be pinged

— ping all the IP’s

WHILE @counter <(SELECTcount(*)FROM [leads].[dbo].[tbl_contacts] where [URLip]isnull)

BEGIN

SELECT  top 1  @web=replace([URL],‘http://’,”),@webs=[URL] FROM [leads].[dbo].[tbl_contacts] where [URLip] isnull

exec sp_get_ip_address @web,  @ip out

UPDATE [leads].[dbo].[tbl_contacts]

SET URLip=rtrim(@ip)

WHERE [url]=@webs

END
set @counter=@counter+1

mssql