«

My first time with CLR in the DB


Well, after a pretty interesting session on CLR integration in SQL Server 2005 I was desparate to actually start writing some code.
We were lucky enough to get the opportunity to visit the Experience Music Project tonight (more on that later), but as soon as I returned to my suite I sat down at the old Tablet and got cracking.

Unfortunately, it took me nearly an hour to get SQL up and running on my new Vista build. I tried running the installation a few times, but kept getting an error:

Shared Memory Provider: Connection was terminated [1236].

I stumbled onto a possible fix, but still no dice. Then I remembered that I haven’t had an opportunity to join our domain (more software problems ;))…I had configured the MSSQL service to run as system, so I tried switching it to run w/ a local account on a lark and things fell swiftly into place.

The next step was creating a database, a sample table, and then a VS.NET database project. Twenty minutes later I had a working user defined function to strip host names out of email addresses. Even better, the debugging worked perfectly and helped me catch a little sleepiness induced bug in my code.

Here’s my managed UDF:

     [Microsoft.SqlServer.Server.SqlFunction]     public static SqlString GetHostNameFromEmail(SqlString rawEmailAddress)     {         string rawEmailAddressAsManagedString = rawEmailAddress.ToString();                  int startIndex = rawEmailAddressAsManagedString.IndexOf('@');         int endIndex = rawEmailAddressAsManagedString.IndexOf('.', startIndex);          string result = null;                  if (startIndex > -1 && endIndex > -1)         {             result = rawEmailAddressAsManagedString.Substring(startIndex + 1, endIndex - startIndex -1);         }          return new SqlString(result);     }  

and the query I used in the test script which triggers the function when I start debugging:

 SELECT dbo.GetHostNameFromEmail(EmailAddress) FROM dbo.Customer 




Sweet. It's going to be hard to go back to work, VS.NET 2K3 and Oracle. It won't be hard going back to my own apartment...next stop NYC! [updated 8/5: I added the code samples.]