I have a job that has been running fine for several months but I disabled it for a few days and now when I run it, it hangs on a particular step. If I run it in test mode it runs fine and completes but in normal mode it hangs. Also, if I submit it remotely it runs fine. The job is as follows:

insert_status("08-Balance Sheet")
JobRun( "08-Balance Sheet" )
update_status("08-Balance Sheet")

It runs the first 2 steps fine but fails on the update_status step. The update_status is a script in my script library used to update an oracle job log table. Here is the code in the update_status script:

Dim rows, number
Dim update_sql, string
Dim status, number
DatabaseConnect "edwp"

//Get the status of the job
JobGetStatus( job_name, status )

//Update the record with end time and status.
ConcatEx "Update job_process_log Set end_time = sysdate, status = '",status,"' Where sequence_key = ",global.seq_key, update_sql
DatabaseExecute update_sql, rows

DatabaseDisconnect

Enable the tracing option, but don't enable the database tracing option for now, which is a separate option.

Let the job run and hang and then check [job id].log file in the Performance data directory.
This will provide you with an idea which particular operation (line of cade) is causing the job hanging. It will be then much easier to figure out the root cause of the problem (connect, update or something else).

: I have a job that has been running fine for several months but I disabled it
: for a few days and now when I run it, it hangs on a particular step. If I
: run it in test mode it runs fine and completes but in normal mode it
: hangs. Also, if I submit it remotely it runs fine. The job is as follows:
: insert_status("08-Balance Sheet")
: JobRun( "08-Balance Sheet" )
: update_status("08-Balance Sheet")

: It runs the first 2 steps fine but fails on the update_status step. The
: update_status is a script in my script library used to update an oracle
: job log table. Here is the code in the update_status script: Dim rows,
: number
: Dim update_sql, string
: Dim status, number
: DatabaseConnect "edwp"

: //Get the status of the job
: JobGetStatus( job_name, status )

: //Update the record with end time and status.
: ConcatEx "Update job_process_log Set end_time = sysdate, status =
: '",status,"' Where sequence_key = ",global.seq_key,
: update_sql
: DatabaseExecute update_sql, rows

: DatabaseDisconnect

I enabled the tracing option and it is hanging on the JobGetStatus step. If I comment this out it works. Any ideas what is happening? I've tested this same job on another server writing to the same database and it works fine.
Thanks,
Jeff

: Enable the tracing option, but don't enable the database tracing option for
: now, which is a separate option.

: Let the job run and hang and then check [job id].log file in the Performance
: data directory.
: This will provide you with an idea which particular operation (line of cade)
: is causing the job hanging. It will be then much easier to figure out the
: root cause of the problem (connect, update or something else).

Are you sure the job hangs forever? or it hangs and then failes after about 60 seconds waiting for JobGetStatus?
Does the user running this job have "PROCESS_QUERY_INFORMATION" privileges for any process?
Is the job "08-Balance Sheet" setup to run using different user account?

: I enabled the tracing option and it is hanging on the JobGetStatus step. If I
: comment this out it works. Any ideas what is happening? I've tested this
: same job on another server writing to the same database and it works fine.
: Thanks,
: Jeff

Yes it was in the queue for several hours last night, it has always ran fine in the past with the same user so I assume that is not the problem. All the jobs use the same user. Why would it run fine in test mode but not in normal mode? It almost sounds like something got corrupted the queue or the script?

: Are you sure the job hangs forever? or it hangs and then failes after about
: 60 seconds waiting for JobGetStatus?
: Does the user running this job have "PROCESS_QUERY_INFORMATION"
: privileges for any process?
: Is the job "08-Balance Sheet" setup to run using different user
: account?

My two cents, if you restart your computer everything will get back to normal. My guess is that either process querying or Windows DDE functions stopped working correctly. Don't know whether recent anti-virus updates on your computer contribute to that problem or not.

: Yes it was in the queue for several hours last night, it has always ran fine
: in the past with the same user so I assume that is not the problem. All
: the jobs use the same user. Why would it run fine in test mode but not in
: normal mode? It almost sounds like something got corrupted the queue or
: the script?

A re-boot of the server fixed it. Thanks!

: My two cents, if you restart your computer everything will get back to
: normal. My guess is that either process querying or Windows DDE functions
: stopped working correctly. Don't know whether recent anti-virus updates on
: your computer contribute to that problem or not.