views:

24

answers:

0

When you use msdb.dbo.sp_send_dbmail to send a query result as an attachment, you'll have to provide the @query_result_width parameter which is an integer between 10 and 32767.
Now, If you use For XML Path('Row'), Root('Data'), Type in your most-outer Select query, you may get the send_dbmail to send an xml attachment for you which is really easy to open in Microsoft Excel (just 2 or 3 clicks and it's a nice excel table)
But, if your xml file exceeds the 32767 bytes, the send_dbmail WILL insert a "line-break" at 32767th column of the first line of xml file, and it does this UNCONDITIONALLY! It means it doesn't care what's written there; it just breaks the line and therefore WILL corrupt your xml data... I've been wandering the internet for more than 5 hours with no positive result on how should I prevent sp_send_dbmail from injecting that nasty line-break. The only solution I came across was injecting the line-break where I have the control and it's not after 32767th column. A query like the following will do the trick, but it's extra work and not clean...

Select Replace(
    Cast(
        (
            Select
                *
            From Table
            For XML Path('Row'), Root('Data'), Type
        )
    As NVarChar(Max))
, N'/>', N'/><!--This is the controlled line-break. Remove this comment-->
')

Anybody has any better idea? Am I missing something here?
I use SQL Server 2008 Enterprise (x64)