Awesome script. Works like a clock. Thanks.

You do not need to have a stored procedure there.

Made 3 modifications:

1. Converted it an ad hoc query using the sysprocesses.hostname column

2. Expanded some string variables to hold longer strings

3. Filtered empty hostnames and duplicates as following (cut execution time by half):

EXEC('INSERT INTO #TblHostName (Hostname) Select distinct hostname from sysprocesses where hostname <> ''''')

The altered version below:

You could drop BEGIN/END (I did not bother)

use master;

BEGIN

CREATE TABLE #Results ( Results VARCHAR(4000) )

DECLARE @Commandstring VARCHAR(4000) ,

@IP VARCHAR(300) ,

@ctr VARCHAR(600) ,

@Hostname VARCHAR(128)

CREATE TABLE #TblHostName

(

ID INT IDENTITY(1, 1) ,

Hostname VARCHAR(255) ,

IpAddress VARCHAR(32)

)

EXEC('INSERT INTO #TblHostName (Hostname) Select distinct hostname from sysprocesses where hostname <> ''''')

SELECT @ctr = MAX(id)

FROM #TblHostName

WHILE @CTR > 0

BEGIN

SET @IP = 'NOT FOUND'

SELECT @Hostname = hostname

FROM #TblHostName

WHERE id = @ctr

SET @Commandstring = 'ping ' + @Hostname

--Insert the string in temp table

INSERT INTO #Results

EXEC master..xp_cmdshell @Commandstring

--Get the IP address from the string

SELECT @IP = SUBSTRING(Results, CHARINDEX('[', Results) + 1,

CHARINDEX(']', Results)

- CHARINDEX('[', Results) - 1)

FROM #Results

WHERE Results LIKE 'Pinging%'

UPDATE #TblHostName

SET IPADDRESS = @IP

WHERE id = @ctr

TRUNCATE TABLE #Results

SELECT @ctr = ( @ctr - 1 )

END

SELECT *

FROM #TblHostName

DROP TABLE #TblHostName

DROP TABLE #Results

END

Alex Donskoy

SQL DBA at GTLaw

Miami FL