March 6, 2007 at 9:58 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 6, 2007 at 12:08 pm
The application doesn't need to get input from the user does it? Message box, console application waiting for hit return.
Russel Loski, MCSE Business Intelligence, Data Platform
March 6, 2007 at 12:17 pm
Try changing the time the job runs and see if it helps. one of my jobs that is scheduled used to run for hours (and it wouldnt stop) and i have changed the time and its working fine.
March 6, 2007 at 1:22 pm
This job runs every 15 minutes and will be polling a particular table. If any records are found, it will process them.
The package when executed manually works fine [The executable also runs fine if it is executed manually] but when it is scheduled in a SQL Agent Job, it just hangs.
I am still trying to find a way out. Any leads would be of much help.
March 6, 2007 at 2:34 pm
If you have Internet Explorer Enhanced Security installed on the server, it will cause a message box to pop-up when you attempt to run an executable. Since there is no way for the DTS package to respond to the message box, the job hangs.
March 7, 2007 at 2:46 am
The Executable does not popup any dialogue boxes for the user to respond. The DTS Package executes fine without any error / problems when executed manually [Right click package and click execute package].
But the same DTS package, when scheduled on a SQL Server Agent Job, hangs midway through. Since the job has to be cancelled manually, no errors are recored in sysjobhistory or sysdtspackagelog.
This is getting wierder by the day.
March 7, 2007 at 3:59 pm
When you say it does not popup any dialog box, does that mean it dosn't do that when you log onto the server with the SQL Server Agent service account and run the package, or only that it doesn't do it when you run it from your desktop computer? That is two completely different things, and only doing the first tells the true situation.
March 8, 2007 at 4:07 am
I recon a bit more details will help understanding this problem.
I have an executable [say report.exe] which generates a report and emails them to the person who requested the reports. This executable uses CDOSYS objects to send email. This executable when executed from the command prompt works fine.
This executable is called from a DTS Package. The DTS package when executed manually [Right click package > Execute Package] executes fine.
As per our business need, this executable has to be executed every hour and hence, we scheduled this package as a SQL Server Agent Job. The agent job stalls midway through the execution.
The DTS package does not wait for any user intervention as the executable executes fine without any intervention. I am a bit puzzled as to why the same executable stalls when executed under SQL Server Agent Job.
Hope this gives more information. Your comments / suggestions to get this going would be of much help.
March 8, 2007 at 7:07 am
From your answer, I guess that means that didn't try logging onto the server with the SQL Server Agent service account and running the package.
There are many thing that could be going wrong, and you will not find it without trying it on the server running under the SQL Server Agent service account.
The Internet Explorer Enhanced Security that I mentioned in a previous post is one thing that could be causeing the problem. It detects the attempt to run an executable in VB script and pops up a dialog box.
Just because your application does not have a GUI component doesn't mean you are not getting a message box.
March 10, 2007 at 3:58 pm
Thank you for your reply.
I checked Internet Explorer Enhanced Security and this component is not installed on the Server.
This package used to work fine and it was giving this problem for the past 3 or 4 weeks. This Executable uses CDOSYS.dll to send mails. When the job is executed, the procedure stalls while sending emails. I tried executing the customized procedure sp_send_cdosysmail and it gave an error 'the transport could not connect to the server'. After checking with the DNS entries, the procedure now gives an error as "Exception Ox0000005" which says that it could be due to permissions. I tried giving sys admin privileges to the domain user account but still this is giving the same error message.
And an additional information. The executable is able to send emails when executed from the command prompt / windows explorer or from DTS Package. On all occations, it still uses the CDOSYS.dll to send emails. But when the same executable is scheduled as a SQL Server Agent job, the job stalls at where the emails are sent.
Does that ring any bells. I am still puzzled and looking around for a fix.
March 10, 2007 at 4:38 pm
So what happened when you logged onto the server with the SQL Server Agent service account and ran the package?
March 10, 2007 at 4:45 pm
The package executes without any errors if I execute the package from the command prompt by logging on to the SQL Server with the SQL Server Agent Service account.
March 12, 2007 at 10:53 am
Also, in the SQL Server Agent Properties, Under the General tab, the section "Mail Session" is disabled on the server which has this problem. On all other installations, this section is enabled.
I am not sure if this could be the problem. Can any one tell me how to get this "Mail Session" enabled?
March 29, 2007 at 10:47 am
Has any one encountered "Exception Ox0000005" while sending emails using CDO objects?
Kindly let me know incase you have any workarounds for this exception.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply