query and send an email after a job finishes.

after a SQL job is complete, what step can be added to the job to query a table and send the result by email? or is it better to do outside the job?  (SQL 2012)
Who is Participating?
Mandeep SinghDatabase AdministratorCommented:

You can add steps to your job to do so.


Step1: do whatever your job want to do.
Step2: Now query the table you want and store the output and send it to our email.

-- Start T-SQL
    USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'MailProfile1',
      @recipients = 'someone@microsoft.com',
      @subject = 'T-SQL Query Result',
      @body = 'The result from SELECT is appended below.',
      @execute_query_database = 'msdb',
      @query = 'your select query goes here'
-- End T-SQL --

Open in new window

Reference: http://hightechnology.in/sql-query-result-set-html-table/
Haris DulicCommented:

if your job is calling some procedure, you can add your check (or call to another procedure which will check table and then send the results.

I always use another procedure for testing and mailing and then you can reuse it.

To send the results you can use sp_send_mail, on this link you can find more info on sp_send_mail procedure.
Vitor MontalvãoMSSQL Senior EngineerCommented:
25112, do you still need help with this question?
25112Author Commented:
figured out the error.. thanks all.

thanks to Vitor also!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.