I have created a stored procedure to send an email notification of some data. This is quite convenient to get notifications about database status, reports and any related business needs.
Basically, this is done by using Microsoft SQL 2005's build-in msdb.dbo.sp_send_dbmail. It looks like that some of email related SPs have been faded out since the SQL Reporting Service launched. The reporting services provides much versatile features and allow developers to design more ad-hoc web based reports. However, email notification still has its uniqueness and useful applications.
Since my SP is used for specific notification about data status, therefore, only one parameter is defined, as recipients of emails separated by comma.
The content of the email is HTML text. Depending on the length of text, if it less than the size of email limit(see my previous SQL Server Database Mail setting), then the data are formatted as HTML; otherwise, the file will be saved as a file and it will be attached to the email out.
The following codes set HTML header part:
DECLARE @headerHTML NVARCHAR(MAX);
...
SET @headerHTML =
N'<html>
<head>
<script type=''text/javascript''>
function toggleIt(id) {
var post = document.getElementById(id);
if (post.style.display != ''none'') {
post.style.display = ''none'';
}
else {
post.style.display = '''';
}
}
</script>
</head>';
Here I added some JavaScript to hide and display detail views of data by function
toggleIt(id). For example, the following codes define a title node with a link referenced to the function:DECLARE @htmlTitle1 NVARCHAR(MAX);
...
SET @htmlBefore =
N'<a href=''javascript:void(0)''
onclick=''javascript:toggleIt("1")'';
style=''text-decoration:none'' title=''Expand/collapse this item''>
<H2>[+/-]My Data</H2></a>
<div class=''post-body''
id="1" style="display:none">
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Value</th>
</tr>';
After the link tag(a), the above codes add a table tag with a row of headers defined for the next data retrieved from SQL.
The next blog will continue on the topic on how to use xml type to hold SQL retrieved data.
Posts
0 comments:
Post a Comment