finding ip address with stored procedure

Can anybody tell me if a stored procedure exists that pings a server and returns the ip address from within SQL Server email address. Any assistance will be highly appreciated.

CREATE PROCEDURE sp_PingServer
(
@ServerName varchar(50),
@DesireEmail bit
)
AS
/*********************************
ProcName: sp_PingServer
Author: Chris Thibodeaux
Email: chris.thibodeaux@sscims.com
Date: 1/15/2001

Purpose: Returns IP Address of
Destination server via T-SQL

Variables: @ServerName - (The Destination Server)
@DesireEmail - (Added for Swynk User)


**********************************/
SET NOCOUNT ON

-- Declare Local Variables
DECLARE @Sql varchar(500)
SET @Sql = 'PING '+@ServerName+' -n 1'

-- Create Temp Table
CREATE TABLE #temp1
(t varchar(3000))

-- Insert PING Data Into Temp Table
INSERT INTO #temp1
EXEC master..xp_cmdshell @Sql

IF (@@ERROR = 0)
BEGIN
-- Sends Email if An Email is Desired.
IF (@DesireEmail = 1)
BEGIN
EXEC xp_sendmail @Recipient = 'YourRecipient',
@Subject = 'YourSubject',
@query = 'SELECT TOP 1 SUBSTRING(t, 12, CHARINDEX('':'', t, 12)-12) from #temp1 ORDER BY t DESC'
END
ELSE
BEGIN
-- Simply Returns The IP Address of the Server
SELECT TOP 1 SUBSTRING(t, 12, CHARINDEX(':', t, 12)-12) from #temp1 ORDER BY t DESC
END
END
ELSE
BEGIN
RAISERROR('ERROR - Pinging The Destination Server', 16, 1)
END

-- Drop the Temp Table
DROP TABLE #temp1

RETURN(0)
GO


------------
ade fatona at 2/16/01 10:36:05 AM

Can anybody tell me if a stored procedure exists that pings a server and returns the ip address from within SQL Server email address. Any assistance will be highly appreciated.

IF EXISTS (SELECT * FROM SysObjects WHERE XType = 'P' AND NAME = 'sp_PingServer')
BEGIN
DROP PROCEDURE sp_PingServer
END
GO

CREATE PROCEDURE sp_PingServer
(
@ServerName varchar(50),
@DesireEmail bit
)
AS
/*********************************
ProcName: sp_PingServer
Author: Chris Thibodeaux
Email: chris.thibodeaux@sscims.com
Date: 1/15/2001

Purpose: Returns IP Address of
Destination server via T-SQL

Variables: @ServerName - (The Destination Server)
@DesireEmail - (Added for Swynk User)


**********************************/
SET NOCOUNT ON

-- Declare Local Variables
DECLARE @Sql varchar(500)
DECLARE @t1 varchar(18)
DECLARE @Err varchar(100)

-- Set Local Variables
SET @Sql = 'PING '+@ServerName+' -n 1'
SET @Err = 'Error - Pinging The Destination Server - '+UPPER(@ServerName)

-- Create Temp Table
CREATE TABLE #temp1
(t varchar(3000))

-- Insert PING Data Into Temp Table
INSERT INTO #temp1
EXEC master..xp_cmdshell @Sql

SET @t1 = (SELECT top 1 t from #temp1 order by t DESC)

IF @T1 = ('Request timed out.')
BEGIN
RAISERROR(@Err, 16, 1)
RETURN(-1)
END

IF (@@ERROR = 0)
BEGIN
-- Sends Email if An Email is Desired.
IF (@DesireEmail = 1)
BEGIN
EXEC xp_sendmail @Recipient = 'YourRecipient',
@Subject = 'YourSubject',
@query = 'SELECT TOP 1 SUBSTRING(t, 12, CHARINDEX('':'', t, 12)-12) from #temp1 ORDER BY t DESC'
END

-- Simply Returns The IP Address of the Server
SELECT TOP 1 SUBSTRING(t, 12, (CHARINDEX(':', t, 12)-12)) FROM #temp1 ORDER BY t DESC
END


-- Drop the Temp Table
DROP TABLE #temp1

RETURN(0)
GO


------------
ade fatona at 2/16/01 10:36:05 AM

Can anybody tell me if a stored procedure exists that pings a server and returns the ip address from within SQL Server email address. Any assistance will be highly appreciated.