Send Automatic Emails from SQL Server 2000 dynamically

In my web application, after the user makes a purchase, he has to register with us within next 30 days. So I need to send him an email every week for 30 days, till he registers. After he registers, I need to stop sending the email.

I have the VB script file to run the code which would send the email (attached)
I know I can create a job and set a recurring time in Management -> SQL Server Agent ->Jobs.
But how do I set the time dynamically, as and when a user makes a purchase? Then I need to stop the emails once the user registers. Can you please take me in the right direction?

Dim objRequest
	Dim URL
	
	SET objRequest= CreateObject("Microsoft.XMLHTTP")
	URL = "http://server/project/EmailSchld.aspx"
	objRequest.Open "POST", URL, False
	objRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
	objRequest.Send		
	Set ObjRequest = Nothing

Open in new window

Angel02Asked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Can you not write a Windows Service? Or an exe which is run by the Windows Schedular? Number of emails sent is not depending on whether its run by Windows Schedular or SQL server. That would depend on other factors such as your code.
0
 
CodeCruiserCommented:
Is it for ASP.NET? You can use Quartz.NET open source scheduling system

http://quartznet.sourceforge.net/

http://stackoverflow.com/questions/1356789/quartz-net-with-asp-net
0
 
Angel02Author Commented:
Thanks for the link. I went through it

(1) I am using .NET 1.1, so I downloaded Quartz.NET-1.0.3. But  \Quartz.NET-1.0.3\server\bin has only 2.0 and 3.5 folders. I anyway copied the  Quartz.Server.Service.exe from \Quartz.NET-1.0.3\server\bin\2.0\service.

When I ran installutil Quartz.Server.Service.exe, it says the format of file is invalid.

(2) How is Quartz.NET different from the SQL job scheduling? If it is the same I can go with SQL ?

(3) I don't yet have a clear picture of how it works. I don't want to schedule the job at one recurring time like 'every Monday at 4 pm'. I have to schedule the job in accordance with the purchase made and I need to be ale to stop the job. Where should I mention all this logic. That is logic to stop the job if the purchaser has registered. Where should this trigger be placed?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
CodeCruiserCommented:
One option would be to use a SQL Server trigger on the tables where you record the purchase and the registration information.
0
 
Angel02Author Commented:
If a trigger is created on a table, won't it be fired only where some specified data-modification takes place  in the table?
I am not sure if it would work for me in that case.

I have a table with status "Not registered", "incomplete" and "registered". I thought of checking that table like every Monday at 10 am and send emails to all the users who don't have "registered" status. Does that sound too cumbersome or inappropriate?

Please let me know if there are other options.
0
 
CodeCruiserConnect With a Mentor Commented:
Trigger could be used to add/remove the user from the list of people who would receive the email. You can use the trigger to add user to that list when a purchase is made and remove user when status updated to Registered.
0
 
Angel02Author Commented:
Will this trigger also send emails to all the users in that list? Where will I be mentioning the time or event when the emails should be sent? Can you please direct to a useful link to learn more about triggers?

Also please let me know your thoughts on what I said in my previous post.
"I have a table with status 'Not registered', 'incomplete' and 'registered'. I thought of checking that table like every Monday at 10 am and send emails to all the users who don't have 'registered' status."
0
 
CodeCruiserCommented:
>I thought of checking that table like every Monday at 10 am and send emails to all the users who don't have 'registered' status."

That's what I was referring to when I mentioned trigger. Your email sending code runs at a fixed schedule and you just control the list of users that the email is sent to depending on their status. You can even use the code that adds the purchase order and that registers the user to get this functionality without using triggers.
0
 
Angel02Author Commented:
Thanks. Sounds good.

I have written all the code to obtain the email addresses and my SendEmail() Function in my webform EmailSchld.aspx.vb. I have a .vbs file which calls this webform. (I had attached it in my first post).

Now, I can't figure out how to call this .vbs from SQL Server agent. I am  creating a job, but when it asks for "Steps", what command do I mention there. I can't find xp_cmdshell in my Master database either.
0
 
CodeCruiserCommented:
Are you really using SQL 2000? See if this helps

http://msdn.microsoft.com/en-us/magazine/cc301942.aspx
0
 
Angel02Author Commented:
Oops! After looking at your link, I realized something.
My web application is published on the Exchange Server. All the project files are thus copied on the Exchange server. The database is on a different server. The database server does not have the project files locally. So, I don't have the file path to .vbs, hence .aspx from the Database server. (I dont know if I can access the file on the exchange server from the database server and schedule a job. )
Two other options are:

(1) I will have to run the trigger within Exchange server to access the .vbs, which would run the .aspx file.
Can I use windows scheduler? How does that work for large number of emails?

(or)

(2) I will have to forget about the .vbs and .aspx and write a stored procedure which would obtain the valid email addresses and send email. I will then have to create a SQL job to execute that stored procedure.

#1 sounds easier to me, but is #2 more efficient? Please advise.
0
 
Angel02Author Commented:
So I created a .vbs which would be run by the windows scheduler. Looks good and works fine. I think I'll go with that. Thanks so much for the help. Merry Christmas!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.