sp_send_dbmail as CSV

  • 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

  • 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

  • @query_result_separator

    what do i change it to?

  • anybody?

  • 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

  • im getting everthing on one column...

  • 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

  • still having trouble with guys

  • .

  • 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.

  • 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. 🙂

  • 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. 🙂

  • 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. 🙂

  • 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. 🙂

  • 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