Excel Document as attachment via dbmail

Question

Post reply

Excel Document as attachment via dbmail

mayankminawat

Mr or Mrs. 500

Points: 547
More actions
October 19, 2014 at 10:04 am

#300251

Hello All,
I'm trying to export the results of a query in an excel file and send that file via attachment in an email. The issue that I am facing is that the excel file is not properly formatted as it is a csv. I tried a tab-delimited format as well and even that is distorted. Can someone please suggest me the best way to send an excel file using this dbmail procedure?
Also, in my query I just have two columns right now.
DECLARE
@csv char(1)
BEGIN
set @csv= ','
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'nationstar University',
@recipients = 'donald@nation.com',
@query = 'select FNAME,LNAME from PED.dbo.TD_EmployeeProfile_FinalV2 where manager_number=100000491' ,
@subject = 'Test email',
@attach_query_result_as_file = 1,
@query_attachment_filename='filename.xls',
@query_result_separator=@tab,
@query_result_no_padding=1
END

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What do you mean by "distorted"?

Can you provide some sample data that will return "distorted"?

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]

mayankminawat Mr or Mrs. 500 Points: 547 More actions · Answer 2

This is how the two columns come out as in excel when I open the csv attachment in the email. It should rather come out in two different columns and not in one single column? Thanks for your help.

FNAME,LNAME

-----,-----

Rus,Corle

Evett,Porte

Syia,Ramirz

Brice,Frankn

Lina,Stenge

Bety,Colls

Noeita,Bth

Cthia,Hkins

Frri,Ami

lissa,Aman

LutzM SSC Guru Points: 107049 More actions · Answer 3

Try the following:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'nationstar University',

@recipients = 'donald@nation.com',

@query = 'select FNAME,LNAME from PED.dbo.TD_EmployeeProfile_FinalV2 where manager_number=100000491' ,

@subject = 'Test email',

@attach_query_result_as_file = 1,

@query_attachment_filename='filename.xls',

@query_result_separator='' -- tab

I'm using this configuration quite frequently with no issues whatsoever.

But there are a few items that need to be considered, for instance

a) make sure, there's no tab in any of the columns since this will offset the related row

b) use character values whenver possible (e.g. convert DATETIME values into a data format EXCEL recognize as datetime values

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]

mayankminawat Mr or Mrs. 500 Points: 547 More actions · Answer 4

mayankminawat

Mr or Mrs. 500

Points: 547

October 19, 2014 at 1:06 pm

#1753372

Thank you!! That actually worked.