database email with attachement(excel file / pdf file)?

Question

I've to send a mail from database to my mail-id with attachment.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '[email protected]',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

from this code i'm getting result in txt file. but i want to get query result in pdf file or excel file

anybody known means please share with me.

advance thanks !!! :)

Answer 1

This will attach your query result as a csv, just change it to filename.xls if you prefer that format. I think csv works better, with excel you might get a warning saying the file is in a different format than specified by the extension.

Also I used CHAR(9) or TAB for the query result separator, you can change that to whatever works for you.

I also added SET NOCOUNT ON to your query, if you don't have that you'll get (1000 rows affected) (whatever number of rows your query returns) at the end of your excel file.

DECLARE
    @tab char(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '[email protected]',
    @query = 'SET NOCOUNT ON 
              SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
              WHERE DueDate > ''2004-04-30''
              AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1,
    @query_attachment_filename='filename.csv',
    @query_result_separator=@tab,
    @query_result_no_padding=1