HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Function to ping in SQL Server
admin
Whilst the following is an sp and not a function, its use would be similar, it will return in the following formats:


--Usage:
usp_ping '192.168.49.16'



Status Details
------ --------------------------------------------
online Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),



Status Details
------ --------------------------------------------
offline Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),



CREATE PROCEDURE dbo.usp_ping @ServerIP VARCHAR(15)
AS
    DECLARE @Command VARCHAR(1000)
    DECLARE @ResponseLineCount INT
    SET NOCOUNT ON
    CREATE TABLE #tmpPingResult
        (
          [Result] [varchar](1000) NULL
        )
    SELECT  @Command = 'ping ' + @ServerIP + ' -n 1'
    INSERT  INTO #tmpPingResult
            ( Result )
            EXEC Master..xp_cmdShell @Command
    UPDATE  #tmpPingResult
    SET     Result = LTRIM(RTRIM(Result))
    DELETE  FROM #tmpPingResult
    WHERE   REPLACE(REPLACE([Result], CHAR(10), ''), CHAR(13), '') = ''
            OR Result IS NULL
    SELECT  @ResponseLineCount = COUNT(*)
    FROM    #tmpPingResult
    IF @ResponseLineCount = 4
        BEGIN
            SELECT  'offline' AS [Status] ,
                    ( SELECT    Result
                      FROM      #tmpPingResult
                      WHERE     Result LIKE 'Packets: Sent = %'
                    ) AS Details
        END
    ELSE
        IF @ResponseLineCount = 6
            BEGIN
                SELECT  'online' AS [Status] ,
                        ( SELECT    Result
                          FROM      #tmpPingResult
                          WHERE     Result LIKE 'Packets: Sent = %'
                        ) AS Details
            END
   --SELECT * FROM #tmpPingResult
    DROP TABLE #tmpPingResult
GO


Regards

J.
Edited by admin on 24-08-2010 11:04
Jordon Pilling | Heavencore Administrator
 
Jump to Forum