xp_cmdshell to return ping-result

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

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

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