1

I need to read some of your opinions. I'm developing a desktop app (.net/winform) to keep data in one place. This is used by several clients and the data is stored in MS SQL Server (client-server arch.)

One of the feature is to be able to send notifications via email when conditions met. I've never did something like this so I can't figure out the best way to implement this.

Mails are sent on a daily basis. They don't have attachments nor heavy content. Just plain text.

  • Send mails directly from SQL? Stored Procedure?

  • Make a separate app to handle this? (Running on serverside).

  • Use a schedule task? (Windows Server)

  • Use a 3rd party app?

Hope anyone know what is the best way to do this.

Thanks for you time & answers.

Improve this question
2
  • So just to be clear: The application runs on the client side, the server is only running the SQL Server? Commented Apr 24, 2014 at 1:28
  • @KGChristensen Yes. Multiple clients connect to the same Server running SQL Server. Thanks! Commented Apr 24, 2014 at 11:57

2 Answers 2

Reset to default
2

Do you not have a server-side component running now?

I would avoid having the SQL Server send the Email itself.

If you just need code somewhere to wake up occasionally and check for specific conditions and take action, you could write a simple console app and use the Windows task scheduler on the server to run it every so often. If the conditions are right, send your Email messages, otherwise, just exit clean. Check again the next time the task scheduler fires up your console app.

Or you could write a Windows service to essentially do the same thing, except that you would use the task API or Windows timers to occasionally wake up and check for appropriate conditions. The service will be a bit trickier than scheduling a simple console app. There are service installation issues. You can't have your worker code blocking the primary thread where you're listening for important things like service control messages. Your worker code needs to be responsive to these events, as well. So if you get a shutdown message, you need to respond to it in a reasonable amount of time (a second or two, not a minute or two). You probably want to consider some kind of a queue-based design that can send half the messages, get shut down, wake back up and send the other half without losing it's place, and so on.

If you're not savvy about issues like service installation and the thread synchronization and service control events, I'd just do the simple console app and use the Windows Task Scheduler. "Low tech" can actually be kind of a beautiful thing.

But to reiterate, I definitely would not do this directly from SQL Server. Just because you can (.NET extended stored procedures) doesn't mean you should. :-)

Improve this answer
6
  • Right. My best option was to make a console app like you said. As is this my first time doing something like this, a console application sounds weird to me at first but thanks to your comments it isn't. Is Console App + Windows Schedule realiable? I appreciate your answer. Commented Apr 24, 2014 at 12:02
  • I think it's totally reasonable. It's a part of Windows on purpose. There is the schtasks.exe utility that you could launch from an install program, or there is a COM API you could use for creating the tasks. Microsoft themselves use it for a launching a number of housecleaning tasks. Several third parties do, as well. In fact, from the standpoint of using system resources efficiently, it might actually be better than a service since your program won't be running and using system resources full time when it isn't necessary. Commented Apr 24, 2014 at 13:07
  • You can also, of course, create your scheduled task directly in the Windows GUI, if it doesn't need to be part of an install program. Commented Apr 24, 2014 at 13:30
  • so a console app using windows scheduler is the best way to this. If it couldn't send those mail, it should be logged (to blame someone else :P) but I can do that writing it back to the database. Many thanks, I'm accepting this answer. Have a nice day! :) Commented Apr 24, 2014 at 13:38
  • Well... :-) You'd log the success and/or failures just so you have documentation of what happened. In the case of the failures, if the Emails are important, you could try to send them again by processing your "failed" log. The 2nd or 3rd time they fail, you could escalate them for closer scrutiny. Maybe you have a misspelled Email domain in one of your records and you could easily fix it. Etc. But you can't really know those things if you don't at least log the failures. Commented Apr 24, 2014 at 13:47
1

You can send email directly from SQL Server, providing you have configured it for sending email. Based on your stated requirements, this may be the easiest method to use. It would also mean less development effort as you wouldn't need to create an application or service to do something that is already built into SQL Server.

If you have several processes that need to send email with some number of processes that are outside of SQL Server, then it might be in your best interest to create/buy an application/service that sends email. If you already have IIS set up on the server machine, you can use it to send your emails.

As for the scheduled task or 3rd party app, I wouldn't do that with what is already available.

Improve this answer
2
  • Of course you can send Email directly from SQL Server. Still doesn't mean you should. I presumed you would be sending it via IIS or Exchange, 6 of one, half a dozen of the other. It's just an SMTP server. But how are you going to trigger the Email job? It should only run under certain conditions, right? Are you going to have every one of your client app instances send the Emails, or designate just one of them to do the job? I was under the impression that you were shooting for more of a batch process than the occasional one-off Email, based on "Mails are sent on a daily basis." Good luck! Commented Apr 24, 2014 at 13:12
  • 1
    That sp_send_dbmail documentation doesn't say anything at all about whether the Email message is sent synchronously or asynchronously, for example. So if it's sent synchronously, then you have an expensive SQL Server connection tied up on a blocking call waiting for your SMTP server (IIS, Exchange, whatever) to successfully fire off that Email. That could be fast, slow, or fail. How do you deal with failed sends? Do you log it? Do you then roll your log back and try them again? Maybe the SMTP server was down temporarily. Maybe lots of things. I would NOT do this from SQL Server, FWIW. :-) Commented Apr 24, 2014 at 13:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.