I have a DTS package that is scheduled to pull read-only data from an
Informix server every 10 minutes. When it works, it takes less than 10
seconds. I have *zero* control over the Informix server, so all error
handling *must* be done on the SQL Server side. At times, this job executes
endlessly as if the table being read is locked, or similar. In the DTS, in
the connection to the Informix server, I have specified the Connect Timeout
and General Timeout both as 30 (seconds) in the Advanced Connection
Properties. However, this morning I check the job manually and it has been
executing since Saturday morning.

Are there work arounds for this on the SQL Server side? It strikes me that
some connection property should allow for an automatic stop of the
job/query, but I'm willing to create a programatic solution if that is the
only way.

SQL Server 2000, Windows 2003 Server, all patches.

Thanks in advance.

Mark

I'm almost certain this would work, but it's ugly. In job #1 (the one which
executes the DTS package in question), insert a record into a log table with
getdate() in a column called [time_started]. On completion of the package,
update the [time_completed] column of the same record with getdate(). You
now have a record of when job #1 started and when job #1 completes. If
[time_completed] is NULL, then the job is still running. Have another
scheduled job (job #2) which every 1 hour or so querys this log table to
determine if job #1 has been running for more than say 1/2 hour. If so, then
execute the sp_stop_job procdure to stop job #1.

"Mark" <Mark (AT) nowhere (DOT) com> wrote

Hi,

JT wrote:
I don't think you need this extra table.
You can just call sp_help_job to get start time and execution status of the jobs.
When you check wether status is 1 or maybe some other status (see SQLDMO_JOBEXECUTION_STATUS)
and compare the date & time you should be able to fined timedout jobs.

Helge

Sure, if you must be a minimalist. ;-)

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote