Saturday, November 29, 2008

SQL Send Email (3)

This is the continuing part of my previous two posts:

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

0 comments: