Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Send email as part of an automated script

Posted on 2007-11-14
3
Medium Priority
?
283 Views
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.

Regards

Durban

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)
0
Comment
Question by:Durbanpoison
[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
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 1500 total points
ID: 20280840
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 ...

http://msdn2.microsoft.com/en-us/library/ms190307.aspx

Hope this helps ...
0
 

Author Comment

by:Durbanpoison
ID: 20287769
That is exactly what I did, cheers anyway.

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


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Kerry Reid, BTS>
-- Create date: <14/11/2007>
-- Description:	<Automatic Approval Trigger>
-- =============================================
ALTER TRIGGER [dbo].[AutoApprove]
   ON  [dbo].[UserProfile]
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- 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
 
END

Open in new window

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20288271
Glad I could be of any help !
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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