SQL Server Compact Edition Binaries

Question

I am maintaining an application which was deployed using ClickOnce. Its recent version required SQL CE; and we placed the SQL CE dlls along the application and every thing was working fine.

Now some people are reporting issues like at one place the user is getting the following exception. He has Visual Studio 2010 Beta 2 installed

File version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality.  This could be due to the presence of multiple instances of SQL Server Compact of different versions.  Please install SQL Server Compact binaries of matching version [ADO.NET Provider File Version = 8044, Native Binary File Version = 5692]
   at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath, Int32 moduleVersion)
   at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
   at System.Data.SqlServerCe.SqlCeConnection..ctor()
   at System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString)

At the other place the user is getting the following exception.

System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
   at System.Data.SqlServerCe.NativeMethods.DllAddRef()
   at System.Data.SqlServerCe.SqlCeConnection..ctor()
   at System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString)

How to bundle our application while keeping SQL CE dlls local to the application? It would be very hard to ask everyone to uninstall and reinstall with newer ClickOnce setup having package dependency on SQL CE

---

Khurram

Answer 1

what version of sql compact you bundeled with you app. Can you check if the version of System.Data.SqlServerCe and other sql compact native binaries in deployed folder is same on the machine which the app is deployed?

AND

BadImageFormat exception is indication that users are trying to load 32 bit binaries on 64 bit box or vice-versa. If your application is for both the platforms then you have to include both 32bit and 64 bit sqlce binaries with your clickoncle application. you can find this post helpful for doing that.

-Tarun

Answer 2

Thanks Tarun for the quick reply...BadImageFormat issue is understood; and will check the 64bit scenario!

Yes; the version of System.Data.SqlServerCe and native binaries is same; 3.5.5692.0

---

Khurram

Answer 3

Hey Khurram,

"File Version Mismatch" is one of the known issues for Beta2. IF I'm not wrong - the deployment machine would be a 64bit one.
As a general recommendation, a 64bit machine must either install both x86 and amd64 MSIs of SQL Server Compact or none. And both installations should be of the same version.
VS2010 beta2 installs only x86 MSI - that is why this recommendation is violated and hence the issue.

As resolution of this issue -
Install x86 & amd64 MSIs from http://www.microsoft.com/downloads/details.aspx?FamilyID=411ba1c5-ba57-45b6-9148-91bed6e7a9f1&displaylang=en on the machine where you are seeing this issue.


Regards
Amit

Answer 4

Thanks Amit for the reply. The File Version Mismatch issue is being reported by our internal developer who is running 32bit Windows 7 and has Visual C# 2010 Express Beta 2 / Visual Web Developer 2010 Express Beta 2 installed which I think also installs SQL CE 3.5 SP2 Beta

As long as its only due to some conflict with SQL CE 3.5 SP2 Beta and will get resolved when SQL CE 3.5 SP2 RTM will come out; its not that big deal. Even if it remains as is; we will be able to include SQL CE 3.5 SP2 RTM dlls and associated ADO.NET assembly.

---

Khurram

Answer 5

Khurram, This looks interesting to me.

Would it be possible for you to share the details of the application and the machine state in following terms -
1. What version of SQL Compact, the apps are using (What version to be precise? What version of System.Data.SqlServerCe.dll and sqlceme35.dll is packaged with the app)
2. What is the state of the machine (What version of SQL Compact MSI installed - it should be visible from Add-Remove-Program)


Regards
Amit

Answer 6

1. The application uses 3.5.5692.0 version of System.Data.SqlServerCe.dll. sqlcese35.dll, sqlceqp35.dll and sqlceme35.dll all have the same version. These all dlls (All the sqlce*.dlls and System.Data.SqlServerCe.dll) are placed in local folder to the ClickOnce deployed application
2. As mentioned earlier its our developer workstation and interesetingly he has the following items listed in its Control Panel > Programs and Features

1. Microsoft SQL Server 2005 Compact Edition [ENU] (3.1.0000)
2. Microsoft SQL Server Compact 3.5 For Devices ENU (3.5.5386.0)
3. Microsoft SQL Server Compact 3.5 SP1 Design Tools English (3.5.5692.0)
4. Microsoft SQL Server Compact 3.5 SP1 Query Tools English (3.5.5692.0)
5. Microsoft SQL Server Compact 3.5 SP2 Beta English (3.5.8044.0)

---

Khurram

Answer 7

Hello Amit, Hello Khurram

I also ran into this issue after installing Visual Studio 2010 beta 2. We resolved our issue by uninstalling 'Microsoft SQL Server Compact 3.5 SP2 beta English' (everything works just as before thereafter). However, this leaves us wondering what exactly is going on, as our application uses a privately deployed instance of SQL Compact, precisely because we want to avoid these types of issues. Why is a central installation of SQL Compact interfering with our private deployment? Can someone explain what is going wrong?

Scenario:

My Machine:
Windows 7; 64bit (clean install)

SQL Compact:
No centrally deployed version of SQL Compact exists on the system
The application uses a privately deployed, 64bit version of SQL Compact 3.5 SP1

Errors:
Installing Visual Studio 2010 beta 2 installs 'Microsoft SQL Server Compact 3.5 SP2 beta English' (x86)
Thereafter, running our application leads to same exception mentioned by Khurram:

File version mismatch detected betweeen ADO.NET Provider and native binaries...
[ADO.NET Provider File Version = 8044, Native Binary File Version = 5692]

   at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath, Int32 moduleVersion)
   at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
   at System.Data.SqlServerCe.SqlCeConnection..ctor()

Simply uninstalling 'Microsoft SQL Server Compact 3.5 SP2 beta English' from the control panel "fixes" the issue, but leaves me wondering how private is a private installation really?

Answer 8

This is due to the standard .NET assembly loading process and improved error messages from the SQL Compact ADO.NET provider (System.Data.SqlServerCe.dll). You have a reference to this file which has version 3.5.1.0, and the same file is in the GAC (build 8044), but this never build now checks that the native ddl files match in build number. This is not the case, as the native code binary files are loaded privately, but are older.

So a "private" installation is really not so private in terms of the managed code part. I think this will improve once 3.5 SP2 has been released (I'm guessing here!).

This is from the 3.5 SP2 beta download page:

Due to changes in SQL Server Compact SP2 Beta 2 and additional 64-bit version support, centrally installed and mixed mode environments of 32-bit version of SQL Server Compact 3.5 or 3.5 SP1 and 64-bit version of SQL Server Compact 3.5 SP2 Bea 2 can create what appear to be intermittent problems. To minimize the potential for conflicts, and to enable platform neutral deployment of managed client applications, centrally installing the 64-bit version of SQL Server Compact 3.5 SP2 Beta 2 using the Windows Installer (MSI) file also requires installing the 32-bit version of SQL Server Compact 3.5 SP2 Beta 2 MSI file. For applications that only require native 64-bit, private deployment of the 64-bit version of SQL Server Compact 3.5 SP2 Beta 2 can be utilized.

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 9

Is there any way we can force ".NET assembly loading process" to always pick the System.Data.SqlServerCe.dll from the "private bin" (some tweak in app.config etc) and not the latest one from GAC; so that when its associated native binaries get loaded the version conflicts doesnt happen?

---

Khurram

Answer 10

No, as long as the assembly version matches, the GAC file will always be loaded: http://msdn.microsoft.com/en-us/library/yx7xezcf.aspx

But if you look in the 3.5 SP2 install folder, there is a /Private folder with a System.Data.SqlServerCe.dll which has a different assembly version:  3.5.1.50 - so maybe this will work better once 3.5 SP2 has been released ?

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 11

No, as long as the assembly version matches, the GAC file will always be loaded: http://msdn.microsoft.com/en-us/library/yx7xezcf.aspx

But if you look in the 3.5 SP2 install folder, there is a /Private folder with a System.Data.SqlServerCe.dll which has a different assembly version:  3.5.1.50 - so maybe this will work better once 3.5 SP2 has been released ?

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Too bad, that the System.Data.SqlServerCe.Entity.dll requires the System.Data.SqlServerCe.dll of version 3.5.1.0, which obviously is the non private fashion. 

Edit: I found a workaround for this. The Application Config should redirect the public SqlServerCe 3.5.1.0 to the Private Version 3.5.1.50

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" culture="neutral" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="3.5.1.0" newVersion="3.5.1.50" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe.Entity" culture="neutral" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="3.5.1.0" newVersion="3.5.1.50" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

Answer 12

Hi Andreas,

Have you tried the  System.Data.SqlServerCe.Entity.dll which is also located in the Private folder? - or is that the one you are referring to already?

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 13

Hi ErikEJ

Yes, it looks like these things will get better with SQLCE 3.5 SP2. My recap:

a)
The official recommendation is to install both 32- and 64-bit SQLCE packages to the GAC (with an assembly version of 3.5.1.0). Furthermore, both packages must have identical file versions. Currently I know of the following:
SQLCE SP1:               5692
VS 2010 beta2:          8044 (Issue: VS 2010 only contains and installs the 32-bit package!)
SQLCE SP2 beta:        8049
This is acceptable for developer machines where the environment is easily controlled and well understood.

b)
So far private SQLCE deployments were fiction. Assuming nobody gets the idea of installing SQLCE SP2 private assembly (version 3.5.1.50) to the GAC, these days should finally be over. Thank you EricEJ. Your comments were much appreciated!

Kind Regards