November 1, 2011 at 7:31 pm
Oh great fount of all SQL knowledge.
I am a complete noob.
I have a sql statement that exports to either a file or straight to me via email.
However, after my 9th column, it starts a new line even though it is still displaying a column names. (I would say it was a word wrap issue).
I know that may not make any sense.
Let me try it this way:
My SQL query is :
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Dropship',
@body = 'Nightly Dropship',
@subject = 'Nightly Dropship',
@recipients = 'cpb@foo.com',
@execute_query_database = 'DB',
@attach_query_result_as_file = 1,
--@query_result_separator = '',
--@query_attachment_filename = 'dropship.csv',
@query = 'select c.masterorderid, c.createdatetime, a.orderItemId, a.orderId, a.serialNumber, a.filled, d.itemNumber, d.equipName, e.*, f.commentText from orderItems a
inner join orders b on a.orderId = b.orderId
inner join masterOrders c on b.masterOrderId = c.masterOrderId
inner join equipmentTypes d on a.equipTypeId = d.equipmentTypeId
inner join shipAddresses e on b.shipaddress = e.shipaddressId
left join comments f on f.commentOrderId = b.masterorderid'
This runs just fine in SSMS (the query part), but when I run it and ask that it email the data to me, that is where the wrap occurs.
I have tried many different delimiters as well as different types of outputs (CSV/XLS/TXT). They all come out really messy.
I have tried a few different things I have found online to get it working. I have failed on an epic scale.
Please, any ideas would be awesome.
I will buy you a beer if you are in the Everett, WA area. Seriously. (if you can fix my issue)
fireballbrady
November 1, 2011 at 7:43 pm
I wonder if comments.commentText (or less likely, one of the other fields) contains some values with CR/LF sequences...I've run into that before. You could try something like this to replace each of those ASCII characters with a space (or other delimiter of your choice):
REPLACE(REPLACE(f.commentText, CHAR(13), ' '), CHAR(10), ' ')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply