Emailing job output as a file attachment


One of our customer would like to email result of a SQL Agent job to email id. related thread. You can achieve this by enabling job step output to a file, add a new job step in current job that calls sp_send_dbmail stored procedure to emails with file attachments


USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name

               FROM   msdb.dbo.syscategories

               WHERE  name = N'[Uncategorized (Local)]’

                      AND category_class = 1)

  BEGIN

      EXEC @ReturnCode = msdb.dbo.sp_add_category

        @class=N’JOB’,

        @type=N’LOCAL’,

        @name=N'[Uncategorized (Local)]’

      IF ( @@ERROR <> 0

            OR @ReturnCode <> 0 )

        GOTO quitwithrollback

  END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job

  @job_name=N’DBCC IndexDEFRAG’,

  @enabled=1,

  @notify_level_eventlog=0,

  @notify_level_email=0,

  @notify_level_netsend=0,

  @notify_level_page=0,

  @delete_level=0,

  @job_id = @jobId OUTPUT

IF ( @@ERROR <> 0

      OR @ReturnCode <> 0 )

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

  @job_id=@jobId,

  @step_name=N’DBCC index defrag on sysjobhistory’,

  @step_id=1,

  @cmdexec_success_code=0,

  @on_success_action=3,

  @on_success_step_id=0,

  @on_fail_action=2,

  @on_fail_step_id=0,

  @retry_attempts=0,

  @retry_interval=0,

  @os_run_priority=0,

  @subsystem=N’TSQL’,

  @command=N’DBCC INDEXDEFRAG(msdb, ”sysjobhistory”)’,

  @database_name=N’msdb’,

  @output_file_name=N’c:\temp\dbccindexdefrag.txt’,

  @flags=0

IF ( @@ERROR <> 0

      OR @ReturnCode <> 0 )

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

  @job_id=@jobId,

  @step_name=N’send email’,

  @step_id=2,

  @cmdexec_success_code=0,

  @on_success_action=1,

  @on_success_step_id=0,

  @on_fail_action=2,

  @on_fail_step_id=0,

  @retry_attempts=0,

  @retry_interval=0,

  @os_run_priority=0,

  @subsystem=N’TSQL’,

  @command=N’EXEC sp_send_dbmail
 @profile_name=”msft”,
 @recipients=”seths@microsoft.com”,
 @subject=”Index Defrag job output”,
 @body=”Index Defrag report”,
 @file_attachments=”C:\temp\dbccindexdefrag.txt”
 ‘
,

  @database_name=N’msdb’,

  @flags=0

IF ( @@ERROR <> 0

      OR @ReturnCode <> 0 )

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.sp_update_job

  @job_id = @jobId,

  @start_step_id = 1

IF ( @@ERROR <> 0

      OR @ReturnCode <> 0 )

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver

  @job_id = @jobId,

  @server_name = N'(local)’

IF ( @@ERROR <> 0

      OR @ReturnCode <> 0 )

  GOTO quitwithrollback

COMMIT TRANSACTION

GOTO endsave

QUITWITHROLLBACK:

IF ( @@TRANCOUNT > 0 )

  ROLLBACK TRANSACTION

ENDSAVE:

GO 

T-SQL Script:
Emailing job output as a file attachment

Advertisements

Developer @ SQL Server Team, Microsoft

Tagged with:
Posted in SQL Agent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: