How to email the output of a scheduled CLI job?

Summary
This article explains how to email the output of a scheduled CLI job – SQL file, report, etc using SQL Server job

Solution
To email created files, do the following:

1. Configure mail profile and account on the database server in SQL Server Management Studio. See MSDN Database Mail Configuration Wizard article for more information
2. Create an ApexSQL CLI statement that outputs the results to a SQL file. Create a job based on this statement. See How to Schedule a CLI job using SQL Server for more information

3. In the same job, create another step with ‘Transact-SQL (T-SQL)’ type and paste the following to ‘Command’ field:

   CODE:
   EXEC MSDB.DBO.SP_SEND_DBMAIL
   @profile_name = 'DBProfile',
   @recipients = '',
   @subject = 'Mail from DB',
   @file_attachments = '\result.sql'

   Where:
   @profile_name – is the database e-mail profile that will be used
   @recipients – is a semicolon-delimited list of e-mail addresses to send the message to
   @subject – is the subject of the e-mail message. If no subject is specified, the default subject ‘SQL Server Message’ is used
   @file_attachments – is the full path and name to the files to be sent

4. Schedule the job

About ApexSQL
ApexSQL is a leading provider of SQL Server tools for database recovery, database auditing, database comparison, documentation as well as SQL Server Management Studio and Visual Studio add-ins for SQL code refactoring, code completion and database source control. We strive to ensure that every one of our tools is standard-defining product in its class, from feature set, to performance, interface and quality.

Last updated
November 30, 2011