Post reply

bcp or sp_send_dbmail

  • December 22, 2010 at 4:30 pm

    #149524

    trying to send out results of query from SP via bcp or @query in sp_send_dbmail ..

    but:

    - bcp does not export column headers

    - dbmail exports but puts dashes underneath them

    really?!

    how to do this in a painless way (no manually adding column headers etc.)?

    also in dbmail , i use @query_result_separator = ',' & give filename as .csv

    but Xcel doesn't open file properly - just puts all data in one column.

  • December 23, 2010 at 9:33 am

    #1265995

    is this a select query? why not just use SSRS?

  • December 23, 2010 at 9:37 am

    #1265998

    yes, select.

    SSRS is included in list of painful ways.. 🙂

  • December 23, 2010 at 9:40 am

    #1265999

    in my experience it's a lot less painful than spending 3 hours with the string functions getting the email just right. i can't even remember most of the string functions since i use SSRS for a lot of these things

  • December 24, 2010 at 7:18 am

    #1266200

    SSRS isn't painful. 🙂 if you installed it,

    1. run the ssrs config utility and set your accounts,

    2. open VS 2005 or BIDS,

    3. select report wizard and it steps you through each part to make a report.

    4. You can deploy reports using:

    A. project config, or

    B. just open up the report services website and upload the datasource and report .rdl.

    5. Set up a subscription using the website by choosing the subscriptions tab on the report.

    6. Your done.

    The other option is use .net to format and send the report using HTML email. We've done that in the past but ssrs is still easier.

    We used db mail previously and always had so many issues with it (formatting, etc) that the only times we used it was to notify us of errors. We don't care about formatting IT emails so it wasn't an issue for us to us dbmail.

  • December 27, 2010 at 5:34 pm

    #1266599

    fixed the 'sp_send_dbmail .csv attachment Xcel not opening properly - putting all data in one column' issue with a small hack:

    in sp_send_dbmail, do:

    @query_attachment_filename = 'blah.csv'

    ,@query_result_separator = ' ' --tab

    btw, this is internal report so SSRS seems overkill. also, recipients accepted to have dashes underneath column names, so dbmail is good for quickly testing reports

  • December 27, 2010 at 10:17 pm

    #1266614

    asdfgh-617842 (12/27/2010)

    fixed the 'sp_send_dbmail .csv attachment Xcel not opening properly - putting all data in one column' issue with a small hack:

    in sp_send_dbmail, do:

    @query_attachment_filename = 'blah.csv'

    ,@query_result_separator = ' ' --tab

    btw, this is internal report so SSRS seems overkill. also, recipients accepted to have dashes underneath column names, so dbmail is good for quickly testing reports

    Have you looked up p_send_dbmail? They have a pretty easy code example of how to convert some simple data to an HTML format as part of the body of the email. It's probably not the super simple thing that you want, but it doesn't use SSRS. 😉 If you're any good at all with HTML, you can whip up a very pretty standard format very quickly.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply