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 !!! :)

Frank · Accepted Answer · 2013-09-24 15:00:48Z

14

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

answered Sep 24, 2013 at 15:00

Frank

7131 gold badge8 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

twoleggedhorse Over a year ago

This doesn't actually answer the OP's question, excel or pdf. What you are suggesting is the creation of a csv which then has to be manually updated to have an xls extension.

twoleggedhorse · Accepted Answer · 2012-11-08 13:47:21Z

0

Is there any reason the attachment has to be created by SQL server?

I don't think this can be done as query results are text and the sql engine doesn't know about PDFs or excel. You can script the results as HTML by using the XML PATH attributes (example at bottom of this page: http://msdn.microsoft.com/en-us/library/ms190307.aspx).

I guess you could use a similar method to build up an xml string and then use the .xlsx extension in the @query_attachment_filename variable. Excel 2007+ is essentially an openxml file but I'm not sure how easy/feasible this would be.

answered Nov 8, 2012 at 13:47

twoleggedhorse

5,0484 gold badges26 silver badges38 bronze badges

1 Comment

twoleggedhorse Over a year ago

Why the downvote? The OP is specifically asking for excel or pdf output and this cannot be done using only SQL server.

Amit Soni · Accepted Answer · 2018-12-26 08:52:50Z

0

One way is to use the extended stored procedure xp_sendmail. It does require that you have a MAPI profile and SQL Mail configured. Then it can be used like this:

xp_sendmail
@recipients = '[email protected]',
@subject = 'Query Results',
@query = 'SELECT fname, lname FROM Employees WHERE empid = 9'

In the above example the results of the query will be included in the e-mail message. Read in SQL Server Books OnLine more about the additional parameters for xp_sendmail. They control how the query is executed (database context, database user) and how it is displayed (headers, formatting, attach query results in a file).

Here is more info on configuring SQL Mail: http://support.microsoft.com/kb/q263556/

edited Dec 26, 2018 at 8:52

Amit Soni

3,3267 gold badges34 silver badges52 bronze badges

answered Nov 28, 2013 at 10:34

Indranil.Bharambe

1,4983 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related