Post reply

Using SP_Send_DBMail to send query as csv

  • November 12, 2014 at 9:58 am

    #291955

    Hey All!

    I have this code written up so that i can change our existing SSRS reports and SQL Agent job reports to only send the report if data is present..Currently we have a bunch of reports that are sent out blank.

    I did some research and was to get up to this point:

    IF Exists ( Select * from table where last_updatedate>'11/11/2014')

    execute msdb.dbo.sp_send_dbmail

    @profile_name=Null,

    @recipients='email@domain.com',

    @subject='Test',

    @Execute_Query_Database='DBName',

    @query='Select * from table where last_updatedate>''11/11/2014''',

    @attach_query_result_as_file=1,

    @query_attachment_Filename='TEST.csv'

    --@query_result_header = 1,

    --@query_result_width = 256 ,

    --@query_result_separator=' '

    IF @@ROWCOUNT = 0 raiserror ('No Data', 16, 1)

    It works fine and i get the results i want, but in the csv the column headers are all mixed up and the data is just a mess. From the other posts i've read, they mention that the @Query_Result_separator is where you want to change; The space in there is actually a TAB. If i enter the separator as ENTER, i get all the data in a single column, anything else and it looks like this: .

    Can someone help me figure out how to get the same same format as if i were to just copy results from SSMS And paste in excel? The last parameters are commented out because i was just playing around with those to see if i would get different results...no luck.

    EDIT: The dates are formated as DD-MM-YYY hh-mm-ss:ss, but i just enter it as mm\dd\yyyy. Everything else is either INT or a varchar

  • November 17, 2014 at 7:30 am

    #1758979

    So i was able to get this script working on my end after playing around with the different parameters;

    IF Exists ( Select cht_number, cht_itemcode, cht_description from chargetype where last_updatedate>'11/11/2014')

    execute msdb.dbo.sp_send_dbmail

    @profile_name=Null,

    @recipients='mail@domain.com',

    @subject='Test',

    @Execute_Query_Database='DB_Name',

    @query='Select cht_number, cht_itemcode, cht_description from chargetype where last_updatedate>''11/11/2014''',

    @attach_query_result_as_file=1,

    @query_attachment_Filename='TEST.csv',

    @query_result_no_padding=1,

    @query_result_header = 1,

    @query_result_width = 256,

    @query_result_separator=''

    IF @@ROWCOUNT = 0 raiserror ('No Data', 16, 1)

    Would anyone be able to suggest other ways of getting the same result? What wuld be the best approach to applying this to existing, more complicated agent job reports or SSRS reports?

Viewing 2 posts - 1 through 1 (of 1 total)

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