Email As Csv Using Database Mail

Hi everyone,

I'm having problems sending my query results as a csv file. Ideally I would be able to send my results from a sql job every night as a csv but I'm having problems doing it. My code is shown below




Code Block

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'admin'
,@recipients = m@m.com
,@subject = 'Notification Created Today'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='

select
a1.c#, cli, fe, type, dateReq, p, (select convert(varchar(50),convert(money,amount),1)), (select convert(varchar(50),convert(money,vat),1)),(select convert(varchar(50),convert(money,total),1)), cReq
from Disb_1 a1 inner join Details on a1.c#=Details.c#
where datediff(day,getdate(),dateReq)=0 and dateReq <= getdate()'






I've tried many different things and been through the parameters of sp_send_dbmail but I just cant find anything.

Also, how can I use column names for those money conversions? At the moment it just says 'no column name'

Thanks,

Steve