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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
CodeCruiserCommented:
One option would be to use a SQL Server trigger on the tables where you record the purchase and the registration information.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
CodeCruiserCommented:
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
CodeCruiserCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.