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 ««123»»

Can DatabaseMail attach a formatted Excel file? Expand / Collapse
Author
Message
Posted Tuesday, December 1, 2009 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 8:09 AM
Points: 28, Visits: 35
Hi

As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'cv@xx.co.uk',
@body= 'Please find attached',
@subject = 'Test Email from SQL Server',
@profile_name ='DBMailProfile' ,
@query = 'exec uat.dbo.csp_Report 1,1',
@attach_query_result_as_file = 1 ,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_header =0,
@query_attachment_filename = 'report.csv';

Im quite stuck here as all query data is coming as one row without column separation

Thanks
Post #826799
Posted Wednesday, December 2, 2009 10:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
charu.verma (12/1/2009)
Hi

As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'cv@xx.co.uk',
@body= 'Please find attached',
@subject = 'Test Email from SQL Server',
@profile_name ='DBMailProfile' ,
@query = 'exec uat.dbo.csp_Report 1,1',
@attach_query_result_as_file = 1 ,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_header =0,
@query_attachment_filename = 'report.csv';

Im quite stuck here as all query data is coming as one row without column separation

Thanks


Did you make sure that there is a tab space between the quotes?

When I do it, there is a marked space like ' '
Post #827688
Posted Thursday, December 3, 2009 5:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 8:09 AM
Points: 28, Visits: 35
Its a 100% tab & not a spcae between quotes..still all data is coming in one column separated by a space,whereas I want each column of the result query to come in a separate column of the excel
Post #828063
Posted Friday, December 4, 2009 11:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:29 PM
Points: 127, Visits: 860
Maybe this will work?
@query_result_separator = char(9)


"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
Post #829124
Posted Wednesday, June 16, 2010 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 1:49 PM
Points: 1, Visits: 3
Thanks a lot, TAB resolved my issue!
Post #938533
Posted Wednesday, January 5, 2011 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 4:32 PM
Points: 16, Visits: 158
Hello,

I have followed the advise on the post for the following and have place a 'tab' in the query_result_seperator and the csv file is still opening incorrectly.

I would just like to verify that I place a ' then hit the TAB key and close with '
Any help would be appreciate. [I am running EXCEL version 2007 , SQL Server 2008]
Code:
exec msdb.dbo.sp_send_dbmail @recipients = '@.org;@.net;',
@body = 'Here are the visits :',
@query = 'exec usp_nh_PharamacyInterventionExport',
@query_attachment_filename = 'results.csv',
@query_result_separator = ' ',
@subject = 'Payors Changed Yesterday',
@query_result_header = 1,
@attach_query_result_as_file = 1,
@execute_query_database = 'it'

thank you

Post #1043093
Posted Wednesday, January 5, 2011 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 10,381, Visits: 13,436
Try using CHAR(9) instead of using a TAB from the Keyboard, like Doug suggested a few posts earlier.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1043095
Posted Wednesday, January 5, 2011 8:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 4:32 PM
Points: 16, Visits: 158
Tahnk you Jack that worked.
Post #1043102
Posted Thursday, March 3, 2011 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
just stumbled across this thread. worked a treat thanks.
Post #1072635
Posted Friday, March 4, 2011 6:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 1,522, Visits: 2,731
I had a similar problem exporting a CSV file from SSRS, where Excel would open the file as a single column. Turned out that the default SSRS export encoding for CSV was unicode, and Excel did not know how to handle that. I found the "Render" section in rsreportserver.config where you can override the encoding - set it to ASCII and that fixed my problem.

Not exactly the same as the OP's issue but hopefully this will help somebody.
Post #1073636
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse