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 |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-10-24 : 07:32:04
|
I have a very complicated multi-step, multi-dependancy set of jobs that run over night.One particular job has failed for the last two night reporting this error quote:
Unknown token received from SQL Server [SQLState HY000](Error 0). The step failed.
When I arrive at 6:30am and restart the job at the step that failed, it runs through fine (although serveral hours late, thus holding up other jobs critical to the business).The failing step is a stored proc that that uses a cursor to build a flattened adjacency model hierarchy from a regular hierarchy. By 'flattened' I mean there is only 1 level of depth such that there is row for every entity and every other entity in his downline with a column for level. I think this is inconsequential.This step has been running for weeks, without incident and I am in the process of looking for something that has changed.I searched the Microsoft site a bit, but didn't come up with anything that seems to fit.There are no errors reported to the SQL Server log, the Agent log and there are no OS events written.Any ideas?Jay White{0} |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-24 : 08:00:23
|
| You could just include logic to retry until it succeeds then at least the job would recover from any transient errors.Are you sure no backups or anything are going on at this time.Could put a delay at the begining of the step (or as an extra step). Maybe the previous step needs a bit of time to clear up.If this is critical of complicated I wouldn't run it as a single job.Have a job that runs every minute to call a scheduler SP. This SP gets the next SP to run from a table and gets the result before going on to the next one.In this way it is easy to keep retrying processes.You could also call a VB app rather than an SP to getthe next one in the schedule to get better error reporting.I am due to send in an article on this but haven't got round to it yet.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 10/24/2002 08:04:56 |
 |
|
|
|
|
|
Help me debug an error.