how to Create Execl File From msdb.dbo.sp_send_dbmail and mail it

Question

Post reply

how to Create Execl File From msdb.dbo.sp_send_dbmail and mail it

guptaprashant1982-1107326

SSC Veteran

Points: 204
More actions
August 8, 2011 at 3:03 pm

#241437

how to Create Execl File From msdb.dbo.sp_send_dbmail and mail it

Viewing 11 posts - 1 through 10 (of 10 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

guptaprashant1982-1107326 (8/8/2011)
how to Create Execl File From msdb.dbo.sp_send_dbmail and mail it

Some of the options are:

1) SSRS subscription

2) CLR

3) Attach query result as a .CSV file

4) Export/SSIS

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 2

-- Start T-SQL

USE msdb

EXEC sp_send_dbmail

@profile_name = 'dbmail',

@recipients = 'XYZ@abc.com',

@subject = 'T-SQL Query Result',

@body = 'The result is attached',

@execute_query_database = 'msdb',

@query = 'SELECT * FROM msdb.dbo.sysjobs',

@attach_query_result_as_file = 1,

@query_attachment_filename= 'Test.xls'

-- End T-SQL --

@query_attachment_filename -- change the extension of the file name based on your requirement.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Sumata's Solution is on target.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

When I try to open Test.xls as mentioned in Sumata's solution, I get error "The file you are trying to open, 'Test.xls', is in different format than specified by the extension..."

I think just changing the exteion to .xls does not make it an Excel file.

BTW, Excel opens .CSV file without any issue.

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 5

I ran the same and it worked for me.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Suresh B. (8/9/2011)
When I try to open Test.xls as mentioned in Sumata's solution, I get error "The file you are trying to open, 'Test.xls', is in different format than specified by the extension..."

What version of Excel are you using?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

Suresh B.

SSC-Insane

Points: 22986

August 9, 2011 at 10:01 pm

#1366388

Microsoft Office Excel 2007

deepak.a SSCertifiable Points: 5330 More actions · Answer 8

i think SSRS subscription will be better option either standard subscription or data driven subscription based on your requirement.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Suresh B. (8/9/2011)
Microsoft Office Excel 2007

Change the file extension to .xlsx.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JediSQL SSCrazy Points: 2118 More actions · Answer 10

OK,

I was able to make this work with SQL Server 2014 and Excel 2013.

First, here's the sp_send_dbmail call:

exec msdb.dbo.sp_send_dbmail

@profile_name = 'dbmail'

, @recipients = N'XYZ@abc.com'

, @subject = N'T-SQL Query Result'

, @body = N'The result is attached'

, @query = N'select top 10 * from sys.objects;'

, @attach_query_result_as_file = 1

, @query_attachment_filename= 'Test.xls'

, @query_result_width = 32767

;

With extension ".xls", when I try to open the file with Excel 2013, I get this message:

[highlight="#DDDDDD"][font="System"]The file format and extension of 'Test (003).xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?

[Yes] [No] [Help][/font][/highlight]

I click [Yes], and the file opens.

With extension ".xlsx", when I try to open the file with Excel 2013, I get this message:

[highlight="#DDDDDD"][font="System"]Excel cannot open the file 'Test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension maches the format of the file.

[OK][/font][/highlight]

I can only click [OK], and Excel does not open the file.

I added "@query_result_width = 32767", because at first I was getting extra line breaks that were causing each query result row to be split across multiple Excel rows. With "@query_result_width = 32767" each one query result row is one Excel row.

Now I can open the file properly in Excel. Then I select column A and use "DATA > Data Tools > Text to Columns > Fixed Width..." to convert the text lines to real Excel cells. Then I format the datetime columns with Custom: yyyy-mm-dd HH:mm:ss.000 and delete row 2 with the hyphen underlines for the column headers.

Sincerely,
Daniel