LinkedIn

Tuesday, 18 June 2013

Setting up Sql Database mail for a client

I had a requirement to periodically send a data extract from an SQL database. I had a few ideas about how I could accomplish this but decided to take a look at SQL server to see what it had to offer as I hadn't used it to send anything for the last few years.

Anyway, I came across SQL database mail:

Here is what Microsoft says about it:
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

So back to the task I needed to complete. I needed to periodically schedule a SQL Server Agent job to query my database and send the results to an email recipient.

Here's what I did.

I needed to send the following data:
SELECT P.Id, FirstName, LastName, 
        Email, AddressLine1, Suburb, PostCode, ST.Name, FutureCommunication, [Uid] 
        FROM PARENT P
        INNER JOIN 
        STATES ST
        ON P.State = ST.Id;

Using:
EXEC msdb.dbo.sp_send_dbmail

You can find the documentation on this here:
http://msdn.microsoft.com/en-us/library/ms190307(v=sql.105).aspx

I came up with the follow script:
EXEC msdb.dbo.sp_send_dbmail
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DatabaseMailProfile',
        @recipients = 'recipient@destination.com.au;',
        @query = @parentEmailQuery,
            
        @subject = @parentEmailSubject,
        @execute_query_database = 'DATABASE_NAME',
        @query_result_header = 1,
        @query_attachment_filename = @parentCsvFileName,
        @query_result_separator = '|',
        @query_result_no_padding = 1,
        @attach_query_result_as_file = 1 ;

This is basically a list of options that database mail needs.
Some useful ones I will point out are:

  • @profile_name = 'DatabaseMailProfile' - I will show you what this relates to shortly.
  • @query = @parentEmailQuery - This the query I showed you above but I included: SET NOCOUNT ON; before my query and SET NOCOUNT OFF; after my query. This was because the following: "n rows were returned" kept on appearing in my final out put and I did not want this.
  • @query_result_header = 1 - this shows or hides the column names. When it is set to 1 it also adds in an ugly row of dashes after column headings. This was ok for me but you may want to investigate writing a custom query to write out the header instead.
For this script to work we now need to talk about setting up SQL database mail.

Firstly we need to set up an account to use to send emails:

 the database mail node can be found in the SQL management studio tree:

 Right click on that and choose:

 You need to first set up an account so choose from the following screen:

and then this screen:































and enter the details of for your account:



After you have created an account you need setup a profile. As you will notice in the above mentioned query, there is a reference to the profile name:
EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'DatabaseMailProfile',

So here we go. Choose the option to add a profile and then add the account you created to it:






























Choose what security options you want:





























In the following screen, leave as is or change if you need to:
































You will also need to run the following:


















After that you can run a test if you like:


















now you can run the script above:
EXEC msdb.dbo.sp_send_dbmail
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DatabaseMailProfile',
        @recipients = 'recipient@destination.com.au;',
        @query = @parentEmailQuery,
            
        @subject = @parentEmailSubject,
        @execute_query_database = 'DATABASE_NAME',
        @query_result_header = 1,
        @query_attachment_filename = @parentCsvFileName,
        @query_result_separator = '|',
        @query_result_no_padding = 1,
        @attach_query_result_as_file = 1 ;

Obviously I have removed my real world values.

This will do the following:
  • Look for the profile we created
  • Run the query to get the data we want to send
  • Set up some email params
  • Send our data as a csv file attachment with headers. The data will be separated with a pipe (|). You could use comma separation if you like.
Here is the email that was sent:






















Following on from this you could set up a SQL Server Agent scheduled job to run periodically to
send your data.

thanks
RuSs