Friday, March 21, 2008

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.


  1. FYI debugging these got a LOT easier in .net 2k5.

    Run your service, while running do the normal debug->attach to processes.

    There is a checkbox or drop down or something (not at my windows machine) that says view processes by all users. Check it.

    You should see your service exe. Attach to it and boom debug away.

  2. Yeah, I actually do that, but it can be a bit tricky. If the error occurs in your OnStartup you have to make it sleep so that you have time to hook up the debugger.

    And you still have to go through the whole uninstall, rebuild, rebuild, install, set permissions, run cycle... It definitely sucks.

  3. To make my debugging my services easier, I create a solution with two projects - a console app and a service - that share the same classes. The console app links to all the files in the service (add existing, add as link). I use log4net so I can log to console rather than file for the console app. All the business logic runs in a background thread that is started OnStart for the service and in the Main method for the console app. As well as making it easier to debug, this approach gives me two deliverables - one that can be run interactively from the console and another can be run as a service. A basic console app can be just a main method < 10 lines that runs till you press a key.


Note: Only a member of this blog may post a comment.