April 18, 2012 at 1:25 am
What to do
1. When users sign up. Random a set of confirmation code.
2. Keep their informations and confirmation code in table "temp_members_db". This is temporary table, we have to move this informations to table "registered_members" after email address has been verified.
3. After sucessfully inserted data into table "temp_membes_db", send confirmation link to email that users used to sign up, if email is invalid they will not receive our email.
4. They have to click on confirmation link to activate their account. (move data from table "temp_member_db" to table "registered_members" and delete data from table "temp_members_db" in this step)
NOTE:1. Random confirmation code
2. Insert data and confirmation code into database
3. Send email to user with confirmation link
how to write the code in sql server and it is possible?????????automatically thrugh send email to user????
pls guide to me...................
SQL server DBA
April 18, 2012 at 9:28 pm
Can't really guide you, but I can tell you, you should do some research in Books Online to start.
April 18, 2012 at 10:42 pm
you can only do some of those things with tsql and db mail but if you add in a web programming component you can do them all. i would google setting up DB MAIL and the random function (look to books online.) since its a rather vague question and sounds like homework or an interview question thats about as helpful as i can get right now.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 19, 2012 at 12:38 am
Thanx for Your Reply....
But Maximum iam trying..but is is not Working code..i know Front end is possible but in back end is it possible or not..when ever user registration is complted ..i.e.,how to go confirmation link to the user?? in sql server..i know database mail(gmail and hotmail..etc)..but how to write the code in back end?? we can write the stored procedure or trigger ????and recipiants also(i mean Email adress also) we can send dynamically??
pls guide to me
SQL server DBA
April 19, 2012 at 12:42 am
USE [database]
GO
/****** Object: StoredProcedure [dbo].[spSignupInsert] Script Date: 04/19/2012 12:08:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSignupInsert]
-- Add the parameters for the stored procedure here
@TITLE varchar(8),
@GENDER varchar(6),
@FIRSTNAME varchar(50),
@LASTNAME varchar(50),
@DATE_OF_BIRTH date,
@EMAIL_ADDRESS varchar(50),
@ACCOUNT_TYPE int,
@COUNTRY_OF_RESIDENCE varchar(50),
@USERNAME varchar(50),
@PASSWORD varchar(20),
@status int,
@SECURITY_QUESTION varchar(20),
@SECURITY_ANSWER varchar(30),
@PROMOTIONAL_CODE varchar(10),
@EFFECTIVE_DATE date,
@ID int
AS
BEGIN
Declare @body1 varchar(4000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
INSERT INTO Signup(TITLE,GENDER, FIRSTNAME,LASTNAME,DATE_OF_BIRTH, COUNTRY_OF_RESIDENCE,ACCOUNT_TYPE,COUNTRY_OF_RESIDENCE,USERNAME,PASSWORD,STATUS,SECURITY_QUESTION,SECURITY_ANSWER,PROMOTIONAL_CODE,EFFECTIVE_DATE) VALUES (@TITLE, @GENDER, @FIRSTNAME,@LASTNAME , @DATE_OF_BIRTH, @COUNTRY_OF_RESIDENCE,@ACCOUNT_TYPE,@COUNTRY_OF_RESIDENCE,@USERNAME,@PASSWORD,@STATUS,@SECURITY_QUESTION,@SECURITY_ANSWER,@PROMOTIONAL_CODE,@EFFECTIVE_DATE)
set @body1 = 'Thank you for signing up to receive Forums Alerts. Alerts allow users to receive email notifications for replies and answers to threads they are interested in'
select @ID=@@identity
EXEC [msdb]..[sp_send_dbmail]
@profile_name = 'FROM TEST SERVER',
@recipients = @EMAIL_ADDRESS,
@subject = '[Database] Email Confirmation',
@body = @body1,
@body_format = 'HTML',
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 0,
@query_result_no_padding = 0
END
pls guide to me..in this script lots of modifications are their???
how to send Registration complted once check confrmation link in user point of view?? how??is it possible in sql server?
SQL server DBA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply