DBA Knowledge Base

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.

Next>

Next>

Click Add…

Click OK…

Next>

Next>

Next>

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.

Next>

Next>

Next>

5. Creating job…

Insert our code in the Command field:

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..

Sql Sever Backup Problem

If you have the following error during backup/restore of SQL Server database:

Just click OK, and let’s start its workaround…

In Select Backup Destination window do not browse anything, or it will arise the previous error, just type the path and name of the backup file, like that:

Click OK, there will appear the following window:

Click Yes… Your backup should work.

I have also checked my backup by restoring it and everything works fine…

Note: Support of the Microsoft recommends you the following workaround:

“To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.”

I did it without this security issue

P.S I found another solution of it… especially by granting db_backupoperator role to that user….

NOTE: If you do not grant this role, user will be able to make a backup as I did in the previous section, but will not be able to restore database…