Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can DatabaseMail attach a formatted Excel file Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 9:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 7:40 AM
Points: 5, Visits: 22
my query


USE [AP_ECC]
GO
/****** Object: StoredProcedure [dbo].[ap_ecc_Mail_for_statuslog] Script Date: 02/06/2013 11:03:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ap_ecc_Mail_for_statuslog] as
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'internal@zeroone.com'
,@body='Hi Team,

Please find attached the status log for today.

Please check the status .

Regards,
Reporting team
'
,@subject ='Status Log for Today'
,@profile_name ='Status HDS A'
,@query ='SELECT [TableName]
,[DateLastRun]
,[StartTimeStamp]
,[EndTimeStamp]
,[StatusLastRun]
FROM [ICMRPTSVR].[ICMRPTDB].[dbo].[icmrptdb_statuslog]'

,@attach_query_result_as_file = 1
,@query_attachment_filename ='Status_Log.txt'


my query is attaching txt file i want it as excel type
Post #1416223
Posted Wednesday, February 6, 2013 12:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:42 PM
Points: 1,310, Visits: 1,786
You need a tab delimited file for excel to recognise it and the file extension needs to be .csv (.xls will work but will display an error first).

eg.

DECLARE @query_result_separator CHAR(1) = char(9);

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@test.com'
,@body='Hi Team,

Please find attached the status log for today.

Please check the status .

Regards,
Reporting team
'
,@subject ='Status Log for Today'
--,@profile_name ='Status HDS A'
,@query ='SELECT ''dddddddddd'' as columnheader, ''aaaaa'' as col2, ''ffffff'' as col3
UNION ALL
SELECT ''a'',''b'',''c'''

,@attach_query_result_as_file = 1
,@query_attachment_filename ='Status_Log.csv'
,@query_result_separator= @query_result_separator
,@exclude_query_output = 1

Post #1416273
Posted Wednesday, February 6, 2013 11:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
foxxo (2/6/2013)
You need a tab delimited file for excel to recognise it and the file extension needs to be .csv (.xls will work but will display an error first).

eg.

DECLARE @query_result_separator CHAR(1) = char(9);

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@test.com'
,@body='Hi Team,

Please find attached the status log for today.

Please check the status .

Regards,
Reporting team
'
,@subject ='Status Log for Today'
--,@profile_name ='Status HDS A'
,@query ='SELECT ''dddddddddd'' as columnheader, ''aaaaa'' as col2, ''ffffff'' as col3
UNION ALL
SELECT ''a'',''b'',''c'''

,@attach_query_result_as_file = 1
,@query_attachment_filename ='Status_Log.csv'
,@query_result_separator= @query_result_separator
,@exclude_query_output = 1




Please do not make a tab delimited Comma Separated Value file (what csv stands for). Now the suggestion to create a type of file excel can open and display with out being an xls(x) is a very good one. my personal preference is a tab delimited text file (using .txt) or a pipe delimited file as both tabs and pipes are rarely present in data (unlike commas which in a csv file need special handling).



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1416659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse