SQL server job timeouts?

[wamatt]

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]

(wa****@gmail.c om) 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, es****@sommarsk og.se

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

[wamatt]

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]

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

[wamatt]

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_Clien t_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_Clien t_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_Clien t_CC @Code
FETCH NEXT FROM curClients INTO @Code
END
END
DEALLOCATE curClients

[Erland Sommarskog]

(wa****@gmail.c om) 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_Clien t_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, es****@sommarsk og.se

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