Email as csv using Database Mail

Getting started with SQL Server

Steve McCall


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




Top

Re: Email as csv using Database Mail

Ian Roke


To use column names just put 'AS ColumnName1' after the statement ie 'convert(varchar(50),convert(money,amount),1)) AS ColumnName,'



Top

Re: Email as csv using Database Mail

Steve McCall

Thanks,

That sorts out that problem but I'm still having major problems with emailing as a csv. Can anyone help

Thanks,

Steve






Top

Re: Email as csv using Database Mail

Vidhya Sagar

Hi,

Try using the below select command
Code Block

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)),',', cReqfrom Disb_1 a1 inner join Details on a1.c#=Details.c#







Top