Microsoft SQL Server forum - SQL server job timeouts?

Author

SQL server job timeouts?

wamatt@gmail.com

2005-06-09, 11:23 am

Subject: SQL server job timeouts?

We have a job that uses WinHTTP inside a stored procedure. We have
another SP wrapper that runs this for a couple hundred records.

When we add this as a job using SQLAgent it times out half way. Running
it from SQL Query analyser and it completes to the end.

Anyway to set the timeouts for jobs?

Erland Sommarskog

2005-06-09, 8:23 pm

(wamatt@gmail.com) writes:
> Subject: SQL server job timeouts?
>
> We have a job that uses WinHTTP inside a stored procedure. We have
> another SP wrapper that runs this for a couple hundred records.
>
> When we add this as a job using SQLAgent it times out half way. Running
> it from SQL Query analyser and it completes to the end.
>
> Anyway to set the timeouts for jobs?

Exactly which component bitches about the timeout? It should not really
be SQL Server Agent, as a job could run for a long time. WinHTTP is a
complete unknown to me.

Could you explain the exact message you get? Don't forget to expand Job
History, and look at the output from the individual job steps.

One possible workaround, is that you can set up a retry interval and a
retry count for the job step, so if fails half-way, it could get a
second change to do the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

wamatt@gmail.com

2005-06-17, 11:24 am

I removed the job so I don't have the failure history. I will check up
on that.

Basically WinHTTP / XMLServerHTTP or whatever posts HTTP to the credit
card processor.

I have say 100 records a day, for each record (using a cursor) it
calls a sub storedprocedure that process that transaction.

Now the overall stored procedure (the one with the cursor) can take
quite a while to process as its doing a large number of HTTP requests
serially. eg 5 minutes.

>One possible workaround, is that you can set up a retry interval and a

I thought of that workaround thanks, just wondered if there was a more
elegant way.

If I run it through SQL query analyser it works fine.

pb648174

2005-06-20, 9:23 am

There should be a timeout for the proxy you are using to make WinHTTP
calls. Should be something like Timeout, CommandTimeout,
ConnectionTimeout, etc.

wamatt@gmail.com

2005-07-03, 8:23 pm

Ok however its not actually the WinHTTP that times out - its the stored
procedure wrapper itself.

The wrapper is as follows as you can see it calls "EXEC
SFIN_Bill_Client_CC @Code" that inner SP contains the WinHTTP call.
It might run through 50 records or so before the wrapper times out.

This only happens when its called via SQL job. Using query analyser it
works fine.

ALTER PROC SFIN_Bill_Client_CC_
All AS
DECLARE @Code varchar(5)
DECLARE curClients CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT CODE FROM VFIN_CC WHERE Autobill = 1 AND Amount > 0
OPEN curClients

FETCH NEXT FROM curClients INTO @Code
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
EXEC SFIN_Bill_Client_CC @Code
FETCH NEXT FROM curClients INTO @Code
END
END
DEALLOCATE curClients

Erland Sommarskog

2005-07-03, 8:23 pm

(wamatt@gmail.com) writes:
> Ok however its not actually the WinHTTP that times out - its the stored
> procedure wrapper itself.
>
> The wrapper is as follows as you can see it calls "EXEC
> SFIN_Bill_Client_CC @Code" that inner SP contains the WinHTTP call.
> It might run through 50 records or so before the wrapper times out.
>
> This only happens when its called via SQL job. Using query analyser it
> works fine.

Well, did you try again, and this time saved the complete error message
from the Step Details in the job history? It would help to see it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links