Continuous Integration with Sql Compact 4.0

In our project we are using Sql Compact 4.0 for testing the nhibernate mappings and our queries. In production environment we use Sql Server 2008 Express R2, therefore we thought that Sql Compact 4.0 is as close as it can get for a file database. Recently we moved our continuous integration system from cruisecontrol.net to jetbrain’s teamcity 6. The teamcity agent is installed on a Windows 2003 R2 x64 server. But our unit tests accessing sql compact 4.0 didn’t work on teamcity! Let’s look closer what happened and how we solved it.

The first exception raised on the build system was the following:

System.Data.SqlServerCe.SqlCeException : Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

Because we are using private bin installation of Sql Compact 4.0 we double checked the deployment paths. We corrected the first found issue by renaming the folder x64 to amd64. The corrected deployment structure looks like the following:

And the prebuild event:

  <PropertyGroup>
    <PreBuildEvent>xcopy /E /Y "$(ProjectDir)..\libs\SqlServerCompact\*.*" "$(TargetDir)"</PreBuildEvent>
  </PropertyGroup>

This is right according to the documentation. Running this configuration gave us another weird exception:

System.BadImageFormatException :  is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)

The exception clearly states that a process is trying to load an assembly which has a different architecture then the calling assembly. So we began investigating. We detected that the following was happening:

1. TeamCity build agent is running under 32 Bit.
2. Agent triggers nant.exe which is running under 32 Bit
3. Nant.exe triggers xunit.console.clr4.x86.exe which is the 32 Bit version of the xunit.console runner
4. Console runner triggers our test assembly which references System.Data.SqlServerCe.dll which tries to load a native x64 bit library
5. Crash of the xunit.console

Interestingly when we executed the build process on the build server in the command window the build was working properly!

Our idea was that there must be something wrong how the System.Data.SqlServerCe.dll detects the operating system architecture. But how could we find the problem cause?

Our idea was to dump out all environment variables in the build triggered by the command window and in the build triggered through the build agent and then compare what could be different.

Build agent	Command window
PROCESSOR_ARCHITECTURE=AMD64  PROCESSOR_ARCHITEW6432=AMD64 … CommonProgramFiles=C:\Program Files (x86)\Common Files CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files CommonProgramW6432=C:\Program Files\Common Files	PROCESSOR_ARCHITECTURE=AMD64  … ProgramFiles=C:\Program Files ProgramFiles(x86)=C:\Program Files (x86)

Crazy, what the heck is PROCESSOR_ARCHITEW6432=AMD64? This variable is set when the process is running in the WoW-Mode (Windows 32-bit On Windows 64-bit). When running in the command window this environment variable is not set. So we quickly removed the environment variable before executing the build script on the build agent by using the following command:

set PROCESSOR_ARCHITEW6432=

In nant this can be done with:

	<target name="prepare-for-sql-compact">
		<setenv name="PROCESSOR_ARCHITEW6432" value=""/>
	</target>

Or only when running under TeamCity (here by detecting TEAMCITY_VERSION environment variable):

	<target name="prepare-for-sql-compact">
		<if test="${environment::variable-exists('TEAMCITY_VERSION')}">
			<setenv name="PROCESSOR_ARCHITEW6432" value=""/>
		</if>
	</target>

Magically then the unit tests are running! So Sql Server Compact 4.0 has serious troubles loading the native assemblies when running under Wow-Mode! Hope this will prevent you guys from serious headaches!