The world's not a perfect place. Never will be. Really though, that's no cause to through ideals to the wind. A recent client request seemed straightforward enough: Create a web service to receive user data gathered from various web widgets, portals and desktop flash modules that a major media company was placing all over the place on behalf of their client, a major national brand. The received data was to be stored in an SQL server database, and based on a client algorithm, emails were to be sent out at specified intervals containing some of the collected user data with the client’s value-added information.
byZero has a developed a pretty slick .Net engine to dynamically create well formatted emails populated with database data. It utilizes XSLT templates to merge the data into HTML email, queues them, then sends them out in a fail-safe manner, that is to say if the email doesn’t send for network connectivity reasons or whatnot, it will send it out again a little later. Works like a charm, never fails. Reliable, data-loaded, pretty emails every time, by the thousands. Of course, given that whole pesky “request-response” internet thing, the email engine must run as either a scheduled executable or a Windows service. So it comes the time to present the application’s technical platform requirements to the client. Well guess what – no executables, no scheduled jobs, and no Windows services. Hmm, why not? Well the answer rendered was twofold – executables expose risk to memory leaks, and scheduled jobs seem to get lost on redeployment. Memory leaks from a simple, single threaded .Net app (a well travelled one, no less)? Bet that guy has a tornado shelter beneath his Manhattan penthouse. And scheduled jobs seem to get “lost” on redeployment? Well I’m no HR guru, but I’d be firing some sys-admins over there. So, begged I, how might we generate the desired emails as they come due? Sql Mail, of course. I wondered if their server runs on steam.
Anyway, here’s why not:
1.) Sql Mail requires extended MAPI, and is finicky with anything but Outlook (not express), or Exchange server. I have a thing against running mail servers on database servers. I have another thing against running client apps on ANY servers. Just crazy I guess.
2.) Plain text email only. Okay to notify the db administrator that the server is on fire, but for a national brand campaign?
3.) The email text body is concatenated with DB values in an SQL stored procedure. Ever hear of separation of church and state?
4.) Execute privileges on xp_sendmail require system administrator privileges to execute. If non system administrator users must access xp_sendmail, the system administrator can create a stored procedure that calls xp_sendmail and provides the needed functionality but does not expose the attachments parameter. This stored procedure must be defined in the master database. The system administrator then grants execute permission on the stored procedure to the necessary users without granting permission to the underlying xp_sendmail procedure. Ask the guy who forgot to port the scheduled services to remember that.
There’s more, but I grow weary, and you probably stopped reading after “never will be”.