Results 1 to 3 of 3

Thread: finding ip address with stored procedure

  1. #1
    ade fatona Guest

    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.

  2. #2
    Chris Thibodeaux Guest

    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.

  3. #3
    Chris Thibodeaux Guest

    Updated Stored Proc (Bug Fixes)

    IF EXISTS (SELECT * FROM SysObjects WHERE XType = 'P' AND NAME = 'sp_PingServer&#39
    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.&#39
    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
  •