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

Be Sociable, Share!
You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.