I am fairly new to SQL Server and I have a question concering email alerts. I have almost 3,000 email addresses that I need the ability to send email alerts to (these are all USERS of the db). Basically the db tracks hours worked each week by employee and if the data is not populated, I need the ability to send those users email reminding them to enter their hours. Is there an Operators table that I can modify or do I have to individually input EACH & EVERY one of these 3,000 email addresses????
I would not use the operators table for this. We do something similar for our users (alerts based on inventory min/max). But I created a custom script to manage it. If you would like some more details, I'd be happy to send you some examples...
But save the operators table for you and your other SQL admins.
Ok, I've showed the script to a co-worker with a little more experience than I and he has some questions. First, he wanted to know where we would run this script? Also, he wants to know if this script works for 6.5?
Any other feedback that you have would be greatly appreciated! Thanks!
We run it from inside a DTS package. But you can run it from just about anywhere. I think probably the easiest would be to save it as a .vbs file on any NT machine (NT4 sp5 or greater, 2000, XP or 2003). I think that you can schedule vbs scripts using the job scheduler in NT.
You need to be sure to register the vbSendMail DLL.
I don't see why it would not work with SQL Server 6.5. It's only going to pull a recordset from the server; for that you only need to connect (preferably via ADO). I don't think you can use a trusted connection with SQL Server 6.5, so you will have to modify the connection string slightly.
Remember, most of the work in the script is done outside of SQL Server; it's not really a SQL Server solution.