Determining the End of the Job

[GShen]

Hi,
Have an application which kicks off a job performing a number of steps. The user clicks on a button on an ACCESS form. The problem I need to resolve is, how do I let the user know the job has completed. The job takes anywheres from 30 secs. to a minute. He cannot start his next process until this is complete because the data is getting loaded in this job. I realize I could take the other job and combine it, but it is cross servers, 2 different databases. The other database is actually an MDB. I could use this method in a lot of other areas as well. Telling the user to wait a minute does not cut it.
I was thinking of creating another table and setting a status field to RUNNING when I clicked on the button and then have the last step of the job update it to complete. I could then have the database (which started the job), keep checking every 5 seconds to see if the status has changed to complete using the timer interval. That seems to be a little crazy but it is better than the current method.

Anybody have any suggestions? I would appreciate it.

 

[TheBugSlayer]

Hmmm, maybe a combination of data from the job related tables from MSDB database can provide you an answer. Study the sysjobs* tables and see if you find a way around.

 

[GShen]

I will check it out. Not a great solution but maybe I could email the user that it is completed. It is better than creating all this code. I will read up more on it latter, time for lunch.
Thanks, I will keep you posted as to what I find.

 

[SQLBill]

If the job has several steps, why not add an additional step? Since a job won't go to the next step until the previous step is successful, make the last step one that will report back "Job Successful".

-SQLBill

 

[GShen]

SQLBill,
That's what I was going to do but how would I get that message back to the ACCESS database application performing the job?

 

[bertovich]

GShen,

The job status is actually kept in the msdb.dbo.sysjobhistory table. It's kind of squirrely but you should be able to come up with something. Look in BOL for the sysjobhistory table.

Bill

 

[GShen]

bertovich,
Hi. I found the table but haven't looked at the components yet. I am sure it is there, thanks. What do you think is the best method to check to see when it has completed? Loop in VBA until that status has shown it is completed or use the timer interval and loop? Or do you have a better method?
Thanks for your help.

 

[RiazKhanmohamed]

surely if you are running a procedure from an access form, you have an onclick function for that button? Simply add a message at the end of the call to the sql server process to show a message? ie msgbox "Finished".

You can also convert the mouse cursor to a timer while it's working in the access database, so the user thinks it is running and doesn't touch anything.

 

[GShen]

FredPerry ,
You have me a little confused.
Yes, the form has an ONCLICK button which kicks off the job. I just can't add a msgbox right after it because the message would appear immediately. I need to somehow acertain when the job is finished and then display the message "Finished". From the above posts, I was going to loop every 5 seconds in Vb and check the status from the system table till it was completed.
Not sure about what you mean about converting the mouse cursor to a timer.
If I am missing something, please fill me in.
Thanks!

 

[RiazKhanmohamed]

what i mean is, surely, within the onclick function, there is something to run the job you want run...

sorry to cause confusion. probably looking at it from a different angle. just wondering how you get the job to kick off - ie by code in vba or some other method.

 

[JamesLean]

FredPerry,
When you execute the statement that starts the job, control is immediately passed back to the app - it doesn't wait for the job to finish, like it would if you were actually executing a stored proc, say. That is what is causing the problem in the first place!

GShen,
Have you thought about converting the job into a stored proc and just executing that from the app? Then you could do as FredPerry says.

--James

 

[GShen]

FredPerry,
Thanks for the reply.
Here is how it works. Yes, the job gets kicked off by clicking on a button in VBA. It is not done directly because you cannot fire of a job from VBA. It actually executes a stored procedure which inturn fires off the job.
Now the problem is the job is running for a time which is unknown. Using an old CICS term (as you can see I have been around a while), the job is actually running in backround.
Hopefully that clears up what I am trying to do.

 

[GShen]

JamesLean,
You hit the nail right on the head.
The jobs first step is to fire off a DTS to import data into a table which is why I created the job.

 

[JamesLean]

You can execute a DTS package quite easily from a stored proc:

EXEC master..xp_cmdshell 'dtsrun /Sserver /E /Npackage'

If that's the only reason it's a job then maybe you could rethink?

--James

 

[RiazKhanmohamed]

i see what you mean, you want a notification mid job. Sorry, didn't fully understand you.

I thought you'd have to write code to run the individual jobs (ie 2 lines, one to run kick off each job within the coding of the onclick button), because then you could use messages in the middle.

the loop thing suggested would work if you need it mid job. makes sense. sorry for the confusion.

 

[RiazKhanmohamed]

if you use jameslean's suggestion, you can run the dtsrun command from a dos prompt, thus running it from within access under an onclick command - then you could code around it. except mid job, which clearly is still the problem.

 

[GShen]

JamesLean,
I just checked out the job. It has 4 steps.
1) DTS
2) run a stored procedure
3) run another stored procedure
4) rename the input to the DTS on a network drive to a backup file name.

So obviously, steps 1 thru 3 would not be a problem. Not sure about how I would do step 4.

The only other problem is that this job has email notifications to a number of people if it does not work. Now we start getting into another area of problems.
I didn't realize, however, you could kick off a DTS using a cmdshell.

I will tinker with that sysjob and see if I can get it to work. It seems there are a number of other users who do this waiting thing and I would like to make their life easier. Some of their jobs run longer than the timeout which causes the DB to close which is why they get fired off in a job (it will run for as long as it takes.).



FredPerry,
No problem, it is always a lot easier writing than to decifer. Thanks.

 

[JamesLean]

I did think about the process taking longer than the timeout - it does sound like you need to keep the job like you have it.

If this were my system, I would probably get the job to email the user once it had finished, letting them know that they could carry on with what they had to do. You could pop up a messagebox once they had clicked the button in the app to say the job had started and to wait for the confirmation email before continuing.

Don't know if that's acceptable for you? Doesn't seem too bad to me...

--James

 

[GShen]

JamesLean,
We think alike, look back my 2nd post. Not everyone has email at our company but the person I am currently do this for does have it. With all the stuff going on right now, I think I am going to do it this way. The message box before I kick it off telling them an email is coming for a confirmation is a great idea.
When I have time to play, I will look into the sysjob table.

Thanks!

 

[JamesLean]

Sorry - it was such a long time ago I forgot everything you've said! ;-)

--James