CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
cgsabari
cgsabari

Reputation: 615

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

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

Upvotes: 4

Views: 45316

Answers (3)

Indranil.Bharambe
Indranil.Bharambe

Reputation: 1498

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/

Upvotes: 0

Frank
Frank

Reputation: 713

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

Upvotes: 14

twoleggedhorse
twoleggedhorse

Reputation: 5048

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.

Upvotes: 0

Related Questions