[RESOLVED] Sending automated emails

[chris128]

Hi Guys,

Was going to post this in the VB.NET section but I figure its actually more of an SQL question..

Basically I have an app that needs to send reminder emails on dates selected by the user. Obviously if my app was open all the time then this wouldn't be a problem but I want to be able to ensure the emails get sent even if no one has my app open. So the first thing I thought of was just to write a vb.net windows service that sends these emails out but I'd rather not make users of my app have to install services on a server if it can be helped.
I remember that someone mentioned to me a while ago that SQL could do like scheduled task kinda things and I wondered if it would be possible for me to get the SQL database to handle the automated emails entirely (my app is aimed at SQL 2000 or higher).
Anyone got any pointers for where to start and any advice on what the best way of doing this is, if its even possible?

Thanks
Chris

[GaryMazzone]

On SQL2000 you use DTS packages to do this (Mail server needs to be installed). On SQL2005 and above you use SSIS packages.

Both are then scheuled to run via the SQL Agent

[chris128]

oh right cool, can all this be done via .net code?

Also, is there any way of making something that will work for both 2000 and 2005 or do I need to try to programmatically find out what version the DB is and then use different methods based on what version it is?

[GaryMazzone]

Sending email? Or seting this all up?

[chris128]

Well I know how to send an email in vb.net so I guess I just mean can the setup and config be done via code. Basically I dont want to require any manual configuration of the database to be done, I was planning to have a "server side" install that just creates the SQL database and tables etc, so can I set this up during the install too (via .net code)

[GaryMazzone]

The SSIS and DTS packages can be built before hand and included in a build (use a filesystem store not a database store). Build the SQL Agent job on you machine then script it out and include the created scripts in the database build.

[chris128]

Ah I see, sounds good - I'll do a little research into these SSIS and DTS packages then.
One quick question though, how do you actually start to create one of these packages? I'm using SQL Management Studio Express and cant for the life of me find any mention of them...

[GaryMazzone]

They are not available in SQL Server Express. They are also not available in SQL Server Workgroup. In SQL Server Developer and SQL Server Enterprise versions.

[CVMichael]

Just make an application that when executed it checks for e-mails, and if any it sends them...

Make the application with no interface, compile it, then schedule the exe to run every 5-10 minutes using Windows Task Manager.

[chris128]

Nah I've seen enough apps that rely on windows scheduled tasks to work properly and they have been nothing but trouble. I'd rather write a windows service if I was going to have my own app that sends the emails.

So is there definitley no possible way to do this kind of thing using SQL Express then?

[GaryMazzone]

No not avail in SQL Express

[chris128]

Darn, looks like its a windows service then... or force users of my app to have a 'proper' version of SQL..

Thanks for the information anyway