Pulling data from a SQL table

Question

I have an Intranet, that is SQL based on the back end.

Users submit documents to the Intranet, but sometimes they set an expiry on the document.

There is a table "dbo.article" that lists all these documents, who submitted them and when (if ever) they expire. I get get this data by doing:

SELECT TOP 999 [NEWSID]
  ,[EXPIRES]
  ,[HEADLINE]
  ,[AUTHORID]
  ,[AUTHOR]
   FROM [ARTICLE]
   ORDER BY [EXPIRES] ASC

I would like this to run, say once a week, and then email me when an article is about to expire.

What would be the best way to achieve this?

Gavin.

Answer 1

a couple of ways to achieve this,

option 1 - write a stored procedure checking the expiry and sending notification emails if necessary, there are ways to send emails from sql server, for example, this link - How to send email from SQL Server?

option 2 - use some script (vbs or powershell, again, either use stored procedure or plain sql query) and when the condition is met, fire an email, and this is more flexible and could be installed at any client machine (not necessary on the SQL server). one sample on how to send email using vbs - http://social.technet.microsoft.com/Forums/en-US/7779b3bb-dfcc-4ab3-966d-9c71d5369ad7/send-email-using-vbscript

option 3 - implement a simple console app or even win form app to do stuff in option 2, instead of using script.

but all the options require you to add a scheduled task on the installed box to run it at your desired timeframe.