dotnet web sql business etc

15 березня, 2011

SQL 2008: send a .csv file using sp_send_db_mail

In SQL server 2008 you can send results of a query stored in a .csv formatted file attachment. To do that, you have to play around with sp_send_dbmail stored procedure parameters. Here is approximate example of what such script would look like:
exec sp_send_dbmail 
      @recipients = ';'
    , @query = 'SELECT TOP 100 * FROM [torders] ORDER BY [date] DESC'
    , @execute_query_database = 'my_database'
    , @attach_query_result_as_file = 1
    , @query_attachment_filename = 'Last10orders.csv'
    , @query_result_separator = ','
    , @query_result_no_padding = 1

The resulting file will arrive, but the values will be crammed into one cell. To obtain a trule comma-separated data: save the file on to your computer, open Microsoft Excel application, open the file. In the text import wizard window, set 1 select file type delimited, in the step 2 select delimiter: comma (diselect tab), save the resulting file as .csv type.

The problem preventing from obtaining a well-formatted .csv file in the beginning is the fact that SQL saves files in Unicode format, and there is not parameter for sp_send_dbmail to indicate this explicitly (like for SQL 2000 xp_sendmail, parameter @ansi_attachment=true), so a small workaround in needed.

Мітки: ,

0 коментарі(в):

Дописати коментар

Підписка на Дописати коментарі [Atom]

<< Головна сторінка