I’m working with SQL Server Agent and I’ve got a job (let’s call it MainJob) that’s set up to run when one of two alerts goes off. The job has just one step that runs a stored procedure with a parameter.
Here’s what I’m trying to figure out: Is there a way to tell the stored procedure which alert caused the job to start? I’d like to pass some info about the alert as the parameter.
For example, if Alert1 sets off the job, I’d want something like:
exec MainProcedure 'Alert1 triggered this'
And if it’s Alert2:
exec MainProcedure 'Alert2 triggered this'
I know I could make separate jobs for each alert, but that’s not great if I end up with lots of alerts. Any ideas on how to do this within a single job?
hey there! maybe try using the system stored procedure sp_help_jobstep? it gives info about job steps, including the alert that triggered it. you could wrap this in your own procedure to extract the alert name, then pass it to MainProcedure. just an idea, hope it helps!
hmm, thats an interesting question! have you considered using tokens in your job step? you could set up something like $(ESCAPE_NONE(A-MSG)) as the parameter. then SQL Server should replace it with the alert message when the job runs. might be worth experimenting with. what do you think about that approach?
One approach you might consider is leveraging SQL Server’s msdb system tables. Specifically, you could query the sysjobhistory table immediately within your stored procedure to retrieve information about the most recent job execution.
Here’s a rough outline of how this might work:
- In your MainProcedure, first query sysjobhistory to get the most recent run details.
- Extract the alert information from the retrieved data.
- Use this information for your main logic.
This method allows you to keep a single job while still differentiating between alerts. It’s not perfect, as there’s a slight chance of race conditions, but it could be a viable solution for your scenario. Remember to test thoroughly in your environment.