Scheduled DTS job never completes

[jjlbmcnews]

Hi,

I have a DTS package that I have put together that pulls data from Oracle into MSSQL 2000. The actual package works fine as I'm able to execute it and it completes. I then schedule this package to run at a specific time every night and it executes but it never completes. The status just shows as Executing Job Step 1. Has anyone ever seen something like this before?

 

[Catadmin]

It sounds like it's getting hung up waiting for resources. Is it running at the same time as a backup? Worse, is someone running a query or left their PC logged in with an open connection that is locking the tables?

Run Profiler when this happens and look for things like this.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[yksvaan]

This is a bit silly, but are you sure you clicked Refresh in EM, since it doesn't refresh the status display automatically? Did the package do what it was supposed to?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[jjlbmcnews]

It shouldn't be getting hung for resources as this server is not being over used, also the DTS package executes fine.

Yes, I've refreshed the EM several times and it doesn't make a blind bit of difference.

 

[yksvaan]

This is quite obvious too, but how about the steps, does the last executing step has the succeed setting "Quit the job reporting success" ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[NiallWestland1974]

I've had a similar problem.

After reading a few discussion forums, where installing service pack 2 was mentioned, among other things, I finally got to the bottom of the problem, in my case at least.

The scheduled DTS was executing at 8pm - the same time as the Veritas back-up for the server was scheduled - it had timed-out too - so it was a server resources issue - changing the schedule ensured both executed and completed.

 

[mercurymik]

I had the same problem when I used an "Oracle Provider for OLE DB" connection that I defined in the DTS package. What I did to overcome that was create a System DSN using the Oracle ODBC driver that loaded with the Oracle client. In the DTS package I chose the "Other Connection" and pointed that to the System DSN.

 

[datenis]

I've had the same problems with a job that runs an OS command. When I run the program manually it exits properly. If it's run automatically as a job then the job never completes. We're not running other jobs in parallel.

I've read on other forums to use the sp_stop_job command as a second job to stop the first job. Is there an example on how to use it stop the first job if it has run for more than 15 minutes?

Thanks so much.

-D