October 22, 2008 at 9:19 am
Hi...
I've got a really strange one here.... I'm trying to send an email with an attachment, it works, but for reason there are lines of spaces between each record. If I just run the SP to a file it's fine, as soon as I send it as an attachment it adds 21 lines of 256 spaces between each record.
ANY IDEAS....PLEASE
Here's the code I call from SQL Agent to send the email.
declare @body1 varchar(4000)
set @body1 = 'ERP upload generated by SQL. ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '
SELECT @body1 = @body1 + DWGFileName + ' '
FROM ERP.dbo.tblDWG d
WHERE ( d.Rev = '0' OR d.Rev >= 'M' )
EXEC msdb.dbo.sp_send_dbmail @recipients='SQL@SQL.COM',
@subject = 'ERP Upload',
@body = @body1,
@body_format = 'HTML',
@query = 'EXEC ERP.dbo.usp_GenerateERPFile',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ERP.psv'
October 22, 2008 at 10:04 am
My suspicion would be that the lines are very long and that they are getting wrapped when they are put in the attachment.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 10:07 am
When I put the ouput into an editor and take word wrap off , each line has a CRLF character at the end, so the do look like different lines.
October 22, 2008 at 10:09 am
Try setting the @query_result_width parameter to something like 511 or greater.
Also, definitely set @query_result_no_padding = 1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 10:11 am
OK, wait. Those are contradictory settings... just set the No Padding to 1.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 10:11 am
Thanks for that, I'll go give it a try.
October 22, 2008 at 10:12 am
Apparently, the default behavior is to pad the attachment out to 256 characters. Which would definitely have the results that you see.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 10:18 am
That Worked !!! Thanks a lot for that... didn't see that parameter in the notes I have.
October 22, 2008 at 11:55 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 5:09 am
Just so you know... you were correct the first time, I actually did need to change the default width to 1000, and then set the padding too, otherwise my records (which are variable length) get cut into 256 character chunks.
But it all works now, thanks again...:D
October 23, 2008 at 6:50 am
Glad it worked out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply