Timeout for a step in a Sql Agent Job 2

[fredp1]

Enviroment: SQL2000sp3, Win 2000k sp2.
Limited Experience in SQL server.

Question: Where in SQL Server do you set some timeout so that the step in the Job will fail after a certain time?

History: The sql server runs our datawarehouse. The tool used to create datamarts basically creates a dts package. All the dts packages are then run from a Job using the OS cmd DTSRun and the name of the package.
The package basically pulls data from a AS/400 to the SQL server staging db using ODBC. The transformation is a simple column to column copy.

What I want to be able to do is put some sort of a timeout on the step if it is not completed by a reasonable time.
The problem that I found was I had the AS/400 IPLed while SQL was running the Job. It was still stuck 24hours later trying to execute that step in the job.

Where in SQL Server do you set some timeout so that the step in the Job will fail after a certain time?

In the ODBC Source, advance connection properties of the package has a Connect timeout of 1200,
the General timeout is blank.
In the SQL destination, advance connection properties, the general timeout is 0.

Also if the step runs a Transact SQL commnad, specifically a Stored procedure, where are the timeouts maintained to have the step completed by a certain time.

Any hints?

Thanks

 

[tlbroadbent]

I'm not aware of any way to "timeout" a DTS package. However, I believe there must be a method to do so. As an interim measure, you could schedule another Job to run a few hours after the main Job. The Job could execute small SQL script that would check if the Job had finished and if not, it could execute sp_stop_job.

The following example shows how to see if a job is still running and how to stop it. It also sends an Email message indicating the Job was terminated.

If Exists
(Select *
From msdb.dbo.sysjobhistory h
Join msdb.dbo.sysjobs j
On h.job_id = j.job_id
--Search for your job name
Where j.name = 'Build Data Warehouse'
--Adjust the date as needed
And h.run_date = convert(char(8),getdate(),112)
--Status 4 means the Job is In Progress.
And h.run_status = 4)

Begin
exec msdb.dbo.sp_stop_job 'Build Data Warehouse'
exec master.dbo.xp_sendmail
@subject='Build Data Warehouse',
@message='The Build Data Warehouse job has been terminated abnormally due to excessive run time.',
@recipients='fredp1@yourdomain.com'
End If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fredp1]

Thanks very much for the reply.
The suggestion and sample code submitted is excellent.

Thanks again.

Fred