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:
- 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.