22. dubna 2010 8:17
Re: SQL Server Agent
I'd like to write a script that sends specific information on job steps.
I know how to query from system tables (master.sys.sysprocesses) to find out what the job is that's currently running. The script executes in a job step and sends the name of the job in a notification to my inbox when a certain event occurs. But how do I know what step is running? Would job steps also register in the master.sys.sysprocesses table during execution?
I'd like to have the script execute during the step and send information via sp_send_dbmail to my inbox if certain errors or warnings occur - as I find the error notification service to be a little shortcoming.
PS: this is running on a SQL 2008 server instance
19. ledna 2012 1:21
please share the script for which step is currently running in SQL Job.
Thanks in advance.
9. února 2012 0:13
If you have are looking at sysprocesses then the job step is at the end of the program_name string:
SQLAgent - TSQL JobStep (Job 0x71BF1A155AABC04AA06CFBE4BC4F2C1B : Step 1)
Regards, Matt Bowler MCITP, http://mattsql.wordpress.com/
29. března 2012 10:16
Its an old post but found it randomly while searching for one of my issue.
You use below command to get details like which step is currently running and wats last execution time and many other things.
@execution_status = 1
8. května 2012 4:05
The following link may be useful.
If you want to send mail when a job fails then follow the below steps.
1. In job step set advanced properties OnFailure 'Go to the next step'
2. Create a new step
name ->'send mail on error'
Type ->Transact SQL script(T-SQL)
Command -> msdb..sp_notify_operator @name='emailname',@profile_name='profile name',@subject='the job failed',@body='error message'
ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
- Upravený Eshani Rao 8. května 2012 4:08