I am creating an easy "service desk" web application with SQL Server as database.
I have a table where the web application is inserting data which is used to send emails for alerting the users about tickets opened, closed, etc. This part is working perfectly fine using sp_send_dbmail and a trigger on insert.
However, there are cases where the email is scheduled to be sent later on (for example preventive maintenance).
I know I can create schedule jobs for this however I would like to know if there is any more effective method rather than creating a scheduled job everytime.
Best Answer
First of all, are you really sure you need to reinvent the wheel? There are open source service desk software such as http://www.otrs.com/software/
Secondly, sending e-mail using database engine is not a good idea. Given that SQL Server box is really expensive, try to avoid spending CPU time on tasks that can be done outside of SQL Server. For example, you can send emails using your application.
But if you really want to stick with your idea, like you said, there is no better way than to create a scheduled job.