Ping with SQL stored procedure possible ? 2

[Webkins]

Good afternoon. I would like to perform a single ping to a specific piece of equipment to determine if it is online or not before attempting to transfer data to this device. Is this possible with a MSSql 2000 stored procedure ? Thank you for any assistance.

 

[Webkins]

Here's what I have so far that works:

CREATE PROC sp_PingServer
AS
DECLARE @strCmd VARCHAR(60)
DECLARE @strServerIP VARCHAR(50)
SET @strServerIP = '172.16.2.222'
SELECT @strCmd = 'ping ' + @strServerIP
EXEC Master..xp_cmdShell @strCmd
GO

Now I need to figure out how to evaluate the results. Any ideas ?
Also I don't need any results displayed, just a 1 or a 0 within the procedure to evaluate.

Thank you

 

[JoeAtWork]

I'm wondering why you would want to do this in your SQL Server in the first place? Sounds like something that is more appropriately done at a higher tier in your application. I.e., do the Ping in some executable, and based on the result call the appropriate stored procedure in the database.

There's an interesting discussion on why not to use your SQL Server to do work outside of its data management duties:

http://www.tek-tips.com/viewthread.cfm?qid=1561624&page=1

 

[gmmastros]

I agree with JoeAtWork. You really should pick the right tool for the job. In this case, you are trying to drive a nail with screwdriver. You can probably get it to work, but should you?

However, if you insist. You can capture the output of a command line app by creating a temp table and then using Insert Exec, like this:

Alter PROC sp_PingServer
AS
DECLARE @strCmd VARCHAR(60)
DECLARE @strServerIP VARCHAR(50)
Create Table #Temp(Data VarChar(1000))

SET @strServerIP = '172.16.2.222'

SELECT @strCmd = 'ping ' + @strServerIP
Insert Into #Temp EXEC Master..xp_cmdShell @strCmd

If Exists(Select * From #Temp Where Data Like '%Lost = 0%')
  Select Convert(Bit, 1) As Result
Else
  Select Convert(Bit, 0) As Result

Drop Table #Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom