May 23, 2005 at 12:41 pm
| I have a DTS package that moves data from an accounting package into a SQL Server DB hourly. Occaisionally, the package gets stuck and the job remains in an "executing" status until it is manually stopped. Because I wanted to setup e-mail notifications upon this type of failure, and because Exchange or another MAPI mail system is not present on this machine, I setup a stored procedure to send e-mails via the CDOSYS object model. The job has two steps. The first executes the DTS package. The second sends the alert by executing the stored procedure via transact SQL statement. I have the first job set to quit with success so that the failure would never be sent if the DTS package works. I have it set to proceed to the next step upon failure so that the alert would be sent. So the problem, as mentioned above, is that when it doesn't work the job is just remainining in an executing status and the first step is never failing (and the the notification never gets sent.) Is there someway that I can have the DTS package or the first job step time out after a certain amount of time (say 10 minutes) so that a failure occurs? Thanks very much. Greg Crout |
May 23, 2005 at 12:52 pm
Hi,
Did you try a second job (not a second step) that will run 10 min after your job starts and will check on sysjobhistory in MSDB looking for run_status and run_duration?
Yelena
Regards,Yelena Varsha
May 24, 2005 at 5:34 am
Make sure you haven't left anything that displays a message in any scripts in the job - e.g. a MsgBox in ActiveX script etc - try running the job interactively logged in as the same user the scheduler is using and watch out for any screen prompts as when scheduled these will never get answered
James Horsley
Workflow Consulting Limited
May 24, 2005 at 7:25 am
I have the same problem with a remote Oracle DB that I download from nightly. I think it has to do with loosing the connection and DTS cannot detect that the connection is broken, so it waits. I run a second job at a time when I know the download should be finished. It consistes of one TSQL command to stop the job:
USE msdb
EXEC sp_stop_job @job_name = 'YourJobNameHere'
If the job is not running I get a job step failure, so I have it quit reporting success.
If the job is running, it stops the job and gives a job step success, so I have it quit reporting failure, then I use the job monitor to page me that the intial job has failed.
I know this sounds backwards, but it works.
Hope this helps,
Michael Lee
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply