Solved

Using SQL Server 2008  or SQL Server 2008 Reporting Services to build an email notification system

Posted on 2010-11-16
5
256 Views
Last Modified: 2012-06-27
I am building a .NET application for a client that wants to setup an simple email notification subscription system. I plan on building an fairly easy input form to enter the user registration information (email address, etc...). Once they are registered they will have access to the content. The notification system needs to remind the user they have access to the content every 3 weeks before there access expires. How can I do this with SQL Server 2008 or SQL Server Reporting Services?  Where I can send the user an HTML formatted email reminder.  Thanks for any help in advance!!
0
Comment
Question by:encoredatas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34152680
>> How can I do this with SQL Server 2008 or SQL Server Reporting Services?

You can use SQL Server 2008 alone to achieve your objective

1. Enable Database Mail in SQL Server 2008 - Make sure that you should have SQL Server edition other than SQL Server 2008 Express.
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
2. Create an SQL Server Agent Job with the below tasks.
a. Add a TSQL Step to check for list of members to send email notifications
b. For all those members, capture their email addresses.
c. Configure sending emails to those users via Database email.
d. Schedule this job to run daily

Once done, the users should be receiving notification emails.
Make sure that all necessary logics are included into TSQL Step.
0
 

Author Comment

by:encoredatas
ID: 34152725
I understand steps c and d , but is there an example of how to accomplish steps a and b. Thanks for all the help!
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34152750
Ok, just a cursor to accomplish the logic in steps a and b and below code can help you out on that.
DECLARE @email_subject nvarchar(1000)
DECLARE @email_body nvarchar(max)
SET @email_body = N'Reminder'
SET @email_body = N'<html><body>Dear {0},<br />Reminder.</body></html>'

DECLARE @email varchar(500);
DECLARE @pbody nvarchar(max)

DECLARE notification_cursor CURSOR FAST_FORWARD FOR
SELECT email, name
FROM ur_table
WHERE isaccess = 1
or last_access_date >= dateadd(ww, -3, getdate())

OPEN notification_cursor
FETCH NEXT FROM notification_cursor INTO @email;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @pbody = 'some_value'

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'some_profile_name',
    @recipients = @email,
    @subject = @email_subject,
    @body = @pbody,
    @body_format = 'HTML'
  
  FETCH NEXT FROM notification_cursor INTO @Id, @email, @name
END

CLOSE notification_cursor
DEALLOCATE notification_cursor

Open in new window

0
 

Author Closing Comment

by:encoredatas
ID: 34152763
Thanks for the quick response.  This is just what I needed!!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34152835
Welcome..
And glad to help you out..
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question