Archive for the ‘Database Mail’ Category

How to Send the Output of a Stored Proc in CSV Format Using Database Mail?

You have been presented with a unique and interesting challenge. The company you work for wishes to see an automated report every Monday morning at 9am based on a query result in CSV format. It does not have Reporting Services installed. We all know that we can use Database Mail to send out html reports or send out alerts. Can we send out report in CSV format? Yes we can! Here’s what you need and how.

While there are no extra software programs or components required to accomplish this task, you do need to have Database Mail and xp_cmdshell enable. In addition, you need to create a folder as the repository for the CSV files to be generated. If you have not enabled Database Mail or do not have an email account handy, see my blog on “How to setup database mail using a gmail account?

Here’s an overview of the steps required:
1. Enable Database Mail if not already done.
2. Enable xp_cmdshell if not already enabled.
3. Create the stored proc for the report you wish to get automated and scheduled to run.
4. Create the folder as the repository for the CSV files to be generated.
5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.
6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.
7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

Implementation steps:
1. Enable Database Mail if not already done.
If not already enabled, then see my blog on “How to setup database mail using a gmail account?

2. Enable xp_cmdshell if not already enabled.

-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

3. Create the stored proc for the report you wish to get automated and scheduled to run. In this example, we will use the stored proc, [dbo].[uspGetBillOfMaterials] in AdventureWorks2012 as an example.
uspGetBillOfMaterials

4. Create the folder as the repository for the CSV files to be generated.
For this example, I have created the C:\BillOfMaterials on my laptop.

5. If for whatever reason you are not able to use domain login to connect to the SQL Server instance, create the required login to use SQLCMD to connect to the corresponding instance with sufficient privileges to run the stored proc.

USE [master]
GO

CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Billy] -- this is for testing purposes only
GO

6. Create the TSQL script to run the steps to generate the CSV file and then send it out as an attachment.

DECLARE @sqlCommand VARCHAR(999)
DECLARE @filePath VARCHAR(125)
DECLARE @fileName VARCHAR(125)
DECLARE	@fileAttachment VARCHAR(225)		
DECLARE @todaysDateFormatted VARCHAR(30)
DECLARE @emailSubject VARCHAR(50)
 
SET @filePath = 'C:\BillOfMaterials\' -- folder to store the csv files to be generated

-- generate the dynamic file name based on the date and time 
SELECT @todaysDateFormatted = CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' +
		 CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR)

-- create the file name
SET    @fileName = 'BillOfMaterials_' + @todaysDateFormatted + '.csv'

-- create the report 
SET    @sqlCommand =
       'SQLCMD -S (local) -UBilly -PBob -d AdventureWorks2012 -Q "EXEC [dbo].[uspGetBillOfMaterials] 800, ''9/13/2014''" -s "," -o "' +
       @filePath + @fileName +
	   '" '
EXEC   master..xp_cmdshell @sqlCommand;

-- create the attachment
SET @fileAttachment = @filePath + @fileName;

SET @emailSubject = 'Bill of Materials Report ' + @todaysDateFormatted

EXEC msdb.dbo.sp_send_dbmail 
  @profile_name='SQLAlerts', -- change your database mail profile name here
  @recipients='jpchen@handsonsqlserver.com', -- change the recipient(s) here, separated by ;
  @subject=@emailSubject, -- email subject
  @body='Please see attachment for today''s report for Bill of Materials!', -- note to show up in the email body
  @file_attachments=@fileAttachment -- email attachment

Check your email to see the email and the report attached. Beautiful isn’t it?

CSVEmailAttachment

7. Schedule a SQL Server Agent job to run the TSQL script you had created in step 6.

REFERENCES:
sqlcmd Utility
http://msdn.microsoft.com/en-us/library/ms162773(v=sql.110).aspx

xp_cmdshell Server Configuration Option
http://msdn.microsoft.com/en-us/library/ms190693(v=sql.110).aspx

How to setup database mail using a gmail account?

Don’t get confused or misled by its name “Database Mail,” you certainly don’t have to wait for the mailwoman or the FedEx truck to come by your house or apartment tomorrow to receive notifications or messages through letters. Database Mail is the solution for sending email messages from SQL Server. The messages can be query results, XML as an attachment, CSV data as an attachment, html reports, and many more. Wouldn’t it be nice instead of logging into each of the SQL Server instances that you support to look for SQL Server Agent job failures, you would get an email notifying you of the job failures and identifying which SQL Server the job failures are coming from? If you setting up database mail at work, you will probably have an SMTP server, please contact your Exchange server administration for the SMTP server, port number, and an account. If your company does not have a SMTP server or you are testing a database mail at home in your laptop (your development environment), let’s read on.

Let’s identify the pre-requisites, required items, and steps before going into the steps for setup so that we don’t fumble or scramble all over the place for these essential requirements and information:

  1. Create a gmail account and have the account and password handy. If you are setting database mail in one of your SQL Servers at work, then you should contact your Exchange administrator for the SMTP server and port number.
  2. Write down your database mail account name, description, email address, display name, and mail server name, mail server type, port number, user name, password, the options to enable ssl. It is required by the MSDB.DBO.SYSMAIL_ADD_ACCOUNT_SP. Explanations are as follows:
    • Account name: The name of the account to add. For example: INSTANCENAME_SQLAlert.
    • Email address: The email address to send the message or notification from. This is the gmail email address or an email account your Exchange server administrator created by you (you really don’t need a valid account for the SMTP server). For example: *****@gmail.com.
    • Display name: The display name to use on email messages or notifications from this account. For example: INSTANCENAME SQLAlert
    • Mail server name: The name or ip address of the SMTP mail server for the account you are using. For example: smpt.gmail.com.
    • Mail server type: The type of mail server. For example: SMTP.
    • Port number: 587 for gmail. The default port for your internal SMTP is 25 unless your Security administrators have changed it.
    • User name: Your user account name to log on to the email server. For example: *****@gmail.com
    • Password: The password for your email account.
    • Enable SSL: Specifies if the database mail will encrypt communications using Secure Socket Layer.
  3. Write down the profile name and the description you like for the profile. The MSDB.DBO.SYSMAIL_ADD_PROFILE_SP will need them to create a profile. The description is optional. But, it is usually a good idea to write one.
    • Profile name: The name for the new profile. For example: INSTANCENAME_Alert.
    • Description: The description for the new profile. For example: INSTANCENAME_Alert profile for database mail.
  4. To add the database mail account to a database mail profile you will use the stored proc MSDB.DBO.SYSMAIL_ADD_PROFILEACCOUNT_SP. You will need to write down the profile name and the account name.
    • Profile name: The profile name to add the account to. For example: INSTANCENAME_Alert.
    • Account name: The account to add to the profile. For example: INSTANCENAME_Alert.
  5. To grant permission for a database user to use a database mail profile you will use the stored proc MSDB.DBO.SYSMAIL_ADD_PRINCIPALPROFILE_SP. You will need to write down the principal name, profile name, and the default option.
    • Principal name: No. Not the principal for your high school. But, the name of the database user or role in the msdb database. You can use the public role or the guest user.
    • Profile name: The name of the profile. For example: INSTANCENAME_Alert.
    • Default: Specify if this profile is the default profile for the principal.
  6. Verify by sending a test email.

Here’s the script executed successfully to setup database mail using a gmail account in my development instance (my laptop):

-- Configuring Database Mail using Gmail
-- Date and time created: 2013-01-17 19:30:30.753

-- Step 1: Enabling Database Mail
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- Step 2: Adding Database Mail Account
-- Gmail: smtp.gmail.com Port: 587
-- Hotmail: smtp.live.com Port 587
-- Yahoo: stmp.mail.yahoo.com Port 25
-- AOL: smtp.aol.com Port 597
EXECUTE msdb.dbo.sysmail_add_account_sp
   @account_name='SQLAlerts',
   @email_address='emailaddress@gmail.com', -- modify this to your gmail account
   @display_name='SQLAlerts',
   @mailserver_name='smtp.gmail.com',
   @mailserver_type='SMTP',
   @port=587,
   @username='emailaddress@gmail.com', -- modify this to your gmail account
   @password='*****', -- the password for the gmail account
   @enable_ssl=1

-- Step 3: Adding Database Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
   @profile_name = 'SQLAlerts',
   @description = 'Profile for SQLAlerts';

-- 4. Adding Database Mail Account to Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SQLAlerts', -- profile name from step 3
    @account_name = 'SQLAlerts', -- profile name from step 2
    @sequence_number = 1 ;

-- 5. Listing all Database Mail Profiles with their respective Accounts
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

-- 6. Grant permission for a database user or role to use the database mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
	@principal_name = 'public',
	@profile_name = 'SQLAlerts', -- profile name from step 3
	@is_default = 1;

-- 	7. Verification: Sending a test email
DECLARE @EmailBody nvarchar(225), @EmailSubject nvarchar(125)
SELECT @EmailBody = 'Test email from ' +@@SERVERNAME+ '. If you can see this message, this means database mail is working successfully!'
SELECT @EmailSubject = 'Test email from ' +@@SERVERNAME 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLAlerts',
    @recipients = 'emailaddress@company.com', -- modify this to your email address
    @body_format = 'TEXT',
    @body = @EmailBody,
    @subject = @EmailSubject

REFERENCES:

Database Mail
http://msdn.microsoft.com/en-us/library/ms175887(v=sql.105).aspx

sysmail_add_account_sp
http://technet.microsoft.com/en-us/library/ms182804(v=sql.105).aspx

sysmail_add_profile_sp
http://technet.microsoft.com/en-us/library/ms188058(v=sql.105).aspx

sysmail_add_profileaccount_sp
http://msdn.microsoft.com/en-us/library/ms186258(v=sql.105).aspx

sysmail_add_principalprofile_sp
http://msdn.microsoft.com/en-us/library/ms187911(v=sql.105).aspx