Post reply

Can DatabaseMail attach a formatted Excel file?

  • January 5, 2011 at 8:00 am

    #1269537

    Hello,

    I have followed the advise on the post for the following and have place a 'tab' in the query_result_seperator and the csv file is still opening incorrectly.

    I would just like to verify that I place a ' then hit the TAB key and close with '

    Any help would be appreciate.

    Code:

    exec msdb.dbo.sp_send_dbmail @recipients = '@.org;@.net;',

    @body = 'Here are the visits :',

    @query = 'exec usp_nh_PharamacyInterventionExport',

    @query_attachment_filename = 'results.csv',

    @query_result_separator = '',

    @subject = 'Payors Changed Yesterday',

    @query_result_header = 1,

    @attach_query_result_as_file = 1,

    @execute_query_database = 'it'

    thank you

  • January 5, 2011 at 8:07 am

    #1269539

    Try using CHAR(9) instead of using a TAB from the Keyboard, like Doug suggested a few posts earlier.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 5, 2011 at 8:19 am

    #1269545

    Tahnk you Jack that worked.

  • March 3, 2011 at 9:18 am

    #1294086

    just stumbled across this thread. worked a treat thanks.

  • March 4, 2011 at 6:40 pm

    #1294937

    I had a similar problem exporting a CSV file from SSRS, where Excel would open the file as a single column. Turned out that the default SSRS export encoding for CSV was unicode, and Excel did not know how to handle that. I found the "Render" section in rsreportserver.config where you can override the encoding - set it to ASCII and that fixed my problem.

    Not exactly the same as the OP's issue but hopefully this will help somebody.

  • January 10, 2012 at 4:23 pm

    #1431491

    Thanks, Ted!

  • August 3, 2012 at 8:27 am

    #1521432

    Hi, would it be possible to post the full script you got working please. Thank you.

  • June 25, 2014 at 9:05 am

    #1724676

    I'd like to see the script too. Trying to get a tab delimiter to work. the char(9) doesn't work

  • November 30, 2015 at 4:05 am

    #1842826

    Thanks a lot. it worked for me also.

    But still i am getting ------ one complete row after the header columns

  • December 1, 2015 at 12:21 pm

    #1843304

    Lavanyasri (11/30/2015)

    Thanks a lot. it worked for me also.

    But still i am getting ------ one complete row after the header columns

    I started this thread years ago - brings back memories hehe :w00t:

    Funny, I just updated a query to fix this very issue you mentioned.

    Step 1: Prepare a table with your data AND a single row that will become your column headings (using UNION ALL), as follows:

    create table temptbl(ACCOUNT VARCHAR(15),FACILITY VARCHAR(255),SORTORDER INT)

    insert into temptbl(account,facility,sortorder)

    SELECT 'ACCOUNT','FACILITY',0 -- these will be your column headings

    UNION ALL

    SELECT

    ACCOUNT,

    FACILITY,

    1 -- these are your rows of data with a sort order of 1 to make sure they are situated below your column headings

    FROM someothertbl

    Then in your database mail code, remove the automatic headings, because those come with the annoying dashes row that you're wanting to get rid of and populate your file making sure its sorted by SORTORDER:

    @profile_name = 'YOURPROFILE',

    @recipients = 'YOUR EMAIL ADDRESSES',

    @subject = 'SUBJECT',

    @query = 'set nocount on;SELECT account,facility FROM TEMPTBL order by sortorder',

    @attach_query_result_as_file = 1,

    @query_result_separator = '' ,

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'BOOK1.csv'

    Hope this helps.

Viewing 10 posts - 16 through 24 (of 24 total)

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