Send email as part of an automated script

Posted on 2007-11-14
Last Modified: 2012-06-21
Hi All

I have a asp website database running on SQL 2005, unforetunately it forces an admin to approve a user once said user has registered. What happens is that they register and get an email stating that they need to wait for their registration email. When they are approved they get another email stating that all is ok. I have no control over this process or the emails so I need to work around it.

What I can do is execute 2 update scripts as often as possible using the agent to approve people, what I can't do at the moment is generate an email to them stating that they are now approved.

Any ideas on how I acheive this? I'm guessing some sort of trigger instead of using the agent but honestly I have not got a clue.

Thanks in advance.



The update scripts I have are as follows:

UPDATE    aspnet_Membership
SET              IsApproved = 1
FROM         UserProfile INNER JOIN
                      aspnet_Membership ON UserProfile.UserID = aspnet_Membership.UserId
WHERE     (UserProfile.IsBTSStaff = 0) AND (UserProfile.IsApproved = 0)

UPDATE    UserProfile
SET              IsApproved = 1
WHERE     (IsBTSStaff = 0) AND (IsApproved = 0)
Question by:Durbanpoison
  • 2
LVL 18

Accepted Solution

Yveau earned 500 total points
Comment Utility
You could try using the sp_send_dbmail procedure ... doing that from the trigger could be your solution ... or do the update based on a new user and do both updates and the sp_send_dbmail in one transaction ...

however you have to configure the SQL Server to use it ... I don't know if you have that opportunity ...

Hope this helps ...

Author Comment

Comment Utility
That is exactly what I did, cheers anyway.

I'm quite happy actually, I just wrote my first trigger!




-- =============================================

-- Author:		<Kerry Reid, BTS>

-- Create date: <14/11/2007>

-- Description:	<Automatic Approval Trigger>

-- =============================================

ALTER TRIGGER [dbo].[AutoApprove]

   ON  [dbo].[UserProfile]




	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.


    -- Insert statements for trigger here

DECLARE @Email AS nvarchar(300)

DECLARE @UserID AS uniqueidentifier

DECLARE @EmailBody AS nvarchar(MAX)

DECLARE @FirstName AS nvarchar(100)

DECLARE @LastName AS nvarchar(100)

SELECT @UserID = UserID, @FirstName = FirstName, @LastName = LastName from Inserted

SET @Email = (SELECT Email FROM dbo.aspnet_Membership WHERE dbo.aspnet_Membership.UserID = @UserID)

SET @EmailBody = 

		N'Dear ' + @FirstName + ' ' + @LastName +

		N'<p>Your registration at the BTS Data Collection Web Site has been approved. You can now login and access the site.</p>' +

		N'<p>If you forget your password you can retrieve it by clicking on the "Forgotten Password?" link on the Login page and following the instructions.</p>' +

		N'<p>Regards,</p>' +

		N'<p>The British Thoracic Society Data Collection Team</p>'

UPDATE dbo.UserProfile

SET IsApproved = 1

WHERE dbo.UserProfile.UserID = @UserID

UPDATE dbo.aspnet_Membership

SET IsApproved = 1

WHERE dbo.aspnet_Membership.UserId = @UserID

EXEC msdb.dbo.sp_send_dbmail

		@profile_name = '107777-APP1',

		@recipients = @Email,

		@subject = 'BTS Data Collection web site - Registration Approved',

		@body_format = 'HTML',

		@body = @EmailBody


Open in new window

LVL 18

Expert Comment

Comment Utility
Glad I could be of any help !

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now