1

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.

  • 3
    Don't use SQL Server as a mail service for things such as this. Write a small application or service to deal with this, you'll have a much easier time and it'll scale much better. – Sean Gallardy Aug 31 '17 at 14:41
  • I've thought about this :) But I want to make the same task using only MS SQL Server. This is like a challenge for me. – whitebeast Aug 31 '17 at 14:44
  • Maybe store the text in a table, and then run a PowerShell command to read the data and send the email. – Tony Hinkle Aug 31 '17 at 14:58
  • You could create a CLR object that SQL Server runs inside the Instance. This would satisfy the challenge of running it inside SQL Server as a security feature without unecessarily exposing the system. Besides, sendmail is not without its problems. :/ – clifton_h Aug 31 '17 at 15:05
  • @clifton_h Just an FYI - if you go CLR, security is a breaking change in SQL Server 2017 and beyond: docs.microsoft.com/en-us/sql/database-engine/… – Sean Gallardy Aug 31 '17 at 17:48
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.