SQL Server Performance

xp_cmdshell to return ping-result

Discussion in 'General DBA Questions' started by frettmaestro, Dec 19, 2002.

  1. frettmaestro New Member

    Hi there,

    we have a small server-park here where I work and I have a table in one of my sql-servers that has a list of all the servers in the domain with IP-adresses and a status. What I want to do is to create a stored procedure that pings all of these servers and from the ping-result sets the status to "running" or "shut down" or something. I have created a stored procedure for this where I try to use xp_cmdshell to retrieve the ping-result but I can't seem to get it to work.

    The procedure looks something like this:


    CREATE PROCEDURE dbo.sp_PingServer
    AS
    DECLARE
    @Peker Cursor,
    @Command varchar(100),
    @ServerID int,
    @ServerIP varchar(50),
    @Status varchar(50),
    @PingResult varchar(300)

    SET NOCOUNT ON

    Set @Peker = Cursor for (SELECT ServerID, ServerIP FROM Servere)
    Open @Peker
    FETCH NEXT FROM @Peker INTO @ServerID, @ServerIP
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SELECT @Command = 'ping ' + @ServerIP
    SELECT @PingResult = EXEC Master..xp_cmdShell @Command

    IF (@PingResult LIKE '%Unknown host%')
    BEGIN
    UPDATE Servere SET Status = 'shut down' WHERE ServerID = @ServerID
    END
    ELSE IF (@PingResult LIKE '%Recieved = 4%')
    BEGIN
    UPDATE Servere SET Status = 'running' WHERE ServerID = @ServerID
    END
    FETCH NEXT FROM @Peker INTO @ServerID, @ServerIP
    END
    Close @Peker
    Deallocate @Peker
    GO
    I get syntax error near 'EXEC' and it also seems like the xp_cmdshell only can retur 1 or 0 depending on if the command was run or not.

    How do I solve my problem??

    --
    Frettmaestro
  2. satya Moderator

    Instead you can use

    SELECT @Command = 'ping ' + @strServerIP
    EXEC Master..xp_cmdShell @Command


    In SQL 2K a SQLDMO method is available which is PingSQLServerVersion method returns a long integer that describes an instance of SQL Server, refer to books online for information.

    HTH

    Satya SKJ
  3. frettmaestro New Member

    The point is that I need to assign the ping-result to a variable so that my procedure can check if the server is up or not...

    --
    Frettmaestro
  4. frettmaestro New Member

    This is what I ended up with:



    CREATE PROCEDURE dbo.sp_PingServer
    AS
    DECLARE
    @Cur Cursor,
    @Command varchar(100),
    @ServerID int,
    @ServerIP varchar(50),
    @Status varchar(50),
    @PingResult varchar(300)
    SET NOCOUNT ON

    CREATE TABLE #tmpPingResult
    (
    [ResultID] [int] identity NOT NULL,
    [Result] [varchar] (1000) NULL
    )

    Set @Cur = Cursor for (SELECT ServerID, ServerIP FROM server)
    Open @Cur
    FETCH NEXT FROM @Cur INTO @ServerID, @ServerIP
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SELECT @Command = 'ping ' + @ServerIP + ' -n 1'
    INSERT INTO #tmpPingResult (Result)
    EXEC Master..xp_cmdShell @Command

    IF (SELECT Result FROM #tmpPingResultat WHERE Result LIKE '%Unknown host%') <> ''
    UPDATE server SET Status = 'Down/Does not exist' WHERE ServerID = @ServerID
    ELSE IF (SELECT Result FROM #tmpPingResult WHERE Result LIKE '%Request timed out%') <> ''
    UPDATE server SET Status = 'No reply' WHERE ServerID = @ServerID
    ELSE IF (SELECT Result FROM #tmpPingResult WHERE Result LIKE '%Sent = 1, Received = 1%') <> ''
    UPDATE server SET Status = 'Running' WHERE ServerID = @ServerID

    TRUNCATE TABLE #tmpPingResult
    FETCH NEXT FROM @Cur INTO @ServerID, @ServerIP
    END
    Close @Cur
    Deallocate @Cur

    DROP TABLE #tmpPingResultat


    GO

    --
    Frettmaestro

Share This Page