Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: SQL Server 7 Agent DTS Job Hangs

    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.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    WHat is the security ( logon used ) to run the SQL Agent? If you are using a domain account, that could cause a problem ( maybe network password has changed? )

    Try setting up the Agent to run as an automated job with local system account ......that shoudl work.

    Post back if probs.

    Cheers,

    SG

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    Hiya SG,

    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.

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    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.

    Cheers,

    SG.
    Last edited by sqlguy7777; 11-02-03 at 19:52.

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    Hiya SG,

    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.

    Thanks for your help,

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Well then I guess time to investigate what opens the window and what it does....what permissions it needs etc.

    Cheers,

    SG

  7. #7
    Join Date
    Oct 2003
    Posts
    5
    Hiya SG,

    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.

    Thanks for your help,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •