locked
sp to send email with csv RRS feed

  • Question

  • How to export a query into a csv and send as email all within a sp?
    Monday, February 13, 2012 2:23 PM

Answers

  • If you don't need the physical file you could use sp_send_dbmail with the following parameter:

    @attach_query_result_as_file = 1

    @query_attachment_filename =YourFile.csv

    @query_result_separator =','

    But you'll need to make sure the data you export do not contain commas....

    For details, please see BOL.

    Monday, February 13, 2012 5:25 PM
  • If this file is supposed to be opened with Excel directly from the mail attachement, why does it have to be a csv file?

    Did you try to change the filename to an .xls extension and @query_result_separator = ' ' (this is a TAB between the ' ' ) instead of comma?

    Wednesday, February 15, 2012 10:11 PM

All replies

  • Hi

    You need to bcp out the CSV file. see the link bellow

    http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

    then use sp_send_dbmail and attah the generated cvs file. See BOL for more info

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Naomi N Monday, February 13, 2012 2:36 PM
    Monday, February 13, 2012 2:28 PM
  • Cool, shame I have to actually create the file physically, would be nice to just send the file data.

    its it easy to delete the file automaticaly after sending?

    Monday, February 13, 2012 4:03 PM
  • I do not believe so since  sp_send_dbmail initiates an asynchronous mailing.  If you ran another xp_cmdshell step immediately after the sp_send_dbmail that deletes the file, the file might well be deleted before the mail gets sent.

    You could create a SQL Agent job to run hourly or daily to delete files in your bcp directory that are older than an hour.  That has been good enough for my purposes.

    RLF

    Monday, February 13, 2012 4:14 PM
  • If you don't need the physical file you could use sp_send_dbmail with the following parameter:

    @attach_query_result_as_file = 1

    @query_attachment_filename =YourFile.csv

    @query_result_separator =','

    But you'll need to make sure the data you export do not contain commas....

    For details, please see BOL.

    Monday, February 13, 2012 5:25 PM
  • You can create the query string dynamically to create the CSV output.  It might look something like this:

    EXEC msdb..sp_send_dbmail
     @profile_name = 'profile_name',
     @recipients = 'someaddress.somewhere.com',
     @subject = 'Test CSV output',
     @body = 'Test CSV output',
     @query = 'SELECT ''a'' + '','' + ''b'' + '','' + ''c''',
     @attach_query_result_as_file = 1,
     @query_attachment_filename = 'test.csv'

    Monday, February 13, 2012 6:01 PM
  • people in my dept say its not best practise to send emails with ms sql, how can I reply?
    Monday, February 13, 2012 6:12 PM
  • can you declare in the same sp some complex code above this instead of stuffing it in the @query code

    SP___________________________________________________

    --complex select here with params

    EXEC msdb..sp_send_dbmail
     @profile_name = 'profile_name',
     @recipients = 'someaddress.somewhere.com',
     @subject = 'Test CSV output',
     @body = 'Test CSV output',
     @query = results of the complex select
     @attach_query_result_as_file = 1,
     @query_attachment_filename = 'test.csv'

    __________________________________________________

    Monday, February 13, 2012 6:23 PM
  • yes, declare a variable as nvarchar(max), then insert the query string in that variable and then change the @query =  line to @query = @somevariablename
    Monday, February 13, 2012 6:36 PM
  • yeah but thats still sticking a query into a string, I hate doing that, I would usually use a view or function instead but I'm passing lots of params into the query so I didn't want to set the function up
    Tuesday, February 14, 2012 10:03 AM
  • To add to what's already been said re: attaching a file to an email.

    I was unsure about when it would be safe to delete the file attachment, due to the asynch nature of Database Mail. In the end I set up a nightly job to delete the attachments created from the past day.

    However, I believe the file contents are also stored in the sysmail_attachment table in msdb - I'm unsure/couldn't find any real reference, as to whether when you call sp_send_dbmail it stores the file contents immediately in this attachment table and then when it does come to send the email, attaches it from there as opposed to needing the file (i.e. in this scenario, it could be safe to delete the file straight away). Or, whether the physical file is still needed after sp_send_dbmail has returned (i.e. in this scenario, it would not be safe to delete the file straight away).

    So, a period clean up job is the route I went down. Also note, you may want to periodically clear down email log within msdb using  sysmail_delete_mailitems_sp: http://msdn.microsoft.com/en-us/library/ms190293.aspx


    Blog: www.adathedev.co.uk

    Tuesday, February 14, 2012 10:24 AM
  • eg , the params fromDate passed to the sp are not understood in the query string Must declare the scalar variable "@fromDate". I done something like this, but it seems clunky DECLARE @q VARCHAR(max) SET @q = ' SELECT [OrderDate] , [Site] FROM [Archive] WHERE OrderDate ' + convert(varchar, @fromDate, 126) + ' etc
    Tuesday, February 14, 2012 10:37 AM
  • everything works except when opening in excel everything is in the 1st column:( EXEC msdb.dbo.sp_send_dbmail @recipients = @to, @importance = 'HIGH', @body_format = 'HTML', @body = 'Tm data', @subject = 'orders' , @query = @q, @attach_query_result_as_file = 1, @query_result_separator =',',@query_result_no_padding = 1, @query_attachment_filename = 'test.csv';
    Tuesday, February 14, 2012 12:26 PM
  • Ok...I've changed this up a little.  Make sure you're query string sets nocount on.  Then add @query_result_header = 0 if you want column headings removed.  Then, and this is the sad part, save the file from the email and open it through Excel rather than dbl clicking the attachment.  This should open the csv wizard in Excel where you can select 'comma' as the delimiter.  It is a workaround but does allow you to send a .csv file from a proc.  I think, given the steps involved I'd probably look into a different solution using bcp or some other method...but this will work if it is your only option.  Good luck!
    Wednesday, February 15, 2012 10:04 PM
  • If this file is supposed to be opened with Excel directly from the mail attachement, why does it have to be a csv file?

    Did you try to change the filename to an .xls extension and @query_result_separator = ' ' (this is a TAB between the ' ' ) instead of comma?

    Wednesday, February 15, 2012 10:11 PM