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 = 'firstname.lastname@example.org', @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:
- Prepared HTML in @body NVARCHAR(MAX) variable. But it truncated to 8000 symbols.
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.
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.
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.