How to send big email via procedure sp_send_dbmail?

Question

I need to send emails via MS SQL server. But the problem is that I have too much information to send. Let's say 50-200Kb per one mail. This is HTML formatted reports.

For sending, I run sp_send_dbmail with the following parameters:

EXEC msdb.dbo.sp_send_dbmail    
        @profile_name = 'profile',
        @recipients = 'mail@server.com',
        @subject = 'subject',
        @body = 'body',
        @body_format = 'HTML', -- or TEXT
        @query = 'select * from ##t',
        @attach_query_result_as_file = 1,
        @query_attachment_filename = '1.html';

I've used the following options:

1. Prepared HTML in @body NVARCHAR(MAX) variable. But it truncated to 8000 symbols.

2. Prepared HTML inside ##t:

   CREATE TABLE ##t (ID INT IDENTITY(1,1), email NVARCHAR(MAX))
   

   and then used @query = 'select * from ##t' in sp_send_dbmail procedure.

   I can send a lot of rows, but all of them will be truncated to 256 symbols.

   Even If I use @query_result_width = 32767 (what is the maximum limit) - I receive truncated columns.

   I wish I could get round this "256-symbols-on-column" limitation. This will be the simplest method of sending emails for me.

3. Prepared HTML inside ##t and used SQL Job with CMD command inside:

   bcp "SELECT html FROM ##t ORDER BY Id" queryout c:\temp\report.html -c -T
   

   This is working approach, but not so simple as I want because now I should create some folder and bother about cleaning files inside it.

4. I can use PowerShell script which will be called by Windows Task Scheduler This is not so simple too.

What can you suggest in this situation? How can I send emails with minimum effort of development and security rights changing? I need a simple solution which could be installed on any Windows server.

Thank you in advance.

Answer 1

I would recommend dynamically building the body of the mail message itself using a query, instead of attaching the query results to the mail using sp_send_dbmail.

For example, something like:

DECLARE @BodyHtml nvarchar(max);

SET @BodyHtml = N'';
SELECT @BodyHtml = @BodyHtml + CONVERT(nvarchar(max), html) FROM ##t;

EXEC msdb.dbo.sp_send_dbmail    
    @profile_name = 'profile',
    @recipients = 'mail@server.com',
    @subject = 'subject',
    @body = @BodyHtml,
    @body_format = 'HTML';

This should send the entire HTML inside the e-mail body itself instead of as an attachment.

The @body parameter is defined as nvarchar(max), so it's likely that you're getting implicit conversions to varchar(8000) when you're building your html body, and that's why it was truncated at 8000 characters (unless, maybe, you're using a very old version of SQL?). This is why the CONVERT function is important while you're building your html body.