Chomping At The Bits

Getting Started With SQL CLR Part 2

As you start playing with SQL CLR, you learn pretty quick that the built in namespaces can be a little handicapping. You can skip this by creating an ‘unsafe’ assembly in your database. In the following demo, we’re going to load a 3rd party .library for XMPP messaging into our SQL Server instance, and use a stored procedure to send XMPP messages.

The XMPP library I’ll be using is agsXMPP. As you’ll see in my code below, I tend to just use a common shared library folder so I can house various libraries and it makes it easier from an organizational standpoint to cram them into the database. I use the following code to ‘cram’ the library into my instance so I’ll be able to use it in a CLR procedure:

FROM 'C:SubversionShared LibsagsxmppagsXMPP.dll'

Once that is out of the way, we can roll our CLR proc, deploy and message to our hearts content (or until the network drops)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using agsXMPP;
using agsXMPP.protocol.client;
// yes,  domains and names have been changed to protect the innocent servers.
public partial class StoredProcedures
    public static void clr_SendXMPP()
        XmppClientConnection xmpp;
        xmpp = new XmppClientConnection();
        xmpp.AutoPresence = true;

        xmpp.AutoResolveConnectServer = true;
        xmpp.Port = 5222;
        xmpp.UseSSL = false;
        xmpp.Server = "";
        xmpp.Username = "SQLAlert";
        xmpp.Password = "SqlClr";


        xmpp.OnLogin += delegate(object o) { xmpp.Send(new Message(new Jid(""),, "This really should be a variable yes?")); };