Reset and run a job

kduke · Post by **kduke** » Tue Nov 02, 2004 2:17 pm

Ray or Hester or someone:

There is a way to reset and job and remained attached to it but I am not sure of the syntax. This is a multiple instance job that I want to reset and run. I was just deleting the RT_STATUS records and noticed this did not always work. I changed it to this code below. If you download EtlStats then this change needs to be added to it. Post new code soon. Sorry.

Code: Select all

* Setup GetEtlQaSqlStat.RunId, run it, wait for it to finish, and test for success
         hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL)
         If NOT(hJob1) Then
            Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl")
            Abort
         End
         LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) 
         If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then 
            Call DSLogInfo(JobReportName, "Reseting job and last run status") 
            ErrCode = DSRunJob(hJob1, DSJ.RUNRESET) 
            ErrCode = DSWaitForJob(hJob1)
            ErrCode = DSDetachJob(hJob1)
            hJob1 = DSAttachJob(JobReportName, DSJ.ERRFATAL)
            If NOT(hJob1) Then
               Call DSLogFatal("Job Attach Failed: ":JobReportName, "JobControl")
               Abort
            End
         end
         ErrCode = DSSetParam(hJob1, "StatDSN", DsnId)
         ErrCode = DSSetParam(hJob1, "StatUser", DsnUserId)
         ErrCode = DSSetParam(hJob1, "StatPwd", DsnPwd)
         ErrCode = DSSetParam(hJob1, "TableName", TableName)
         ErrCode = DSSetParam(hJob1, "SqlToRun", SqlToRun)
         ErrCode = DSSetParam(hJob1, "StatId", StatId)
         ErrCode = DSSetParam(hJob1, "TargetDSN", TargetDSN)
         ErrCode = DSSetParam(hJob1, "TargetUser", TargetUser)
         ErrCode = DSSetParam(hJob1, "TargetPwd", TargetPwd)
         ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL)
         ErrCode = DSWaitForJob(hJob1)
         Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
         If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then
            * Fatal Error - No Return
            Call DSLogFatal("Job Failed: ":JobReportName, "JobControl")
         End

Notice I had to DSDetachJob(hJob1) and then attach to it again to run it after the reset. Is there a shortcut?

chulett · Post by **chulett** » Tue Nov 02, 2004 2:58 pm

Kim - I use DSPrepareJob which doesn't require me to detach and reattach before I can run it.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 02, 2004 3:43 pm

Having been burned with "attach lock" errors I always detach and attach, as your code does. Because they're effectively in adjacent statements in the one routine, I don't really lose control, though I do have to set parameters afresh. So far I've avoided using DSPrepareJob.

And I never use DSLogFatal. Ever.

chulett · Post by **chulett** » Tue Nov 02, 2004 3:52 pm

ray.wurlod wrote:So far I've avoided using DSPrepareJob.

Just... because? Seems to be working just peachy for me.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 02, 2004 7:01 pm

chulett wrote:
ray.wurlod wrote:So far I've avoided using DSPrepareJob.

Just... because? Seems to be working just peachy for me.

Just because. Always done it that way.

kduke · Post by **kduke** » Tue Nov 02, 2004 8:36 pm

Thanks guys. Ray, Will Rogers said "I never met a man I did not like until I talked to him". They usually just quote the first part. Makes a difference when you add "until I talked to him". I like your quote.