March 6, 2007 at 9:10 am
Hi,
I am encountering a strange problem while scheduling a DTS Package.
The DTS Package calls/executes an executable which generates reports and sends it to the user who requested them. The executable when executed manually runs fine and the same happens when the DTS Package is executed.
The execution hangs midway when the DTS Package is scheduled. The job executes for a while and just stays there for hours together. If i cancell the job and execute the package manually, the process completes just fine.
I have tried changing the Owner of the job, giving permissions to the users to send emails, run the SQL Agent using a domain user account who has admin privileges on the server, execute the package under the user account that is able to execute the package/executable manually and etc.....but without any success.
Any lead to proceed with the troubleshooting would be of much help.
Cheers.
March 7, 2007 at 8:53 am
Does the job use Excel? We've run into this problem - especially with Excel. The problem is caused by the job trying to run something (Excel, vb, etc...) without the dll/exe being installed on the server. When the job runs manually, it has access to the software installed on the user's desktop. Running as a scheduled job, it only has access to what is installed on the server. We installed Excel on the server and that particular problem went away. If you're using any 3rd part software to generate the report, make sure it's available on the server.
March 12, 2007 at 10:35 am
Thank you for the reply.
Yes the job uses excel to generate reports and send them to the requested users. If the executable is run manually, the reports are sent to the requestors. But the problem happens when the same executable is scheduled to run as an Agent Job.
Even while executing this as an Agent Job, the files are generated and the job stalls while sending emails. Emails are sent using CDOSYS.dll.
I tried to execute a custom stored procedure that uses CDO objects to send email and the stored procedure fails with the following error message.
Exception 0xc0000005 was generated at address 0x0cbbf390
This seems to be an access violation error but the SQL Agent Account is part of the Administrators group which adds up to the confusion.
This procedure used to work fine and it suddenly stalled for no reason. Also, there are no changes made to the server.
This is my findings. Any leads towards fixing this issue would be of great help.
Cheers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply