Send Mail from SQL Server 2005

Hello Geeks,

I am planning to post very very useful thing.

Once at my work, one co-worker asked me to write such thing:

Retrieve query result from SQL Server 2005, save it as a .csv file and sent it to some recipients everyday at some time.

So let’s start it…

1. First of all you should create Profile and Account.

 

SQL Server Configure Database Mail

SQL Server  Database Mail Configuration Wizard

Next>

SQL Server  Database Mail Configuration Wizard_Select Configuration_Task

Next>

SQL Server  Database Mail Configuration Wizard_New_profile

Click Add…

SQL Server Database mail Account

Click OK…

SQL Server Database Mail Configuration Wizard New Profile

Next>

SQL Server Database Mail Configuration Wizard Manage Profile Security

Next>

SQL Server Database Mail Configuration Wizard Configure System Parameters

Next>

SQL Server Database Mail Configuration Wizard Complete the Wizard

Click Finish…

Everything should be successful.

2. We should enable Database Mail on the server. Using Database Mail XPs.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

3. Let’s send mail… Using msdb.dbo.sp_send_dbmail procedure… Indicating the following parameters.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlReport', @recipients = 'mariam.kupa@gmail.com', @subject = 'SQL Report', @body_format = 'HTML',
@body = '.............', @query = 'SELECT * from [testDB].[dbo].[v_Report_Mari] order by [Computer Name]', @attach_query_result_as_file = 1, @query_attachment_filename = 'qry.csv', @query_result_no_padding = 1, @append_query_error = 1, @exclude_query_output = 1, @query_result_header = 1, @query_result_separator=' '

Let’s discuss each parameter:

[ @profile_name = ]  This is the name of the profile from where message will be sent(We set up it before, 1st step)

[ @recipients = ]       This is a semicolon-delimited list of e-mail addresses to send the message to.

[ @subject = ]           This is the subject of the e-mail message.

[ @body_format = ]   This is the format of the message body. Available values are: TEXT (default) and HTML.

[ @body = ]               This is the body of the e-mail message.

[ @query = ]              This is a query to execute. It’s result can be attached as a file, or included as a text message included in the body . Consider that the query is executed in a separate session and local  variables in the script using sp_send_dbmail can not be used.

[ @attach_query_result_as_file = ] Available values are 1 (YES) and 0 (NO).

[ @query_attachment_filename = ] Name of the attached file.

[ @query_result_no_padding = ]     Available values are 0 (padding) and 1 (no padding). If you indicate 1 it possibly reduce the file size,because there will not be any padding. Default value is 0.

[ @append_query_error = ]             Available values are 0 and 1. If any error occurs in the query indicated in [ @query = ] and that parameter value is 1, then mail will be sent appended with the error message. If the value is 0 then mail will not be sent. Default value is 0.

[ @exclude_query_output = ]        When the value is 1, the execution of the sp_send_dbmail does not print any of the query execution messages on the console. For 0 it is vice versa. The default value is 0.

[ @query_result_header = ]           Specifies whether the query results include column headers. Available values are 1 and 0.

[ @query_result_separator = ]       Indicates by which columns should be separated. Default value is ‘ ’(space). In our example, i ‘ve indicated TAB, just type 2 quotation marks place cursor between of them and press desired key, in our case TAB.

4. Increase the mail size limit from Database Mail. You can do it during setting up the account but I prefer to discuss it separately.

SQL Server Configure Database Mail

SQL Server  Database Mail Configuration Wizard

Next>

SQL Server  Database Mail Configuration Wizard View Change system parameters

Next>

SQL Server  Database Mail Configuration Wizard View Change system parameters

Next>

5. Creating job…

SQL Server Create New Job

SQL Server New Job General Tab

SQL Server New Job Steps Tab

Insert our code in the Command field:

SQL Server New Job Steps General Tab

SQL Server New Job Schedules

SQL Server New Job Schedules Job Schedule

It is easy to understand, I will not explain… This window is for to schedule job for the specified time. Click OK..OK…

Usefull view for checking email status:

SELECT *
FROM msdb.dbo.sysmail_event_log

 

That is all..

Advertisements

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: