SQL Server Performance Forum – Threads Archive

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

---

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

---

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

---

]]>