A nice little nugget today for anyone who uses SQL dbmail to send data in csv attachments.
Sometimes, depending on your language settings, CSV files don’t open correctly in Excel. Excel ignores the ‘,’ and puts all data in column A. You then have to go through the hassle of closing the file and opening it throug the flat file wizard.
There’s a very nice solution to this that I found in this thread. It’s to insert a new row in the top of the file containing “sep=,”. This forces Excel to understand that it is a comma delimited file, and ensures that it will open correctly.
The only problem, how do you insert a header line when using dbmail?…
[Caution – ugly hack alert…]
You can alter the name of the first column to include this header text. We simply rename “Column1” to “sep=,{CR}{LF}Column1”. Then when dbmail prints out the column headers in the file, the Column1 name will be split on two lines, preceeded by “sep=,”.
Excel treats this first row as an instruction and does not display it, just uses it to make sure it formats the data correctly.
So how do we do this in SQL?
DECLARE @Column1Name VARCHAR(255) DECLARE @Query VARCHAR(2048) SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Column1]' SET @Query = 'SELECT Column1 AS ' + @Column1Name + ', Column2, Column3 FROM myTable' EXEC msdb.dbo.sp_send_dbmail @profile_name=XXX ,[Other parameters as required] ,@query=@Query ,@attach_query_result_as_file=1 ,@query_attachment_filename='QueryResults.csv' ,@query_result_separator=',' --enforce csv ,@query_result_no_padding=1 --trim ,@query_result_width=32767 --stop wordwrap
And there you go. It’s a bit of a hack, but works well.
Frog-Blog-Out
Excellent solution. Saved me several hours. Thank you.
Great workaround !!!! I didn’t have many options to implement a solution because I wasn’t able to use SSRS neither SSIS. Thank you !!!!
Excellent solution thanks.
Excellent stuff – much appreciated.
Thank you! This is great!
Big help, thanks….
But where do the ———, ———–, ————–, in the second row come from as they are not in the data? Or more importantly, how to remove them?
Hi Richard
There’s no option to remove the dashes I’m afraid, you’re stuck with them if you want to include the header column names.
However I did see a workaround for this, can’t remember where I’m afraid. And that is to turn off headers in the options (@query_result_header=0) and then have the column headers in the first row of results. e.g.
SELECT ‘Column1Name’, ‘Column2Name’, ‘Column3Name’
UNION ALL
SELECT Col1, Col2, Col3 FROM MyTable
Not an elegant workaround, but as far as I’m aware it’s the only option you’ll have.
Regards
Alex
Great thanks. Its works like a charm
How can we remove the remove the carriage return in the query, to avoid stop the column text going across multiple rows.
DECLARE @Column1Name VARCHAR(255)
DECLARE @Query VARCHAR(2048)
SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘Column1]’
SET @Query = ‘SELECT Column1 AS ‘ + @Column1Name + ‘, Column2, Column3 FROM myTable’
Thanks Richard! This is great!
The above works, but how do you store it in a variable to send that in @query of sp_send_dbmail.
Best,
Ani
Figured out on my own. Thanks!
Thank you so much!
Excellent solution!
I used this solution but I am not getting the expected output. I followed the same steps. Please review below sample code which I used..
/*
declare @columnname varchar(255),@query varchar(2000)
SET @columnname = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘OrderNo]’
set @query = ‘select OrderNo as ‘ + @columnname + ‘,Amount from tblOrders with(nolock)’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBAdmin’,
@recipients = ‘ianujsoni@gmail.com’,
@subject = ‘SQL Mail’,
@Body = ‘This is the mail generated from SQL’,
@query= @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘Orders.csv’,
@query_result_separator=’,’,
@query_result_no_padding=1,
@query_result_width=32767
*/
When I open the csv file column width is not properly set.
Can you please help me to address this issue?
Thanks,
Anuj
Hi Anuj
There is nothing in the CSV that tells excel what column width to use, so all column widths are default. This is the expected behaviour and I don’t think it can be overridden, unless there’s a setting in Excel somewhere. It’s certainly not a bug in the CSV generation code.
The @query_result_width just stops a single row of results being broken up into two rows.
Regards
Alex
I found the problem is db_mail sends the csv with UNICODE encoding (save as in notepad), once you change the file to ANSI, wala, XL opens it properly.
Good info, thanks for sharing Paul
Yes, need to figure out a way to have it send csv attachment with ANSI as default, from what I’ve seen, may need to create my own sp_send_dbmail2 with encoding as a @parameter, as there seems to be no configurable option in dbmail, or existing parameter in the sp to indicate so. if I find any “quick” solution, I will post here. Thank god for Google, 20 years ago nothing of the sort, and issues like this took weeks to resolve. 😉
Genius.
Brilliant! Million thanks!
You can make it easier (works in SQL Server 2008 R2):
SET @Query = ‘print ”sep=,”; SELECT Column1, Column2, Column3 FROM myTable’
Setting @query_result_separator to a ‘tab’ delimited column works also. Since sql mail always saves the csv file as unicode, this is the only delimiter that works by default:
DECLARE @tab char(1) = CHAR(9)
@query_result_separator=@tab
Good info, thanks Rob
Rob,
The tab character solution worked perfectly! Thank you!
…on a side note: is anyone a little surprised that Msoft didn’t ensure these two pieces worked together properly?
Kind of related, but I’ve had problems with stored procedure code saved with “UTF-8” encoding to where they compiled normally (via ps release scripts), but did not run normally. When “re-saved” with Unicode or ANSI encoding and re-compiled, they ran normally. One of our developers was saving with UTF-8 encoding, very strange.
It works
lifesaver! this worked perfectly as I couldn’t run the select within the job spec (lots of unions and made up field names).
This works too
This was very helpful!
fantastic hack! works perfect. Thank you for the post!
You sir,
Are a gentleman and a scholar.
I just use a tab for @query_result_separator and it formats correctly for viewing csv in excel.
EXEC msdb.dbo.sp_send_dbmail
@profile_name=’donotreply’
,@recipients =’xx@xx’
,@query= @query
,@subject= ‘xx’
,@attach_query_result_as_file=1
,@query_attachment_filename=’xx.csv’
,@query_result_separator=’ ‘ — ***LITERAL TAB****
,@query_result_no_padding=1 –trim
,@query_result_width=32767 –stop wordwrap
It messed up my formatting when I submitted, but it is supposed to be the amount of space for a tab between the two quotes
Great Thanks
I faced this issue from 2 days, but with your help this issue is resolved now. But I have another issue related to File Size. My File size is more than 1 MB then in this case what should I do? Please suggest any one
You can change the attachment size limitation in the Database Mail Configuration, just increase the ‘Maximum File Size (bytes)’ property
thanks Alex, but I read any where this limit is up to the 1 MB, But I can set this 1000000000 bytes (1000 MB), so what is correct? I am also send to you reference site below.
https://msdn.microsoft.com/en-IN/library/ms190307.aspx (Under the @file_attachments description).
The link you included states correctly that “By default, Database Mail limits file attachments to 1 MB per file”.
But this is only the default, it can be changed to anything you like, higher or lower.
Although I would not recommend changing this to 1GB as per your comment – your email server may complain about this!!
Great Thanks. My Assignment is Finished Now..
Hello Alex:
Thanks for this code, its perfect what I need.
I appended SET NOCOUNT ON for @Query parameter.
But I need to remove the —– from the attachment file. Can you please help me?
As far as I know, the only way of doing that is by removing the header from the output (@query_result_header=0).
If you need the header row then just change your query so that the first line of results is the text that you want in the column headers.
i.e. SELECT ‘ColumnHeader1’, ‘ColumnHeader2’, etc.
UNION ALL
SELECT [your original query]
I just discovered a great solution for file attachments with SQL sp_send_dbmail Stored procedure
If we want to send output of any query as excel attachment (Where TABLE1 can be a temp table or permanent table in DB)
DECLARE @NumberRecords INT
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
SET @NumberRecords = @@ROWCOUNT
DECLARE @RowCount INT= 1
DECLARE @DBDetailTotal VARCHAR(MAX) = ”
DECLARE @DBDetail VARCHAR(1500)
Iterate through the table rows and rap every column with html tag
WHILE @RowCount <= @NumberRecords
SELECT @DBDetail = '’
+ [COLUMN1] + ”
+ [COLUMN2] + ”
+ [COLUMN3] + ”
FROM @TABLE1 WHERE ID = @RowCount
SET @RowCount = @RowCount + 1
SET @DBDetailTotal = @DBDetailTotal + @DBDetail
END
SET @Query= ‘PRINT ”’ + @DBDetailTotal + ””
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Email Profile Name’
@recipients = @ToList,
@copy_recipients = @CCList,
@subject = @EmailSubject,
@body = @EmailBodyTemplate,
@body_format = ‘HTML’,
@execute_query_database = @DBName,
@query = @Query,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘AttachedFile.xls’,
@query_result_header = 1,
@query_result_separator = ‘,’,
@exclude_query_output = 0,
@query_result_no_padding = 1,
@query_result_width = 32767
With this approach we can also add formatting in HTML table tag which can be displayed in Excel file also.
The code is not posted properly… the goal is to iterate through the rows of table to be send as attachment , wrap the rows in HTML TABLE TR TD attributes and PRINT it in @query parameter
Fantastic! One of those rate but awesome” just what the doctor ordered” experiences.
Hi,
I am trying to send the query result as email with attachment. But its not working. Since the Query statement is very long.
Do we have any limitations on this or am i missing anything?
Please check the below Syntax.
IF EXISTS (SELECT content_id, content_title, last_edit_fname,last_edit_lname
FROM [EktronDev02].[dbo].[content]
WHERE [content_html] LIKE ‘%staging.hfma.org%’ OR [content_html] LIKE ‘%dev.hfma.org%’)
BEGIN
DECLARE @tab char(1) = CHAR(9)
SET @Query = ‘select i.content_id,i.content_title,j.FolderPath,links.link as Incorrect_URL,k.urlaliasnm as Alias,lib.filename as Direct_URL,i.last_edit_lname,i.last_edit_fname,i.last_edit_date from content as i inner join content_folder_tbl as j on i.folder_id=j.folder_id inner join UrlAliasMapping as k on i.content_id=k.TargetID inner join [library] as lib on i.content_id=lib.content_id CROSS APPLY dbo.GetHFMAContentLinks(cast(i.content_html as nvarchar(max))) links where i.content_type!=3 and (links.link like ‘%www.hfma.org%’ OR links.link like ‘%dev.hfma.org%’ OR links.link like ‘%staging.hfma.org%’) order by content_id ASC’
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = ‘CMS_sql_mail’,
@recipients = ‘test@gmail.com’,
@subject = ‘Production Elements Referencing Staging/Dev’,
@body_format = ‘TEXT’,
@Body = ‘Attached are those Production Elements Referencing Staging and/or Dev.’,
@attach_query_result_as_file = 1,
@query_attachment_filename=’BadReference.csv’,
@query_result_width = 1200,
@query_result_header = 0,
@query_result_separator=@tab,
@query_result_no_padding=1,
@query = @Query
END
Lemme know if i am missing any.
Thanks in Advance
Uma
Hi Uma
The @query parameter is an nvarchar(max), so you shouldn’t have any problems with the length of query you’re using. Are you declaring your local @Query variable as nvarchar(max)? If not then add this declaration and it should hopefully work for you.
Thanks
Alex
Hi,
I am trying to send the result of two queries into two different sheets in an attachment .
Is it possible to do that? If yes, can someone help me with this?
Thanks
Lavanya
Hi Lavanya
I’m not aware of any way of doing this with this method of emailing. You’d have to use Reporting Services instead.
Regards
Alex
Yes, dynamically build the sp_send_dbmail call
ou have to build @file_attachments.
DECLARE @attachments varchar(512)
SET @s = @2PATH + ‘;’ + @1PATH
Then pass @attachments to @file_attachments
Hi, I have been struggling for the above task since two days
I want following format need to do. So please help me
i tried a lot to get that format.
declare @qry varchar(8000)
declare @column1name varchar(50)
SET @Column1Name = ‘[sep=;’ + CHAR(13) + CHAR(10) + ‘StatusTypeID]’
select @qry=’set nocount on;select top 1 TypeID ‘ + @column1name +’, col2,col3,col4
exec msdb.dbo.sp_send_dbmail
@recipients=”mailID”,
@query=@qry,
@subject=’Test Email’,
@body = ‘This is just for testing ‘,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘QueryResults.csv’,
@query_result_separator=’;’,@query_result_width =32767,
@query_result_no_padding=1
TypeID Detail message AID Stime Etime
I want those columns result in each column separately and should be wrapped.Because, in the above columns Detail and message having more text with include comma(,) and Stime and Etime should be come as dd/mm/yyyy time.
Hi, I have been struggling for the above task since two days
I want following format need to do. So please help me
i tried a lot to get that format.
declare @qry varchar(8000)
declare @column1name varchar(50)
SET @Column1Name = ‘[sep=;’ + CHAR(13) + CHAR(10) + ‘StatusTypeID]’
select @qry=’set nocount on;select top 1 TypeID ‘ + @column1name +’, col2,col3,col4
exec msdb.dbo.sp_send_dbmail
@recipients=”mailID”,
@query=@qry,
@subject=’Test Email’,
@body = ‘This is just for testing ‘,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘QueryResults.csv’,
@query_result_separator=’;’,@query_result_width =1000,
@query_result_no_padding=1
TypeID Detail message AID Stime Etime
I want those columns result in each column separately and should be wrapped.Because, in the above columns Detail and message having more text with include comma(,) and Stime and Etime should be come as dd/mm/yyyy time.
Can you give me some indication of what isn’t working? Or what error you’re getting?
Use Quotes around data so as it does not think embedded commas in field is not new columns. Or use a different column delimiter, such as tab or pound (#).
i noticed that if you define tab as a separator then .csv file opens up very nicely in both xls and xlsx format.
hi… can u pls give me an example with putting exact value for @query_result_separator so that we will get an excel sheet in desired format. I am also facing the same issue.
Hi, there’s an example of using this at the end of the main post, with code.
Hi Alex,
My query is doing well with attachment the problem is that it is not including all the records from result set. There are more than 2700 records the same query returns from table but when I check in mail, the attachment has only 150 records everytime I send the mail. Do you have any idea?
Query :
set @query = ‘select COL1, COL2,COL3,COL4,COL5 fromMyDB..MyTable with(nolock)’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipient,
@subject = @subject,
@Body = @body,
@query= @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘Myfile.xlsx’,
@query_result_separator=’,’,
@query_result_no_padding=1,
@query_result_width=32767
I have tried .csv, .xls, .xlsx formats..
This behaviour is a bug in the code which limits the total size of the attachment. This has been resolved in some versions of SQL Server. There’s a Connect item that contains some discussion and links that may be useful.
http://connect.microsoft.com/SQLServer/feedbackdetail/view/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit
Alex, have you figured out had to get a column to export leading zero’s with your attached code? Your solution worked great and exported into the correct .csv format, but one of my columns has a value of 0001 which is reduced to 1 once you open the excel.
Do you know a work around for this? I have tried to add a single quote before the 0001 so that it’s formatted like ‘0001 but it still didn’t seem to work.
Let me know if you have any ideas! Great post!
Great thread, thanks all!
Hi Alex I found same problem every column data was placed in one column in CSV file so please help me . where I can placed my csv formet code in stored procedure
Regards
vikash
How we can remove the number of rows affected the CSV is showing at the end of file. Please help.
Try “SET NOCOUNT ON”
Hi Alex,
It’s great solution, however I’m not able to use this trick together with workaround removing dashes from result file.
Here is my query:
DECLARE @Column1Name VARCHAR(255)
DECLARE @Qry2 VARCHAR(2048)
SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘name]’
SET @Qry2 = ‘SELECT ”name” AS ‘ + @Column1Name + ‘, ”ip”, ”model” union all select name, ip, model from myTable’
exec msdb.dbo.sp_send_dbmail
@recipients=’mail@gsk.com’,
@query=@qry2,
@subject=’Client list’,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘result.csv’,
@query_result_separator=’,’,
@query_result_width =32767,
@query_result_no_padding=1,
@query_result_header=0
Despite adding sep alias I’m still getting results with comas. Any idea how to fix that?
Hi Alex,
I’m having the same issue as Woj. I can’t seem to combine the UNION column headers and get the file to open in excel in columns without the dashes.
I don’t believe there is any way of removing the dashes, at least when using this technique.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘maheshnew’,
@recipients = @email,
@body=@description,
@subject = @body11,
— @query = ‘SET NOCOUNT ON;select * from ##temp;SET NOCOUNT OFF;’,
@query=’SET NOCOUNT ON;select ”Vouchercode” as VoucherCode,”Expirydate” as ExpiryDate
union all select Vouchercode,convert(varchar,expirydate) FROM ##temp;SET NOCOUNT OFF;’,
@attach_query_result_as_file = 1,
@query_attachment_filename= ‘Vouchers.xls’,
@query_result_separator=@tab,
@query_result_no_padding=1,
@query_result_header = 0,
@exclude_query_output =1,
@append_query_error=0,
@query_result_width = 1000
This code is removes the dashes and no of rows effected on excel sheet.
am getting “Sep” keyword in first row. How to fix this?
Thanx a lott for your post. It helped. Cheers!!!
Hi Alex, that’s a great post, thank you. I also would like to share what worked for me, with less effort. It might help the others too.
DECLARE @query_result_separator CHAR(09)
, @query_result_width SMALLINT
–// Here is the trick
–// Just add this value in the procedure params and it should work fine.
SET @query_result_separator = CHAR(9)
SET @query_result_width = 32767
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailAddress
, @copy_recipients = @pEmailCopy_recipients
, @subject = @EmailSubject
, @body = @EmailBody
, @query_attachment_filename = @FileName
, @attach_query_result_as_file = 1
, @query_result_header = 1
, @query_result_width = @query_result_width
, @append_query_error = 1
, @query_result_no_padding = 1
, @query_result_separator = @query_result_separator
, @query = @SQLStmt
There are 2 Simpler Methods:
1)
Default Text File Sep for Excel is tab (even if extension is csv).
@query_result_separator=’ ‘
2)
Because DBMail just exports the console output you can add a line using Print
,@query = ‘SET NOCOUNT ON
GO
Print ”Sep=,”
SELECT * FROM table’
,@query_result_separator =’,’
Thanks for the post.
Thank you so much.
Nice Post
Thanks , It helps , But still looking to remove second row lines.
Saved my life thank you
Hi Alex,
Can be add formatting like making headers bold, adding Borders in the resulted excel file.
Can we also add one more result sheet in the same resulted excel.
“The File Format and extension of result.xls don’t match” error is coming after opening the file.
Hi Ritesh
I’m not aware of any way of formatting data, as the export is a csv text file, not an actual Excel formatted file. This is also the reason why you can’t have multiple results. To do this you’d need to use SSRS I expect.
Your error is happening because the file is a csv file, but you’ve given it a .xls extension. It is not an Excel file, so you should give it a .csv extension instead.
Alex
Thanks Alex for your help, (.csv) is working fine.
I had this problem. This is wonderful article which fixed my problem.
Thank you.
Amazing workaround, I was in need of this Workaround Badly
Thank you
Its working perfectly ,Thanks
Thanks!
Thank you very much man!!!!
Still a valuable post after 7 years! Thanks.