January 24, 2007 at 10:04 am
Hello everyone! I have a question on SQL Jobs. I have a few manual processes that an operator manually runs a job.
The Problem:
In SQL 2005, when an operator in the SQLAgentOperator Role runs a job they are immediately returned the output code of success or failure even though the package is still running.
Our analysis insists that they need to know after the package completes not before because they might need to run a job based upon output of the other. (I've already preached about if a package depends on another, combining the package or mother child packages, so let’s not go there on this issue.)
For now, I output a log file and they check it if they need to know when the package completes.
Does anyone else have a solution to recommend?
Thanks in advance
January 24, 2007 at 12:06 pm
Guess my first inclination would be to have the job send an email to whoever needs it when it finishes. Just set the job to send an email whenever it completes. Or if that is used to alert someone else when the job fails, add a step to the job that uses sp_send_dbmail.
January 25, 2007 at 10:10 am
You can check sybjobshistory. You might as well launch the job in a query window, then in another window you can keep checking sysjobshistory. As soon as the job is completed it adds a row for each step of the job. Be careful on how you order your output - you can't rely on run_time because it is not formatted properly. So my suggestion would be to order by the Instance_ID.
Exec dbo.sp_start_job 'RefreshServerMetaData'
SELECT j.Name, jh.*
FROM dbo.sysjobhistory jh
JOIN sysjobs j
ON jh.Job_ID = j.Job_ID
ORDER BY Run_Date desc, run_time
- Paul
- Paul
http://paulpaivasql.blogspot.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply