Tuesday, December 21, 2010

How to send a CSV or XLS file as attachment using SQL Database mail with proper formatting


Today I encountered a weird (may be not) problem…. One of my colleagues was asked to generate a CSV file and email it to someone. He was getting ready to create a SSIS package with data flow task and everything. I thought why to waste time doing this when there is functionality available in SQL Server. Here you go…

Now please remember that the parameter @query_result_separator = ' ' needs to be re-typed to TAB if you are copy pasting the code. When you copy and paste the formatting in SSMS resets it to something else and the formatting in CSV file is all messed up

DECLARE @sub VARCHAR(100)
DECLARE @qry VARCHAR(1000)
DECLARE @msg VARCHAR(250)
DECLARE @query NVARCHAR(1000)
DECLARE @query_attachment_filename NVARCHAR(520)

SELECT @sub = 'TEST XML ATTACHMENT'
SELECT @msg = 'Please refer to the attached spread sheet for the report.'
SELECT @query = ' SET NOCOUNT ON;
            Select top 10 * from master..sysobjects WITH(NOLOCK)  '

SELECT @query_attachment_filename = 'test.csv'

EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SQLMAIL',
            @recipients = 'sqldba@sqldba.com',
            @copy_recipients = 'sqldba@sqldba.com',
            @body = @msg,
            @subject = @sub,
            @query = @query,
            @query_attachment_filename = @query_attachment_filename,
            @attach_query_result_as_file = 1,
            @query_result_header = 1,
            @query_result_width = 256 ,
            @query_result_separator = '   ' ,
            @query_result_no_padding =1;

6 comments:

  1. very USEFUL.
    Thanks for sharing it.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Help Me Please,
    when i'm trying send email with attachment file .csv i'm get this error

    Database Mail is not permitted to send files with the file extension report.csv

    ReplyDelete
  4. Hi I tried this it is not getting proper way in output excel when i opened in mail attachment.
    Columns not getting properly i.e result is getting under next column
    I want to get column result in its filed only even that column result having large text.
    Please help me..

    ReplyDelete
  5. I am using excel 2016. Is there any difference between versions to get data properly?

    Is there any way to get that and is there any need to change in dbmail parameters?
    Your answer is highly appreciated.

    ReplyDelete