sql server 2008 express using a CLR stored procedure - email - couple questions

Question

I just came across this:
http://www.mssqltips.com/tip.asp?tip=1795

which shows how to send email from sql server express using a CLR stored procedure. I'm using sql server 2008 express which does not support SQL Mail or Database Mail, so I'm just finding out.

I'm ok with implementing the CLR stored procedure solution, as I have some .net development experience (a little, but enough). But I had a couple questions first..

1. is this a good option considering I'm using sql 2008 express?

2. what does the 'WITH  PERMISSION_SET  =  UNSAFE' line mean? Will I be degrading security by doing this?

3. in the example the smtp server address gets hardcoded into the .net assembly, can I modify that so that it gets the address from an external file? I have a few servers I need to do this with, and they would all be using a different smtp server. Basically, I'm not sure if accessing the .net code from sql would make that not possible? Or, duh, I just realized I could just add the smtp address as a parameter to the .net function and the stored procedure.

any input would be appreciated.... by the way, is this the right group for this?


Answer 1

This is absolutely the right group for this...

The code in the article that you refer to doesn't have a timeout value (for when you can't connect to the mail server) or error handling. So, you may want to make the sample a little more robust. Database mail, for example, sends a message to a service broker queue, where it's picked up by an exe that actually sends the mail, making it asynchronous with the caller that invokes it. There is another choice here: http://sqldev.net/xp/xpsmtp.htm but it uses an extended stored procedure (XP), and XPs are deprecated in SQL Server (http://msdn.microsoft.com/en-us/library/ms164643.aspx).

Permission set UNSAFE means that the assembly (System.Net?) that hasn't been tested with SQL Server. So it can potentially leak memory or handles, just as unmanaged code (e.g. an extended stored procedure) can. It would have to be at least EXTERNAL_ACCESS, even if all the assemblies were tested, because it communicates outside the SQL Server process.

Configuration information to be used with a database procedure should be stored in a database table, rather than a file (after all, you're in a database). Or you could pass it in a parameter, as you wrote (that's what xpsmtp does).

Hope this helps,
Bob Beauchemin
SQLskills

Answer 2

Thanks for the reply, I appreciate the info.

ok, the CLR stored procedure sounds more attractive to me compared to deprecated xp... but certianly good to know that option exists. You bring up a great point on the synchronous method in that example, and of course the error handling. I would want to address both those issues in my implementation. I am actually more experienced in .net than tsql, so adding error handling and making the .net function multithreaded would not be a problem.. but wait, making the actual .net function multithreaded is not the issue right, since it's called from the sql trigger, it's that trigger that would be making a synchronous/blocking call?

ok,
1. can I make a non-blocking/async call from an sql trigger to the CLR function? In my particular case I don't need to wait around for a result (it would be nice though)... its an email alert that I would rather just not get sent than tie up the sql server.

2. I'm familiar with error handling within .net, but since this is being called from an sql trigger, what do I need to know as far as communicating error conditions? I mean in .net I can throw an exception which can be handled within the function itself or passed up to the caller, but what about this sql trigger scenario? Should I just handle any errors within the .net function and incorporate a success/fail return code that the trigger can process? thats assuming a blocking call will be used from the trigger...

well, I still like the clr approach, but I need to tie up these loose ends to finalize a plan and start coding. What do you think?

Answer 3

Just MHO, but...

It's not a good idea in general to do your own threading in SQLCLR, it has the potential to destabilize the appdomain your component is running in. The way to acheive async operation inside the database is to use the service broker feature; that's what it's there for. And it's available in SQLExpress.

It's also not a good idea in general to do blocking calls inside of a trigger, if you can't send mail, should your database wait? You're making the sending of mail part of the database transaction. Unless there's a good reason to do it, why not just log failures to the SQL log?

I'd have a look at http://sqldev.net/xp/xpsmtp.htm, and see how he's doing it there (although the source isn't included, the recommended usage patterns are).

Hope this helps,
Bob

Answer 4

thanks again for the guidance, I really appreciate it, and I will go check out xpsmtp as well.

so I may wind up ditching this approach then. Basically I need to add email alerts to a system and of course the most logical approach would be to do this from within the part of the system that generates the event that requires the email alert... and this *is* possible but may be a hassle, that's why I thought of doing it 'server-side' via an sql trigger. Client-side for me would mean getting a scriptable, or at least command line, smtp client into a winPE boot image, and control it via wsh/vbscript. I know that is possible, but again, not as straight forward and easy as just having a trigger fire when a table is updated. But, I try to stick to best practices, so if the road I'm going down now is not an acceptable one, I won't do it. I am certianly not an SQL server expert, so I rely on the kindess of the MSDN/Technet community in situations like this, to point me in the right direction, or at least stop me from making a horrible mistake, hehe.

below is the .net code I put together to handle the email, which I have tested via a simple console app test bed. I have not hooked it up to sql yet. Before I completely ditch this approach, let me know what you think about these last few details.

1. the smtp server will always be the local server (same server as sql server is running on). This eliminates any possible network connectivity issues causing the operation to run slow or fail. Also I implemented a timeout on the sending of the email of only 5 seconds, which is plenty since the smtp server is local, and should eliminate chances of the trigger hanging indefinitely on the blocking call.

2. I plan to just make a blocking call, no multithreading, again the smtp server is local so I'm thinking this could be fine. The testbed console app sends the email pretty much instantly and returns...

3. the entire .net method is wrapped in a try - catch structure so any errors would be handled, the method should never hang... and if any errors occur I'm writting to an errors log for troubleshooting.

what do you think? oh, heres the code:

public class SQL_SMTP_Client
    {
        // class used to add the ability to send email from sql server 2008 express, which does
        // not include 'sql mail', or the 'database mail' feature that the non-express version does. This is
        // possible using a 'CLR stored procedure'. 'extended' (XP) stored procedures have been
        // deprecated.

        public SQL_SMTP_Client()
        {

        }
        
        public static bool SendMail(
            string from, 
            string recipients, 
            string subject, 
            string messageBody, 
            bool highPriority, 
            string smtpServer, 
            int port)
        {
            try
            {
                using (MailMessage mailMessage = new MailMessage(from, recipients))
                {
                    mailMessage.Subject = subject;
                    mailMessage.Body = messageBody;
                    mailMessage.IsBodyHtml = true;

                    if (highPriority) mailMessage.Priority = MailPriority.High;
                    
                    SmtpClient smtpClient = new SmtpClient(smtpServer, port);
                    
                    // setting timout value pretty low because the smtp server is the local server
                    // in my case. If this code winds up used elseware, where the server is not local,
                    // this value should be adjusted.
                    smtpClient.Timeout = 5000; // 5 seconds
                    smtpClient.Send(mailMessage);
                }

                return true;
            }
            catch (Exception ex)
            {
                try
                {
                    // log the error info to file
                    using (StreamWriter sr = new StreamWriter(@".\DC_SQLExtensions_ErrorLog.txt", true))
                    {
                        sr.WriteLine("{0:G} ===> {1}", DateTime.Now, ex.Message);
                    }

                }
                catch (Exception ex2) { }

                return false;
            }
        }
    }

Answer 5

If this serves your purposes, that's fine. I like the idea of a timeout, the only thing that worried me was firing it in a trigger. And you could experiment with the timeout value, based on your use case. And that's the way SQL Mail worked until it was replaced by Database Mail in SQL Server 2005.

Cheers,
Bob Beauchemin
SQLskills

Answer 6

thanks again, I appreciate it.

Answer 7

by the way, is it possible to use the service broker queue with your own code? In other words, can I submit my own 'jobs' to the queue so that the call to that job returns immediately, and the task, like sending the email, is queued up to run on another thread?

Answer 8

Sure. When I said "use service broker" I meant "with your own code". The way
Database Mail works is that
1. The sendmail stored proc "packages up" the mail message and writes it to
a queue.
2. An external activation program reads the message from the queue and uses
it to actually send the email.

activation (i.e. through a
stored procedure) or external activation.

The have been people who used broker to build a job queuing system too (see
"A Rational Guide to Service Broker" by Roger Wolter) because SQLExpress
doesn't have SQL Agent jobs built in.

Cheers,
Bob