Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_send_dbmail as CSV Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 4:22 AM
Points: 120, Visits: 288
hi guys im trying to send a resultset via mail in a csv file attachment, everything works great but the resultset within the csv file is not in columns and looks terrible....

this is what my code looks like...


EXEC msdb.dbo.sp_send_dbmail

@recipients =N'testuser@testuser.com',
@body = 'TEST',
@body_format ='HTML',
@subject ='TESTING',
@profile_name ='LOCALMail',
@query='SELECT TOP 10 * FROM [TestServer].[TestDB].[dbo].[BANK]',
@query_attachment_filename = 'bank.csv',
@attach_query_result_as_file = 1

Post #653836
Posted Tuesday, February 10, 2009 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
There's another parameter that might help, @query_result_separator. It's char(1), and defaults to a single space. If you change that, it might help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #653844
Posted Tuesday, February 10, 2009 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 4:22 AM
Points: 120, Visits: 288
@query_result_separator

what do i change it to?
Post #653866
Posted Tuesday, February 10, 2009 9:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 4:22 AM
Points: 120, Visits: 288
anybody?
Post #653915
Posted Tuesday, February 10, 2009 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 19, 2012 6:44 AM
Points: 273, Visits: 56
Are you getting each row inone column or your table columns are getting splitted?

Have you tried in xls file like @query_attachment_filename = 'bank.xls'.
and you can findout the reason..colud be the sql table column name are with spaces in between



Comments please....

Srihari Nandamuri
Post #653985
Posted Tuesday, February 10, 2009 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 4:22 AM
Points: 120, Visits: 288
im getting everthing on one column...
Post #654023
Posted Tuesday, February 10, 2009 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
The default for the query attachment is to just have a blank space between the columns. One blank space. Just because you use the ".csv" file extension doesn't change that. If you don't use the separator, and just save as csv, what you get is one column, with spaces between the "columns" that you should have.

For simple-CSV, you should separate with a comma. That, after all, is what CSV is supposed to mean. However, CSV files usually have quotation marks around the data, as well as commas, because a comma by itself is too error-prone. The separator, however, is limited to one character, so you can't do that.

On the other hand, if you separate with a tab-character, or with a vertical pipe, Excel and most other programs can use those to easily break columns back up again. You'll want to list the file as either txt or xls in that case, not csv. Both are pretty standard, but tabs often make the file more human-readable than pipes, if that matters.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #654151
Posted Wednesday, February 11, 2009 2:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 4:22 AM
Points: 120, Visits: 288
still having trouble with guys
Post #654559
Posted Friday, March 6, 2009 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 29, 2010 8:33 AM
Points: 1, Visits: 9
.
Post #670363
Posted Wednesday, March 18, 2009 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 1, 2013 3:03 AM
Points: 9, Visits: 93
I was trying to solve this today.
Changing setting @query_result_separator to a tab enables Excel 2003 to read that on my computer.
If your target is another app, then you might need to use a different separator and/or convert your columns to e. g. to varchars and prepend and append double quotes (") to them.
Post #678398
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse