Using PING in a job step

[Guest_imported]

Running SQL Server 7.0 on Windows 2000 server.

I would like to create a step job that pings an IP address of a bridge to verify that it is on the network. When using CMDEXEC job type, the exit code is 0 both when the IP replies and when it does not. The job is always successful. I need it to fail when the ping does not get a reply. The following SQL will help to test because @result equals the exit code.

DECLARE @result int
EXEC @result = xp_cmdshell 'ping invalidip'
SELECT @result

@result will equal 1 if a name is used for the ip address that WINS or DNS can not resolve. I could use a complicated batch file, but if anyone hase anything better, it would be greatly appreciated.

 

[TomSark]

The only thing I could find in books on-line was a SQL-DMO item:
CmdExecSuccessCode Property (SQL-DMO)
The CmdExecSuccessCode property records the process exit code of a command shell process executed as a job step.

You could also try and play with the EXIT function via osql and see if you can return the value issued in the query that way... this is documented in the osql utility section in books-online...

I'll check some other resources and if I find a definitive answer, I'll post it...

Thanks,

Tom

 

[TomSark]

Hi,

If you put a different value than the default value in the process exit code for this step, we all know that this value is what the command returns when it succeeds.

Could you try putting a 1 in here and see if this makes a difference? I'm wondering if you have the default value of 0 in there, which also corresponds to the failure value? Maybe?

Just curious... I'm going to try this as well...

Tom

 

[TomSark]

OK... I did this and it worked, but a little on the backwards side...

I put a 1 in for the default value of the step, when I pinged an INVALID server, it returned a 1 and followed the 'success' workflow...

When I pinged a VALID server, it didn't return a 1 and followed the 'failure' workflow...

I didn't run xp_cmdshell in the step, but rather, I put 'ping <server>' in a cmd file and created a step that ran that...


Hope this helps...

Tom

 

[Guest_imported]

Tom,
Changing the default value of the step does not really help the problem. It just reversed the outcome of the initial problem.

Your ping job failed only because it could not resolve your invalid server name. Change your cmd file to ping an invalid ip address instead, or add the invalid server name to your DNS and you will see that the job succeeds anyway, just like when it pings a valid ip address or name. (Or it fails if you put a 1 on for the default value of the step).

Thanks for trying.

 

[TomSark]

No.... I got it to work when it pinged a valid and an invalid server... they both worked...

Tom

 

[Guest_imported]

Tom,

Just so we are clear.

1. You pinged a numeric ip address(ping 172.31.54.200) and not a computer name(ping computername)?
2. You tested a valid numeric ip address and an invalid numeric ip address and got a job failure for one and a job success for the other?
3. You called a batch file from a CMDEXEC type job step?
4. You changed the &quot;Process exit code of a successful command&quot; to 1.

If all of the above are true, I can't seem to replicate your results. What OS are your running and SQL Version?