Schedule SQLServer2k to clean up Junk Rows

A

aak97

New member
Joined
Jun 10, 2003
Messages
2
Im starting a project of my own, its a simple forum. I know therere tons out there, but I just wanna polish my skills a bit. Anyways, in my forum, when user registers they will have to give a valid email address. To verify this email address, Ill send them an email, and if they click on the link on that email, itll activate their accout. If the user does NOT activate his account in 48hrs. His account will be deleted from the database.

Here is the question: Since I Saved the info the user provided to a dbase in SQL Server 2k before I sent the email for verification, If the user does not activate the account in 48hrs, I need some way to remove that ROW in the database table. How can I do this automaticlly? There is actually 2 tables, one table is the MEMBERS table and it is in relation with another table call Verification (this is where I stores the verification string and the Time(smalldatetime) that account is created). the common key is the username.
If the user activated his account within 48hrs, the entry in the Verification table will be removed by the verification page.

is there a function or a way to write a script in SQL Server 2k to automate this task?
this is my first time working with SQL Server, any good book recommandations (ones with lots of examples with TSQL and some basic functions I can use with SQL Server)?

TIA


aak97:D
 
Insert the date and time of the initial registration in a field and check it when the user goes to verify the registration. If the verification period has passed delete the users records. Placing code like this in a loop, timer or trigger would be fairly inefficient unless the registered:non-activated ratio was extremely high; a preferable solution is to perform this action when another new user goes to register.
 
Cleaning up databases is usually done through an automated task (job). You can read about that here;

http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_4v1v.asp?frame=true

Also look up the commands sp_add_job, sp_add_jobschedule, sp_add_jobstep and sp_add_jobserver.

Usually scheduled tasks are performed once a day when traffic to the web site is fairly low (3am in the morning for example). In this case just add a job that does the following;

DELETE FROM Verification WHERE sign_up_date < DATEADD(d, -2, GETDATE())

.. Im sure you get the idea.
 
Back
Top