February 10, 2009 at 8:53 am
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
February 10, 2009 at 8:58 am
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
February 10, 2009 at 9:09 am
@query_result_separator
what do i change it to?
February 10, 2009 at 9:42 am
anybody?
February 10, 2009 at 10:25 am
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
February 10, 2009 at 11:00 am
im getting everthing on one column...
February 10, 2009 at 1:13 pm
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
February 11, 2009 at 2:34 am
still having trouble with guys
March 6, 2009 at 9:25 am
.
March 18, 2009 at 7:15 am
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.
March 25, 2009 at 5:22 am
Yisaacs,
I am persisting with exactly the same problem and running into the same brick walls you are.
Trawling the internet suggests that it seems to work in some environments and not in others.
I've tried csv/xml with variations on all the switches.
I either get an attachment with most of the output truncated or all in one column like you or some rubbish hex data.
On their own, the queries are fine. Whack them into sp_send_dbmail and you enter the secret society of SQL mail programmers. Someone has the answer, but ain't telling. 🙂
March 25, 2009 at 5:25 am
Yisaacs,
I am persisting with exactly the same problem and running into the same brick walls you are.
Trawling the internet suggests that it seems to work in some environments and not in others.
I've tried csv/xml with variations on all the switches.
I either get an attachment with most of the output truncated or all in one column like you or some rubbish hex data.
On their own, the queries are fine. Whack them into sp_send_dbmail and you enter the secret society of SQL mail programmers. Someone has the answer, but ain't telling. 🙂
March 25, 2009 at 5:27 am
Yisaacs,
I am persisting with exactly the same problem and running into the same brick walls you are.
Trawling the internet suggests that it seems to work in some environments and not in others.
I've tried csv/xml with variations on all the switches.
I either get an attachment with most of the output truncated or all in one column like you or some rubbish hex data.
On their own, the queries are fine. Whack them into sp_send_dbmail and you enter the secret society of SQL mail programmers. Someone has the answer, but ain't telling. 🙂
March 25, 2009 at 5:29 am
Yisaacs,
I am persisting with exactly the same problem and running into the same brick walls you are.
Trawling the internet suggests that it seems to work in some environments and not in others.
I've tried csv/xml with variations on all the switches.
I either get an attachment with most of the output truncated or all in one column like you or some rubbish hex data.
On their own, the queries are fine. Whack them into sp_send_dbmail and you enter the secret society of SQL mail programmers. Someone has the answer, but ain't telling. 🙂
January 13, 2010 at 5:45 am
Instead of using a space or double qoutes, use TAB
@query_result_separator=''
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply