May 30, 2008 at 2:44 am
We have several jobs that run DTS extracts from an Oracle DB into a Sql2000 DB. These are scheduled through the SQL Agent scheduler. They have worked successfully for many months, but 3 of them have now started to hang after completion of the DTS transfer. (The other 5 continue to work well). The DTS packets work and complete when run manually. When scheduled these 3 run and transfer/load the data and the DTS log shows that they have completed, but the agent jobs hang and do not stop. They have to be killed manually.
There is a MS paper on this issue which gives the workaround of setting the 'Execute on main package thread' option on the DTS data transformation.step. I've done this but it has not helped.
Any help would be appreciated. Even if anyone knows how to set a 'max run time' for a scheduled job!! I'm getting desperate!!;)
May 30, 2008 at 5:03 pm
Just to post that we have managed to solve the problem. The DTS system scheduled the jobs using DTSRun. It appears that the return code wasn't being passed back to the scheduler by the DTS routine. No running them under the DTSRunUI utlity, they are working fine again.
I can't explain why they suddenly stopped, but at least they're ok now.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply