We have a scheduled DTS package that imports data into MS Great Plains Dynamics. The package ran fine for a week. Then on Saturday the server was restarted. Since then any DTS packages scheduled via an Agent Job hangs. Running the DTS package manually works fine. When running the DTS package manually Dynamics is started and can be seen on the server screen. However when run via the agent Dynamics can not be seen but is stated as running within 'Task Manager' --> 'Processes'. The DTS package contains VB scripts, to which I added message boxes to check where the package starts too hang. But the agent also hangs when the script gets to a message box without displaying the message box. I have checked the service agent's login which is as before the problems began. Any help would be greatly appreciated.
I stopped the Agent Service and then changed the logon of the SQL Agent to the local machines administrator logon (it was as you thought set up with a DOMAIN logon) and set the startup as automatic. I then started the job via the SQL Agent within enterprise manager and again the job hanged as soon as it got to a procedure within the DTS package that causes a window to be displayed. Now.... we also have scheduled backup jobs that within the sql agent that run silent and these are fine. This is really odd, everything was fine until the restart.
Thanks for your help SG, I hope you can shed some more light on this as I'm baffled.
Well now I think we are moving closer to the cause.....
Check your job that runs the DTS package - is it owned by sa?. This will eliminate if there are any permissions problems running the package.
Change it to have owner of sa.
Sounds like the app that opens in the window may not have either the right permissions ( hence why I ask to check who owns the scheduled job ) or the app that opens in the window may be hanging as it cant find something or isnt allowed to execute something. Take out that window step & see what happens. That will at least tell you if thats the cause for the overall job hang.
Tried running the job with the owner set to sa. Still the job hangs as soon as it needs to display a window. If I remove all procedures that display windows the job runs fine, but due to the nature of the package, windows do need to be displayed.
Sorry for the late reply but I have been very busy.
I've checked all the logons(SQLSERVERAGENT, JOB OWNER) and the groups they belong to. Every thing is fine.
But I have come across something else that may be the cause. The job is running everything silently, i.e. I can shell.run a program of which no windows are displayed, but is running under NT processes.
Therefore I was wondering if you know of any setting that enables/disables silent running of sql agent jobs.