Post reply

SQL Alerts

  • January 4, 2012 at 7:04 am

    #249128

    Hi Guys

    I have an Agent job that executes a T-SQL Script

    I've configured the job to send an Email once the job completes.

    How can I get the job to Email me the results of said T-SQL Script?

    Thanks

  • January 4, 2012 at 7:52 am

    #1428322

    Thats not possible from the job itself. I would create a stored procedure to do what you are after and include the results in an attachment using database mail.

    You'll be able to call this proc from the SQL Agent or manually then.

  • January 4, 2012 at 7:57 am

    #1428328

    Depends on what you call complete.

    I have a few jobs that save data to a perm table, then the last step of the job (no error exit path), you can use sendmail there.

  • January 4, 2012 at 7:58 am

    #1428329

    MysteryJimbo (1/4/2012)

    Thats not possible from the job itself. I would create a stored procedure to do what you are after and include the results in an attachment using database mail.

    You'll be able to call this proc from the SQL Agent or manually then.

    Something like this?

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name =''

    ,@recipients=N''

    ,@body='Query'

    ,@subject ='Query'

    ,@query ='SELECT name from sys.databases'

  • January 4, 2012 at 8:00 am

    #1428332

    Ninja's_RGR'us (1/4/2012)

    Depends on what you call complete.

    I have a few jobs that save data to a perm table, then the last step of the job (no error exit path), you can use sendmail there.

    Complete - - - -whether Successfully or not

    I selected the "Email operator" when job completes in notification section of job

  • January 4, 2012 at 8:01 am

    #1428335

    derekr 43208 (1/4/2012)

    Ninja's_RGR'us (1/4/2012)

    Depends on what you call complete.

    I have a few jobs that save data to a perm table, then the last step of the job (no error exit path), you can use sendmail there.

    Complete - - - -whether Successfully or not

    I selected the "Email operator" when job completes in notification section of job

    I got that, I was saying use the last step as "completed point" to mail the report.

  • January 4, 2012 at 8:58 am

    #1428395

    I am working on same Scenario let me know if you find any good solution...

    I have T-sql script that i want to put in SQL Agent Job and i want the result of that script in e-mail.

  • January 4, 2012 at 9:05 am

    #1428401

    derekr 43208 (1/4/2012)

    MysteryJimbo (1/4/2012)

    Thats not possible from the job itself. I would create a stored procedure to do what you are after and include the results in an attachment using database mail.

    You'll be able to call this proc from the SQL Agent or manually then.

    Something like this?

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name =''

    ,@recipients=N''

    ,@body='Query'

    ,@subject ='Query'

    ,@query ='SELECT name from sys.databases'

    Yes.

    I'd include these as well but it depends on your requirements.

    @attach_query_result_as_file

    @query_attachment_filename

  • January 4, 2012 at 9:31 am

    #1428433

    Hi

    I personally put the code into job

    E.g.

    DECLARE @totalrecords int,

    @lcmessage nvarchar(max)

    select @totalrecords=count(*) from [tablename]

    set @lcmessage=

    'This is a Test

    Your query has completed and there are '+CAST(@totalemails AS NVARCHAR)+' new records'

    EXECUTE Msdb..sp_send_dbmail

    @recipients 'test@hotmail.com',

    @subject = 'Test Email',

    @body = @lcmessage,

    @profile_name = '[mail profile name]' ,

    @body_format = 'TEXT',

    @importance = 'HIGH'

  • January 5, 2012 at 4:35 am

    #1428895

    Ninja's_RGR'us (1/4/2012)

    derekr 43208 (1/4/2012)

    Ninja's_RGR'us (1/4/2012)

    Depends on what you call complete.

    I have a few jobs that save data to a perm table, then the last step of the job (no error exit path), you can use sendmail there.

    Complete - - - -whether Successfully or not

    I selected the "Email operator" when job completes in notification section of job

    I got that, I was saying use the last step as "completed point" to mail the report.

    Oh ok, so I will create another step in the job which will send an Email, I will however have to include the @Query parameter in the sp_send_dbmail code.

    ?

  • January 5, 2012 at 5:33 am

    #1428932

    derekr 43208 (1/5/2012)

    Ninja's_RGR'us (1/4/2012)

    derekr 43208 (1/4/2012)

    Ninja's_RGR'us (1/4/2012)

    Depends on what you call complete.

    I have a few jobs that save data to a perm table, then the last step of the job (no error exit path), you can use sendmail there.

    Complete - - - -whether Successfully or not

    I selected the "Email operator" when job completes in notification section of job

    I got that, I was saying use the last step as "completed point" to mail the report.

    Oh ok, so I will create another step in the job which will send an Email, I will however have to include the @Query parameter in the sp_send_dbmail code.

    ?

    Yes. Here's a sample code from Lowell. Other option is the create a ssrs report. Make a subscription that sends mail and then kick off that subscription's job on demand.

    http://www.sqlservercentral.com/Forums/FindPost1151877.aspx

  • January 5, 2012 at 6:08 am

    #1428949

    Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply