2

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?

JSacksteder
  • 780
  • 2
  • 7
  • 21

1 Answers1

3

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
Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • I have been away - late response. Anyway, I want to have a locally deployed app by which an end-user can start and monitor the progress of a remote sql agent job that runs a series of interdependent ssis steps. It's a ugly problem and we have a lame solution in place. Your ideas are good and might be helpful to someone else in this situation. – JSacksteder Apr 04 '11 at 14:47