SQL Send Email (3)

This is the continuing part of my previous two posts:

• SQL Send Email (2), and
• SQL Send Email(1)

As I mentioned in the previous post, what I need is to save the content to an html file. The main reasona are: I need to get the size of the html content, and I have optiont to send the html content by attachment file. I could do the first one easily by calculating the length of variables which hold html strings and xml content. However, when I come to the point how to send the html content, I have to decide to sent it in the body of email or by a file attachment.

If the file size is too big, more than 10mb in some cases, it will cause mail application (outlook) hanging for a long time when the email is opened. In this case, it is better to send html as an attachment file. In addition to that, I could save the html file to a reporting server site so that a light notifiation email with a link to the html file could be sent.

I have posted several blogs on SQL Server Porjects. It is very easy to create a .Net project to save string to a file. Therefore, I created a SQL procedure .Net project to do the job. The store procedure is very simple:

public partial class StoreProcedures
{
 [Microsoft.SqlServer.Server.SqlProdure]
 public static void SaveToFile(
   string content,
   int overwrite0OrAppend1,
   string fileName)
 {
   ...
 }
 ...
}

In the same way, I have created another .Net based SP to get file size. With these SPs,
here are some SQLs to save content vars to a file:

EXEC SaveToFile @htmlBefore, 0, @fileName;
EXEC SaveToFile @xml, 1, @fileName;

Finally, I use the following SQLs to send the HTML content by email:

IF @fileSize < @fileSizeLimit
BEGIN
  EXEC msdb.dbo.sp_send_dbmail -- SQL SP to send email
    @receipients = @p_recipents, -- recipients passed by parameter
    @subject = @v_subject,
    @body = @v_content,
    @body_format = 'HTML'; -- set content as HTML
END
ELSE
BEGIN
  EXEC msdb.dbo.sp_send_dbmail -- SQL SP to send email
    @receipients = @p_recipents, -- recipients passed by parameter
    @subject = @v_subject,
    @file_attachments = @fileName, -- set attachment file
    @body = @v_msg,
    @body_format = 'HTML'; -- set content as HTML
END