-
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.
-
finding ip address with stored procedure (reply)
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.
-
Updated Stored Proc (Bug Fixes)
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|