How to set SQL Server 2005 Job CmdExec Timeout

Question

I have a job setup in SQL Server 2005 which has an Operating System (CmdExec) step.

The step calls a program which can take a long time to run. I see that if the program takes longer than 1 minute 40 seconds to respond the step fails with an error message "The operation has timed out". The program actually continues to run and generates the desired results.

How can I set the timeout period for the step in this job. Alternatively if there is a way to set the timeout for the entire job that would be just as useful since this is the only step in the job. If all else fails I would be willing to change the timeout for the entire server although obviously this would be a last resort.

I have tried looking into the properties for the step, job, and SQL Server Agent but have not managed to find anywhere to set this option.

Answer 1

SQL jobs don't have timeouts -- you can't configure them to stop on their own if they run too long, or reach a particular point in time. (Woulda saved me a lot of development time if you could!) You can configure steps to check for times or durations, and can code the actions taken within steps to check for times, but at the job or the step level, no.

Which is what makes this an interesting question. Why are you getting a timeout? Based on what you've said, I'd guess that SQL Agent is unable to tell that the OS has received the "do this" command you're sending via the cmdexec step. After sending and waiting, it thinks the job never started and reports accordingly (and there's your hidden system timeout). How is this happening? You could investingate security configurations or file access rights, but I'd start by reviewing whatever routine it is that you're starting up -- something about it smells fishy to me.

Answer 2

The Webservices is your answer. As Phillip pointed out, there is no time out for steps in SQL Server jobs.

A question you might need to ask is why the web service is timing out. In my experience, the problem could be that you're running too large a request, either returning too long a result or resulting in somerthing else that's causing the web servce to run long.

Otherwise you need to talk to the web service provider to extend the time out.