Friday, May 7, 2010

[Files] Tricking Excel with SP_SEND_DBMAIL

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile',
@recipients = '',
@subject = 'Here is your file pull done',
@query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'yourfilename.csv',
@query_result_separator = ' ', -- tab
@exclude_query_output = 1,
@append_query_error = 1


Adrienne_PDX said...

This was EXACTLY what I needed. Thank you very much for posting!

bourgon said...

Glad it helped.