none
DTS SQL JOB hangs even after the successful completion of the DTS

    Question

  •  

    Hi All,

    I have a DTS package that runs calling a few stored procedures and an activeX script task. The package does some data load. It takes half an hour to complete. I have emails sent at the end of the package execution along with error notifications if any of the step fails.

    The issue is that, the DTS runs successfully. I ran the package from the server and it completes. If I schedule it, it has been running for over a few months without any issues. Now it gives some problems.


    When the job is invoked automatically on the scheduled time, it runs the package. Does what its supposed to do including sending out the email. But is not finishing the job. The job says its still executing but the final step has completed. I have tried logging the package, and it says completed the last step.. But the JOB Log hasn't written anything to the log file..


    Has anyone have any idea what this issue is?

    Any ideas, suggestions will be much appreciated. Please feel free to let me know if there is any additional information you need in order to troubleshoot this issue. I am using SQL Server 2000 DTS with SP4.

    Thanks

    Bhuvana

    Tuesday, October 07, 2008 6:58 AM

All replies

  • Hi Bhuvana,

    Is there anyother job running simultaneously with the DTS ? Since you mentioned that the final step of sending mails is also completed I wanted to know if you did refresh the job and see the status ?

    - Deepak
    Tuesday, October 07, 2008 9:03 AM
    Moderator
  • This is the only job scheduled at that time. No other jobs was scheduled at the time.

     

    Yes i have also refreshed to check the status. I tried reopened enterprise manager and check the status. But still it shows as executing the job. It went for three days. Atlast i have cancelled the job.

     

    Pls help me to solve this...

     

    Thnx,

     

    Regards,

    Bhuvana

    Tuesday, October 07, 2008 10:08 AM
  • Bhuvana,

    This advice might sound weird but I once faced similar thing where the Restore job in log shipping was reporting as in executing state for a couple of days. Finally we had to restart the SQL Server agent service since it was in a hung state. I guess that might be the cause in your case. You can use this as a last option..

    - Deepak
    Tuesday, October 07, 2008 1:20 PM
    Moderator
  • Hi Deepak,

     

    Thanks for answering my question. I had restarted the SQL Server agent service. Again I am facing the same issue  . Is there any other way i can solve this issue?

     

    Pls help me to solve this.

     

    Thanks & Regards,

    Bhuvana

    Wednesday, November 05, 2008 10:12 AM
  • The DTS is waiting for something.  Probably user input.

    Do you have a MsgBox function in your Script?  You cannot run anything with user input in a scheduled job.  It waits forever for someone to click OK on the box no one can see.


    Wednesday, November 05, 2008 5:54 PM
    Moderator
  • No. I am not using an MsgBox in my script.

    I have added an ActiveX script task at the end of the package which executes and SQL Squery which took 20 to 30 mins to execute. Before adding the script my package work fine. After adding the Activex Script the job hangs.

     

    Regards,

    Bhuvana

    Friday, November 07, 2008 5:43 AM
  • put a script towards the end, to close all the open file and db connections used in the DTS. also run a trace, it may just give you a hint.

     

    Friday, November 07, 2008 3:22 PM
  • Hi,

     

    All the db connections are closed in my package. Still i am facing the problem.

     

    When i checked the event viewer -> System, I found the following error message:

    "Application popup: DTSRun.exe - Application Error : The instruction at "0x77fccc79" referenced memory at "0x00003f9d". The memory could not be "read".

    Click on OK to terminate the program"

     

    Pls help me to solve this.......

     

    Regards,

    Bhuvana

    Monday, November 17, 2008 8:54 AM
  • Although that error can be caused by poor programming, that sounds like you have an intermittent memory hardware problem.


    Monday, November 17, 2008 1:58 PM
    Moderator
  • How can i solve this issue. How can i identify which process causes the memory leak?

     

    Regards,

    Bhuvana

    Wednesday, November 19, 2008 8:51 AM
  • Hi, i've the same problem.

    Did you solved this issue?

    Thanks

     

    Tuesday, September 27, 2011 12:38 PM
  • Hi All,


    DTS Job Runs but Does not Report Completion to Agent. Job Hangs in sql 2000

    I have the same problem. I have tried running the job using DTSRunui utility and also i have enable execute package on main thread option for the active x scripts. BUt this had not helped. Let me know how to solve it.

     

    Monday, October 10, 2011 12:28 PM