This article explains how to email the output of a scheduled CLI job – SQL file, report, etc using SQL Server job
To email created files, do the following:
- Configure mail profile and account on the database server in SQL Server Management Studio. See MSDN Database Mail Configuration Wizard article for more information
- 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
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'
@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
- Schedule the job
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.
November 30, 2011