Varbinary and Service Broker, a story

Today I was working on a very simple SQL 2005 Service Broker service. It was to work like this: someone executes a stored procedure passing in some meta data and a varbinary(max) which contains a document. The stored procedure inserts a record into the database with the meta data, then builds a service broker message containing the varbinary and some other required information. A windows service is monitoring the service broker queue for messages. When one arrives it parses it out and creates a file out of the bytes.

This took me about an hour to get through this morning (I already had written a nice service broker library, which is why it was so easy). However, I spent the rest of the day fighting with setup, data types, and encodings.

Problem #1: Windows installer service can't start. Error 193: 0xc1

A very helpful error message which popped up immediately after I tried to start my service. It was clear the OnStart method was never invoked. Apparently this error can be caused by a random "program" file in C:\ on occasion. In my case that wasn't the problem.

Nope, my case was much dumber than that. C# Services, like most programs, require a main method, usually in a program.cs file, to execute. I didn't have one because I started with the visual studio class library template instead of the windows service template... Once I realized what was missing this was easy to fix. But realizing it took longer than you might think.

Problem #2: Invalid XML Document (1, 256)

It turns out that when you define a SqlCommand and setup a bunch of Parameters on it and you tell the parameter that its length is 256, it's actually going to truncate anything you pass into it to 256 characters... In this case, my xml message was longer than that. Duh.

Problem #3: Invalid XML Document (1, 1)

This one is weird and I can not explain it. Say you create an XML variable in SQL and use it as your service broker message like so:

declare @msgX XML

Then in C# you define the message_body parameter as an nvarchar(max). The types are mismatched, but we know XML is just a string, so everything should be just fine, right?

Wrong. I don't know why. But the string you get out will have some random byte as the first byte. If you print it to the Windows Event Log it will look like a little box. This byte causes C#'s XML parser to blow up. If you change the SqlCommand parameter to XML(max) (so the data types match) the byte goes away...

Problem #4: Encoding the varbinary data into XML from SQL

You can't build your xml message just by concatenating strings together if you're dealing with varbinary. You can't cast the varbinary variable to a string either. What you need to do is encode the varbinary. In SQL, the easiest way to do this is:

declare @encoded xml
set @encoded = ( SELECT @varbinary FOR XML, TYPE )

This will automatically encode your binary into base 64 text.

Problem #5: Decoding the varbinary out of the XML in .NET

It took me awhile to find the right way to do this. At first I was trying to use the static Encoding class like

Encoding.Unicode.GetBytes( encodedData );

But this was just giving me the encoded string back.

Finally I found the right way to do it:

byte[] b = Convert.FromBase64String( encodedData );

Moral of the Story or What I learned:
Debugging Windows Services is hard. You have to print messages to the Event Log to figure out what is going on. And every time you make a change you have to: uninstall the service, rebuild the solution, rebuild the setup file, reinstall the service, go to computer management services, set the Run As permissions on the service, start the service, and check the event log to be sure it started correctly. THEN you can do whatever you need to test it.

This takes time. And it sucks.

So what I learned is, take the time to build a test, or refactor the code, or copy and paste the code if you have to. Even though you think, "oh! I just have to do this and it will work" take the time. Cause it's not going to work after you do that. And then you're going to have to do it all again. So just create a nice little testing environment for yourself. Its a one time expense, and then everything will go much faster from there. You'll be much happier, and if you're lucky, you wont have to spend all day debugging something that had basically been done at 11am.