CODEWITHSUNDEEP
sql-serversmosql-agentsql-agent-job
JSacksteder
JSacksteder

Reputation: 790

Get SQL Agent job status without polling?

I'm trying to find a way to have the SQL Server 'SQL Agent' run a particular piece of code on job step events. I had hoped that there was a way using SMO to register a callback method so that when job steps begin or change status, my code is called. I'm not having any success. Is there any way to have these events pushed to me, rather than polling?

Upvotes: 2

Views: 954

Answers (1)

Pondlife
Pondlife

Reputation: 16240

There is no Smo, DDL or trace event exposed for job execution (as far as I can see from Books Online), so I don't think you can do what you want directly. It would be helpful if you could explain exactly what your goal is (and your MSSQL version) and someone may have a useful suggestion. For example, a trace may be a better idea if you want to gather audit or performance data.

In the meantime, here are some ideas (mostly not very 'nice' ones):

  1. Convert your jobs into SSIS packages (they have a full event model)
  2. Build something into the job steps themselves
  3. Log job step completion to a table, and use a trigger on the table to run your code
  4. Run a trace with logging to a table and use a trigger on the table to run your code

Upvotes: 3

Related Questions