I use [sp_send_dbmail] to send a query result as an xml attachment. When I first started using sp_send_dbmail, my attachments were small XML files and they were delivered correctly and I was happy with it; those files were like 1MB in size.
However, it wasn't that easy at first too. sp_send_dbmail takes lots of arguments and one of them is query_result_width which is a number between 10 and 32767. When you use sp_send_dbmail to send xml attachments, it removes the CR/LF characters at the end
of xml lines and turn the whole xml attachment into a single line file; then it will cut the line into 32767 character lines and it doesn't care where is it going to cut the line and you'll end up with a corrupt xml attachment which needs to be edited manually
before it gets open in the Microsoft Excel...
I solved that problem by casting the resulting xml into nvarchar(max) and replacing "/>" with "/>CRLF" inside that nvarchar string to avoid those nasty line-breaks.
Now I have another problem. sp_send_dbmail will truncate my xml attachments in exactly 2,097,156 bytes...
I think it's originating from that nvarchar(max) casting. Does anyone know anything of a workaround to this?
This is what I use to send emails:
DECLARE @MailProfile sysname;
DECLARE @Recipients VARCHAR(MAX);
DECLARE @CC V
View Complete Post