Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MattHarding
Starting Member
2 Posts |
 Posted - 2002-05-28 : 09:53:51
|
| I have a few DTS packages that run once every 2 hours, updating a SQL2000 database (on a win2k server) with records from an Informix database. When I manually run these packages from within the DTS Local Packages directory, they always work without any problems. However, when run as a job, sometimes they fail or just "hang" (i.e., just say the job is still executing 60 hours after it first started). This happens a few times a day - sometimes they run, sometimes they don't. The only error message I get when viewing the job history is "The job failed", which doesn't help me very much. I've also looked in the windows event viewer log and SQL server log, but am not getting any more detail. I don't think it's due to server activity, as there is no pattern to when they fail.Anybody know either where I can get a more detailed error log, or know what is going wrong?Cheers,Matt |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-28 : 10:34:46
|
| The best thing you can do is for both the pkg and the job define a output file to write status too. (I don't have a 2k install infront of me) In the pkg is should be available in the pkg properties window and in the job, is should be on the advanced tab of the job step properties window. In addition to reviewing these files, you can use SQL Profiler to monitor activity . . .I would bet that the SQL Server Agent credentials differ from those used with running the dts package straight . . . permissions issue.<O> |
 |
|
|
MattHarding
Starting Member
2 Posts |
Posted - 2002-05-28 : 11:40:56
|
| Thanks for your advice. I'm running the jobs with the output files now (I didn't know about these before), so with any luck I'll be able to see what's going on... |
|
|
|
sakheb
Starting Member
4 Posts |
Posted - 2002-05-28 : 12:04:56
|
| I had the exact same problem...........turns out the jobs sometimes "hang" in my case when there is a prompt. What my DTS did was run a stored proc and writes the output to an excel file and it "hanged" when a file with the same name already existed and excel prompted to overwrite. When a package is run as a job, none of the prompts actually show up on the screen, they just stay in the background. |
|
|
|
shell_l_d
Starting Member
17 Posts |
Posted - 2009-12-05 : 21:26:25
|
| MattHarding... did you find the cause of your DTS hang issue please?I'm having a similar problem. :) |
|
|
|
|
|
|
|
|
DTS scheduled jobs failing