sp_send_dbmail ... Help

Question

sp_send_dbmail ... Help

Ram:)

SSCrazy

Points: 2246
More actions
November 19, 2010 at 1:47 pm

#231425

Hi Every body..
Can any one suggest me how to attach the result of select statement to the mail, may be format is xls. ( using sp_send_dbmail )
Regards..
🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

[ @query = ] 'query'

Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks Administrator',

@recipients = 'danw@Adventure-Works.com',

@query = 'SELECT COUNT(*) FROM AdventureWorks.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 ;

Ram:) SSCrazy Points: 2246 More actions · Answer 2

Thanks..

I tried.. Its working fine.. But is there any way to get data in excel format..

Ram..

🙂

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

Doesn't look like there's a direct way to do it. See:

http://www.sqlservercentral.com/Forums/Topic885747-1291-1.aspx

for some possibilities.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Try to assign an xls file name to @query_attachment_filename

like

@query_attachment_filename ='Test.xls'

IIRC, that's the way I did it when I needed the output in xls format.

If it doesn't work, post back. (I'll dial into my work PC and see if I can get the mail configuration out for an example).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]