By Bill Wunder

Hopefully you've had a chance to try out the latest release of my utility that creates and maintains a complete history of DDL changes on a SQL Server in a SourceSafe repository. You can read about it in the article Bill Wunder’s DDL Archive Utility meets VB.Net. I like to use the Utility as a learning tool and in turn as a medium to convey what I've learned through articles like this one. If you download and install the Archive Utility and follow along with the steps outlined in this article, you will gain some valuable experience in the maintenance of .NET applications. Something tells me this is going to be useful knowledge for every SQL Server DBA.

The DDL Archive Utility is somewhat atypical of .NET applications in that is has a number of external dependencies. A more typical .NET application can be built to be deployed to any host simply by copying files created by the compiler to that host. The DDL Archive Utility requires that a SQL Server 2000 Client is first installed on a host machine because it relies upon the SQLDMO.dll, SQLNS.dll, DTS.dll and the DTSCustTasks.dll libraries shipped with SQL Server and similarly requires that that the Visual SourceSafe client is installed on that host machine because the Utility relies on the SSAPI.dll shipped with Visual SourceSafe. These are all COM libraries so the the Utility uses CLR wrappers called Interop assemblies to use these COM based libraries. Assemblies are nothing more that cogent CLR building blocks. And according to the MSDN Library for Visual Studio 2003,

Interop assemblies are .NET assemblies that act as a bridge between managed and unmanaged code, mapping COM object members to equivalent .NET managed members. Interop assemblies created by Visual Basic .NET handle many of the details of working with COM objects, such as interoperability marshaling.

Visual Studio does the work of creating these Interop assemblies and it seems that as long as the dll referenced in the Interop assembly can be found the application is happy. The flip side is that I expect that the dll hell problems will find their way into the depths of Interop assemblies. Interop assemblies exist to aide and ease the transition from COM component models to CLR assembly models and should probably not be thought of as permanent components of a long term solutions. SQL Server is obviously moving to assemblies with Yukon. Hopefully we will also see a .NET replacement for the SourceSafe API in the near future as well. Still, It's going to be months or more likely years before most of us can say good bye to Interop assemblies.

Another prerequisite for the DLL Archive Utility is SQLXML 3.0. SQLXML is a full fledged .NET assembly. SQLXML 3.0 is placed in the Global Assembly Cache (GAC) when it is installed. If you are not familiar with the Global Assembly Cache you should be. A good place to start is with Jeremiah Talkar's excellent article, "Demystifying the .NET Global Assembly Cache". In a nutshell if an assembly is in the GAC then that the assembly is a shared assembly available for use by any application. It also means that if you upgrade SQLXML the DDL Archive Utility, and possibly other applications if you've written others that use SQLXML - will stop working. The current version of the DDL Archive Utility at the time of this writing is compiled with a reference to SQLXML 3.0 SP1. The current version of SQLXML available at the same time for download from Microsoft's web site is SQLXML 3.0 SP2. If you install SQLXML 3.0 SP2 and try to use the Archive Utility you get a message that looks the one shown in figure 1.

The actual error message in this stack trace is , "File or assembly name Microsoft.Data.SqlXml, or one of its dependencies, was not found." I have to say that's not a particularly use nor accurate error message because what it's really trying to tell us is the the version of SQLXML on the system does not match the version the utility expects to find. It's actually even kind of interesting that the CLR doesn't seem to know if it's a file or an assembly.

Seems to me that a more correct and obviously knowable error for the CLR might be something like, "The requested strong name for assembly Microsoft.Data.SqlXml was not found."  Or maybe even, "Version 3.0.1523.0 of assembly Microsoft.Data.SqlXml not available"  If you look in the GAC you will find Microsoft.Data.SqlXml. You can view assemblies in the GAC in a number of ways. The easiest is probably to use the Assembly Cache Viewer (shfusion.dll) that is automagically invoked by browsing in Explorer to the "assemblies" subdirectory of the Windows or WINNT - depending on the host's OS - directory on the host machine. Another easy method is to use the "Microsoft .Net Framework 1.1 Configuration" utility found in "Administrative Tools" program group on the host. My suggestion would be to use the latter Configuration Utility because it let's you perform the necessary maintenance to resolve problems like the one we are talking about with the DDL Archive Utility.  

Using the "Microsoft .Net Framework 1.1 Configuration" tool you can first look in the GAC to see that the version of the SQLXML assembly is 3.2.2917.0.

Then if up right click on the Applications item in the left pane and Add the DDL Archive Utility as an application, then Discover Assemblies you'll note that the Archive Utility expects to be using the SQLXML version 3.0.1523.0.

To fix the mismatch you can configure the Microsoft.Data.SqlXml assembly for all applications by Adding it to the Configured Assemblies group just under My Computer. Alternately you can Add the assembly to the Configured Assemblies under the application if you want the change to affect only the application and not others that might reference the assembly. In the case of SQLXML I'm hard pressed to identify which is more desirable because SQLXML makes you uninstall SP1 before you can install SP2 which means all applications with a compiled reference to SP1 will break. I'm sure the preferred alternative has or will emerge and there will no doubt be very good reasons to choose one over the other.

The only difference in the steps to configure for either choice is that at the application level you can pick from a distilled list of only those assemblies referenced by the application if you want to in the Add dialog. At any rate, once you add the assembly to a Configured Assemblies you can access the properties of the assembly. Selecting the Binding Policy tab of the assembly properties allows you to redirect request for a specific version or a range of versions to a specific version.
 

Notice that here I entered 3.0.0.0-3.2.2917.0 in the requested Version input box. I could also have entered 3.0.1523.0 or even 3.0.1523.0-3.2.2917.0 and achieved the same result in this case. That result being that the DDL Archive Utility will now execute correctly.

It's worthwhile to point out that I can also fix the Utility to work for SQLXML 3.0 SP2 by recompiling the Archive Utility with the newer version of SQLXML installed. The problems with that solution are more troublesome. That requires a redeployment and reinstall or upgrade of the DDL Archive Utility for everyone when they upgrade to SQLXML3.0 SP2. Editing the binding policy seems to me a much better alternative because it permits the same executable to work with either SP1 or SP2. Of course, I suppose you could also map the new version back to the old version if the application expected to find version 3.2.2917.0 and all you had installed on the host was version 3.0.1523.0. (Note: I did not test this, it just seems reasonable.)  So, no mater which version you need and what version you have, provided the functionality within the assembly is not changed between versions such that your usage would cause real invalid references or incorrect syntax for example, with the "Microsoft .Net Framework 1.1 Configuration" tool you have added ability to keep things running. And everyone likes a DBA that can keep things running.