ApexSQL Knowledgebase: How can I email the output of a scheduled CLI job (e.g. reports, log files)?

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

How can I email the output of a scheduled CLI job (e.g. reports, log files)?

DESCRIPTION
To do this, we will use a SQL Server job. Below steps discuss how to email an audit report generated by ApexSQL Log (through the command line interface)

SOLUTION

I. In SQL Server 2005, execute the following steps:
1. Configure mail profile and account on your database server:

CODE:
EXEC SP_CONFIGURE 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC SP_CONFIGURE 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXEC MSDB.DBO.SYSMAIL_ADD_PROFILE_SP
       @profile_name = 'DBProfile',
       @description = 'Profile used to send mails.' ;
GO

EXEC MSDB.DBO.SYSMAIL_ADD_ACCOUNT_SP
      @account_name = 'DBAccount',
      @email_address = 'DBAccount@localhost',
      @display_name = 'Mail from Server',
      @mailserver_name = '',
      @username = '',
      @password = ''
GO

EXEC MSDB.DBO.SYSMAIL_ADD_PROFILEACCOUNT_SP
      @profile_name = 'DBProfile',
      @account_name = 'DBAccount',
      @sequence_number = 1
GO

Where:
- SMTP server from which e-mail will be sent
- e-mail account on SMTP server
- password for e-mail account

Database Mail may be configured manually from Microsoft SQL Server Management Studio:

More detailed information can be found: here

2. Create a Job for SQL server and schedule it to run every Monday, 11PM.

a. Open SQL Server Studio and create a new job:

b. Add new step to the job and specify ‘Operating System (CmdExec)’ as the command type:

c. Paste your CLI command into the ‘Command’ edit box:

CODE:
APEXSQLLOG.COM /S:TESTSERVER /D:TESTDB /SQL:<FULL PATH>\RESULT.SQL /VERBOSE

The results of execution will be outputted to \result.sql. If the result file already exists, it would be overwritten.

d. Create another step with 'Transact-SQL (T-SQL)' type and paste the following to 'Command' edit field:

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

Where:
@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 is 'SQL Server Message'.
- is the e-mail to which results of audit will be sent
\result.sql' - is the full path to audit's result

e. To run the job every Monday, 11PM, create a new schedule for the job. On
Schedules, click "New":

Next, setup the schedule:

II. Click here to get more information on how to do this in SQL Server 2000.

AUTHOR
David Dawson

LAST REVIEW DATE
06 November 2007

Labels: ApexSQL Log, General

posted by Janice Lee on Wednesday, November 07, 2007

0 Comments: