471,108 Members | 1,278 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

SQL server job timeouts?

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?

Jul 23 '05 #1
5 8691
(wa****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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.

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

Jul 23 '05 #4
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

Jul 23 '05 #5
(wa****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mircu | last post: by
2 posts views Thread by Bernie Yaeger | last post: by
2 posts views Thread by =?Utf-8?B?Q29ubnVsbA==?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.