June 9, 2003 at 7:41 am
I have a DTS package that I am trying to schedule to run hourly. Usually executing the DTS package takes approx 30mins to run, it reads from an AS400(urgh!), and performs lots of DB updates etc, and then emails operators to confirm its completion/failure.
The DTS owner, job owner, and SQL Agent security are all the same login.
My problem is that the job starts, but never seems to successfully complete. Does anyone have any ideas why this happens? Why does it not complete with an error/success message?
Thanks in advance.
Carl.
June 9, 2003 at 8:44 am
Be sure you don't have any interactive elements in the package. If so, it will stall waiting for input. Usually this is either a message box or an error with an "OK"
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 10, 2003 at 5:11 am
There are definately no interactive elements.
Any other ideas?
TIA
June 10, 2003 at 5:15 am
Hello Carl,
quote:
Any other ideas?
are you able to figure out where the package hangs (AS400, DB Updates, eMail)?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 10, 2003 at 7:07 am
We do this same thing with about 200 jobs ... on an AS400 9406-730 OS400 v5.1 .. Have verified the jobs are connecting to the AS/400?
June 10, 2003 at 7:30 am
Carl,
When you do a test run (non-scheduled) of the package, do you do so logged on to the SQL box itself, or using client utilities from your workstation?
Cheers,
- Mark
June 10, 2003 at 8:10 am
Can you run the job to completion in the dts designer?
If not, you can determine which step is causing the problem by running them individually using the Right Click menu - execute step.
June 10, 2003 at 9:08 am
You might try turning package logging on and then run the job. The log should tell you where the package is hanging up. If it doesn't give an error message, you can tell where it stopped logging and investigate that step.
Good Luck!
June 10, 2003 at 11:07 am
Carl, I had the same problem and I seemed to solve the issue by making sure that a user is logged into the SQL Server (ie. logged into Windows). In our case when the server did not have a user logged into it the jobs would hang the way you describe, but if I left a user logged into the server they would run fine. I'm guessing it's something to do with the security when trying to connect to AS400 although I haven't been able to find anything to document the issue.
Andrew
June 11, 2003 at 2:31 am
The machine that hosts the DTS is the machine where the job is scheduled. Everything is done from the same machine to avoid SQL security issues. Therefore executing the DTS manually should be the same as scheduling it? Correct me if I am wrong.
I shall try the logging, and see what happens: Next question. Can someone remind me how to turn on the logging?
TIA.
June 11, 2003 at 2:08 pm
Carl,
Are you executing the DTS as the same user that is used to automate the schedule?? If not the issue may be permissions.
Good Luck,
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 12, 2003 at 9:56 am
Yes, the same security is being used throughout.
June 12, 2003 at 11:02 am
Carl,
to turn logging on: Open DTS package, click on the package menu option then the logging tab.
Should be good to go from there
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 12, 2003 at 11:08 am
You can also try logging thru the job --> Properties.
Good Luck.
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply