Re: Kill job that is executing endlessly

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@xxxxxxxxxxx> wrote in message
news:ecEm065XFHA.3184@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>


.

Relevant Pages

  • Re: DTS Tasks running in job random failure
    ... > then uninstalled (created a unforseen problem on the server). ... > a lookup, which I'd like to follow Microsoft's advice for a workaround to ... > Job now running is one Job executing one DTS Package that executes 6 DTS ...
    (microsoft.public.sqlserver.dts)
  • Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... open connection throughout this process. ... It opens it when the process is ... I have run some profiling on the server. ... >> The windows service executes on a workstation. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: setup an offline connection
    ... If you are calling the package in VB then you can simply use the object ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > the laptop to a client, I would like to modify the DTS package to ... > delete the connection to my computer and put the connection to their ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Package - truncate, drop index and migrate data in same DTS package
    ... server to another. ... server, the transformation and Connection 2, the destination server) ... table and drop the index on the table located at Connection 2. ...  But I would like to do it in the same DTS package. ...
    (microsoft.public.sqlserver.dts)
  • Executing DTS from Code
    ... I have VB.Net code that executes a store procedure that executes a DTS package. ... Everything works fine on my development workstation which has SQL Server ...
    (microsoft.public.sqlserver.dts)