Extracting a .NET Assembly from SQL Server 2005

I just answered a great question on Stackoverflow: Extracting a .NET Assembly from SQL Server 2005

Yes, this is possible. The actual binary representation of the assemblies live in the SQL catalog for your server. Namely, if you run a join between sys.assembly_files and sys.assemblies you can get to all the information you need. The assemblies binary is in the content column of the sys.assembly_files view.

But in order to extract the binary representation from SQL Server and into a file on disk you will have to write some .NET code that needs to run on the same database where the assemblies you refer to are located now. In Visual Studio start a SQL CLR project and add a class to it with the following code:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;

namespace ExtractSqlAssembly {
    [PermissionSet(SecurityAction.Demand, Unrestricted = true, Name = "FullTrust")]
    public partial class SaveSqlAssembly {

        public static void SaveAssembly(string assemblyName, string path) {
            string sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname";
            using (SqlConnection conn = new SqlConnection("context connection=true")) {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
                    param.Value = assemblyName;

                    cmd.Connection.Open();  // Read in the assembly byte stream
                    SqlDataReader reader = cmd.ExecuteReader();
                    SqlBytes bytes = reader.GetSqlBytes(0);

                    // write the byte stream out to disk
                    FileStream bytestream = new FileStream(path, FileMode.CreateNew);
                    bytestream.Write(bytes.Value, 0, (int)bytes.Length);

Then build the project and deploy it to your database. Make sure that the CLR Enabled configuration option is enabled on the SQL Server. This is probably already enabled, since you have assemblies on it. In case clr execution is not enabled you can run the following code on SSMS to enable it:

sp_configure 'clr enabled', 1


One more thing that you need to be aware of is the by default SQL server may not allow you to write to the disk from the .NET code. If you get a FileIO security error when you run the code above by calling the stored procedure in SSMS, you will need to configure the proper permission set for the assembly. You can do this via SSMS: right-click the new assembly and look at the Permission Set in the Properties dialog. Set it to External Access. Now you should be able to export your assemblies by running the following code in SSMS:

exec SaveAssembly 'AssemblyName', 'f:\path\to\assemblyname.dll'

[delicious | grep links | blogger] for 2010-10-30

[delicious | grep links | blogger] for 2010-10-23

Testing Regular Expressions with Regex Builder

I have been working on a recent project where I need to make heavy use ofregular expressions and I have been spending a lot of time tweaking theseexpressions. Well, the tweaking is a lot easier if you can use a visual tool totest the expressions. There are lots of such tools out there, and I provide ashort list below, but I decided to hack together my own tool just to learn howthese things work under the hood.

This is a very simple tool written in CSharp to visually highlight regularexpression matches. The source is available on github and it's called RegexBuilder (I know... very original name).

Regex Builder

Here's how it works... There are 2 text boxes, one for the regular expressionitself and another for the test string and a couple of check boxes for theoptions. I run the regular expression provided with options against the teststring and I receive back a collection of matches. I also have a Style classthat encapsulates two Colors for background and foreground. I then loopthrought the match collection and I apply the alternating Style to each Matchin the collection. The highlighting is made possible by the properties of the rich text box. This is the core of the code:

string regexPattern = textRegex.Text;
string inputText = rtbTest.Text;
int i = 0;
RegexOptions ro = new RegexOptions();

if (!checkCase.Checked)
    ro = ro | RegexOptions.IgnoreCase;

if (checkMultiLine.Checked)
    ro = ro | RegexOptions.Multiline;

regex = new Regex(regexPattern, ro);
matches = regex.Matches(inputText);

foreach (Match m in matches) {
    Style s = getStyle(i);
    rtbTest.SelectionStart = m.Index;
    rtbTest.SelectionLength = m.Length;
    rtbTest.SelectionColor = s.ForegroundColor;
    rtbTest.SelectionBackColor = s.BackgroundColor;
    rtbTest.Select(m.Index + m.Length, 0);


Regex Builder was a simple mental exercise around regular expressions for meand it's the minimum thing that actually works. I have no plans to develop itfurther. There are other tools that fall in this Regex Tester/Debugger category and are able to accomplish much more than my humble Regex Builder:

[delicious | grep links | blogger] for 2010-10-16

Excel Drives Hexapod Robot

This is a couple of years old, so it's not news, per se... but it's news to me...

The Phoenix is a hexapod robot which uses off-the-shelf electronic components (servos, controller and bluetooth interface). The body of the Phoenix seems tobe custom made but I can't confirm that for sure from the post. The bot talksto the computer via bluetooth and the Visual Sequencer software.

Phoenix Hexapod Bot

The really cool part of this robot to me is that it uses Microsoft Excel to doall the Inverse Kinematics (IK) calculations for the robot's movements. It'sall based on Excel sheets and macros written in VBA. BTW, IK is the process ofdetermining the attributes of a jointed flexible object in order to achieve adesired position, or, in short, it's a type of motion planning.

Excel Workbook Drives Phoenix Hexapod Bot

Here are some links:

Delicious to Blogger Python Script

Readers of this blog (that means my wife and my mom) will notice that there is a new post with a weird title right before this one (in chronological order). What's going on with that?

On a weekly basis I save several links to my Delicious account and I consider that to be a pretty good representation of what is going on in mind at the time. The only problem is that sometimes I link to something and then it gets lost in the black hole that Delicious can sometimes be. There are situations when I saved a link and didn't go back to look for it until a year later.

So I thought that I wanted to group these links together and bring them up front and center in my radar. I just want to have a little better visibility into what I am thinking at the time. This is a task that can be easily automated and I wrote a script in Python to do that for me. You can look at the code here.

Basically, the script looks at the links posted to Delicious since a certain date, downloads the information about those links and creates a blog post for my Blogger account based on the Delicious links.

[delicious | grep links | blogger] for 2010-10-09

On networking and selling your brand as a geek

My manager at work thinks that I do really good work. I think she tells me thetruth since this is reflected in my reviews and bonuses. But she also says thatgood work alone is not enough, and that I have to go sell myself and networkwith my peers to get more visibility to the important projects that I'm workingon. This visibility is important at the company where I work when review timerolls around.

There's nothing wrong with that (and I really mean it... I'm not mocking anyonehere or the process itself, despite the vague reference to Seinfeld). But thereis something about this process that always made me uncomfortable, and althoughI've always had a vague idea about what it was, I could never nail it down. Or,more likely, I never took the time to think it through and analyze it.

Reginald Braithwaite (of Raganwald fame and now working on homoiconic), a chap that I have followed for a few years now, has put it very succinctly and eloquently:

"... I have social anxiety, and while I am stimulated in an incredibly rich and rewarding way when I meet and talk to people I like and admire, when there are more than a few people in the room I feel incredibly uncomfortable and have to fight the urge to slide out.

The irony of using networking in my career when I'm deathly afraid of one ofthe most important methods of networking is not lost on me ..."

To which I respond: Yes! Me too! I could not have said it better myself.

Tidy up your XML files

I have been working with some really large XML files (20 MB) that I have to parse and save the data to a database. The XML data is available to me as a single line of XML (probably to save up space) which is quite unreadable by a human. There are many tools out there that will help you tidy up and reformat your XML to a human-readable format. But writing my own tool using C# and LINQ to XML is so trivial that I decided to do my own. Here's the code:

using System;
using System.IO;
using System.Xml;
using System.Linq;
using System.Xml.Linq;

namespace XmlTidy {
    class Program {
        static void Main(string[] args) {
            try {
            } catch (FileNotFoundException ex) {
                Console.WriteLine("{0}: {1}", ex.Message, ex.FileName);

        static void XmlTidy(string filename) {
            if (File.Exists(filename)) {
                var doc = XDocument.Load(filename);
                doc.Save(filename, SaveOptions.None);
            } else {
                throw new FileNotFoundException("File not found", filename);

What's going on in the code above? When you call the program from the command line, it expects a file name as a parameter, like so: xmltidy samplefile.xml. Next is the call to the XmlTidy function, which checks if the file exists and then loads the file contents into a LINQ to XML document, and then we just save the file again to the same location. That's it! The XDocument class takes care of formating the XML data for you.

You can download the full source, which has some enhancements, from my Github project. This code works on Windows 7 with .NET 4.0 and also on Ubuntu 10.04 with Mono 2.6.7 .