Website initial design help (Ms SQL Server vs mySQL)

[agibby5]

I know that there's a way in MS SQL Server to setup an alert to fire an email, for example, when the value of a column changes. However, can I do this when a certain date passes and a column's value isn't filled in (null or emptystring). For example, at the end of the month, someone should fill in a column with when a certain task was completed. If by the end of the month that doesn't occur, an email should be fired off to them as a reminder.

If this is possible with MS SQL Server natively, is the same possible with mySQL? The reason I'm asking so generally is because I haven't yet began to design this. I haven't decided whether I'm going with ASP and MS SQL Server or php and mySQL...

All help is greatly appreciated, as usual.

 

[Crusty]

Sounds like a cron job is what you're after!

 

[agibby5]

I'm really hoping for something server side. The idea of the application is that if a certain date passes, and a column hasn't been updated by that date, a reminder email is sent to the user. The date would be set by in an admin section of the website and there will be many rows for which these columns would be 'watched'...

EDIT: If the column still isn't updated by another date, then a manager will be emailed, etc...

 

[Crusty]

I would keep that logic out of your database, what happens if you need to update your database schema or even change RDBMS providers? If all of your application/business logic is embedded in your specific database you lose all sorts of portability and flexibility of your application.

Even so, a cron job would be server side. It should execute on the same box as your database, or on a different server that has access to the database.

 

[agibby5]

I guess my concern of the cron job argument is I'd have to be able to create/delete a new job for each email alert that I'd like to fire. This could be great deal of cron jobs. Can I create a cron job from the website I'm looking to design? I was thinking of creating an MS SQL Server alert to be fired on the database to email the user(s). But I wasn't sure how I'd go about creating that alert based on the time criteria... The server I'd use is a local MS Small Business Server. Cron jobs are typically in linux, what's the equivalent to a cron job in MS?

 

[Markbnj]

I'm with Crusty, in that I like to keep my tiers distinct. The database is for storage of state that tells the next layer up when to do things like fire alerts. A cron job or jobs is one way to do it, and there are likely many others. You could write a small scheduler in java using quartz and easily fire off emails or do whatever else you need to do.

 

[imported_Dhaval00]

To begin, I have no experience in MySQL/PHP.

The following explanation is based on my assumption that you're using SQL Server 2005.
I am not sure regarding your knowledge of SQL Server Integration Services, and I don't mean to offend you [if you're a master of SSIS]. Essentially, this sounds like a perfect scenario for creating a SSIS package that will "watch" your tables. The SSIS package will in turn be invoked by a SQL Server Agent job [you would just set the schedule to your liking]. You can email people, generate Excel files, and do all sorts of things in SSIS. Moreover, if you have SQL Server license, you don't have to spend extra - SSIS is included. It is a very powerful tool and you can wrap all your logic in its entirety inside the package.

Just because we're talking design, I guess examples might help: I have packages running every night polling 20-30 different tables, generating 100+ reports, emailing 50+ users, and moving data between databases - all within a matter of minutes. So yea, if I was doing this, I would simply use SSIS. Nonetheless, if you don't know SSIS, then this will be an issue. I don't think firing "events" whenever a value changes is a smart, feasible, and scalable design. You'll probably have serious issues going forward. I agree with what Crusty said - don't trickle your logic all the way to the DB; try and keep it outside.

If you can't use SSIS, I would say just write a Console applicantion in .NET that will poll your DB for changed values. You can tie the .exe to a scheduled task and let it run periodically?

 

[Crusty]

On Windows you could use a scheduled task or like Markbnk suggested writing a small application to do it for you. You could install it as a service so you don't have to be bothered to open it up all the time.