How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way to
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||"LWhite" wrote:

> Hello,
> We are currently using an inventory control system that is a little lacking.
> It allows us to store expiration dates for our materials but lacks any way to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not then
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmine
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate())) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate())) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end