September 12, 2013 at 2:05 am
Morning everyone.
I have a query that works ok , and uses msdb.dbo.sp_send_dbmail to send the results.
The results in .csv look terrible.
Can anyone recommend anything to format the results ?
Query results to grid look fine , results to text look terrible.
Does anyone have any recommendations on how to out put in a better format ?
many thanks
September 12, 2013 at 2:43 am
MickyD (9/12/2013)
Morning everyone.I have a query that works ok , and uses msdb.dbo.sp_send_dbmail to send the results.
The results in .csv look terrible.
Can anyone recommend anything to format the results ?
Query results to grid look fine , results to text look terrible.
Does anyone have any recommendations on how to out put in a better format ?
many thanks
further investigation has shown that one offending col is called HTMLBody is a nvarchar(MAX) . This is the column that is not displayed corrected when saved as .csv
September 12, 2013 at 2:59 am
What do you mean with "it looks terrible" and "not displayed correctly"?
What is the problem?
(we cannot see what you see)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 12, 2013 at 3:20 am
Koen Verbeeck (9/12/2013)
What do you mean with "it looks terrible" and "not displayed correctly"?What is the problem?
(we cannot see what you see)
Hi Koen
Thanks for reply. Apologies for vague question. The output when displayed in SSMS to grid looks fine.
When contents saved to .csv content is badly formatted. The issue seems to be that there is a plain text field. This field seems to allow users to write text. This text is then wrapped around when displayed in csv.
example:
In SSMS grid output.
Plain text field: Allows users to entry text and perform carriage returns.
When out put to CSV this wrapps around , and is not formatted (as expected) when results displayed.
PlainText is nvarchar(MAX)
Do you have any suggestions on what I can pass back to developers to allow them to format the output as they expect to see it?
many thanks for your advice.
September 12, 2013 at 3:24 am
Still not 100% sure what the problem is - screenshots would be nice - but I'm getting a hunch.
CSV is a simple plain text file. Since carriage returns are row delimiters in the .csv file, carriage returns in your data can cause problems.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 12, 2013 at 3:40 am
Koen Verbeeck (9/12/2013)
Still not 100% sure what the problem is - screenshots would be nice - but I'm getting a hunch.CSV is a simple plain text file. Since carriage returns are row delimiters in the .csv file, carriage returns in your data can cause problems.
Thanks Koen
Would you like me to PM you and Image ?
I think that is the issue. The text field allows users to do carriage returns to format their text. When output to CSV that causes the issue.
September 12, 2013 at 3:45 am
Sure, you can PM me if you want.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 12, 2013 at 5:03 am
it also depends what application opens your csv file.
Does that interpret carriage return characters, tabs, ...
What are your client settings and what are the separator characters used in the csv ?
In many cases, we use these settings.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SMTP_myapp',
@recipients = @RecipientList,
@body = @MessageBody
, @subject = @MessageSubject,
@query ='select ...
from ...
where ...
order by ...',
@execute_query_database = 'userdb',
@query_result_width = 8000,
@query_result_separator = '',
@attach_query_result_as_file = 1,
@query_attachment_filename = @AttName,
@append_query_error = 1 ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply