Wednesday, January 11, 2012

Installing SQL CE on my dev machine (x86)

I’m running VS2010 Express (C#). After downloading and installing SQL Server CE (3.5) on my 32-bit machine, created a simple app, added a reference to System.Data.SqlServerCE, and expected it to “just work”. It didn’t.

PROBLEM #1: At the point of instantiating a new SqlCeEngine, a SqlCeException was thrown:

“Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8080. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.”

The Inner Exception stated:

“Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)”

As for the outer exception, the KB article implies that this should only be a problem on a x64 system. It provides no help for those who encounter this on a 32-bit system. So I turned my attention on the 'sqlceme35.dll' referenced in the inner exception. I found this dll located at the root of the SSCE installation folder (C:\Program Files\Microsoft SQL Server Compact Edition\v3.5). So why can’t VS find it?

[Of course, I could add the SSCE install folder to my System Path (env var). But this solution won’t work during deployment.]

I tested several options to try to resolve the problem:

  • Test #1: copied 'sqlceme35.dll' to [solution]/bin/debug folder
  • Result: This of course avoids the exception, but we shouldn’t have to do this manually. (Note: The introductory documentation doesn’t even mention this requirement).
  • Test #2: Tried to add a Project Reference to sqlceme35.dll. (using the “Browse” tab in Add Reference dialog).
  • Result: Failed (“Reference…could not be added. Please make sure that the file is accessible, and that it is a valid assembly or COM component.”) Seems that dll is not a .NET assembly (as explained here)
  • Test #3: deleted 'sqlceme35.dll' from Test1 and copied it to the application folder (recommended here). Rebuilt
  • Result: failed.
  • Test #4: deleted 'sqlceme35.dll' from Test3 and copied it to the %WINDIR%\System32 folder (rec’d here)
  • Result: Succeeded; but seems way too intrusive. If this was necessary, why didn’t the SSCE installation msi do this for me?
  • Test #5: deleted 'sqlceme35.dll' from System32 (Test4) and added it to the Solution in VS (rec’d here and esp here). I.e., r-click on project in Solution Explorer, “Add Existing Item”, navigated to SSCE install folder and selected sqlceme35.dll. (NOTE: Using dropdown arrow, I selected “Add As Link” (see instructions here). Had to change the Project Output to “Copy If Newer” (as rec’d above). (Note: that option was greyed out until I stopped debugging). this transfers the dll into the appropriate output folder (e.g. /bin/debug)
  • Result: Succeeded! Will need to do this for all 8 SSCE dlls

PROBLEM #2: Although the results are functional, it really “uglifies” my solution tree. I tried putting the dlls into a folder named SqlCeLib, then repeateating test 4+4b – failed! The “Copy if Newer” functionality copied the tree structure into the target bin, including the parent folder (SqlCeLib)! So the resulting executable couldn’t find the dlls! But this page says it should work!

Solution#1: after re-reading that page, I decided to try creating the folder again, but calling it X86. Lo & behold, it worked! But WHY??

Solution#2: Rather than use the “Copy if Newer” functionality (and changing that setting back to “Never Copy”), I instead added the following Post-build event (rec’d here; From project properties, Build Events tab, add the following to the Post-build event command line):

copy "$(ProjectDir)\SqlCeLib\*.*" "$(TargetDir)"

This works, but only with actual copies of the dlls. It doesn’t work with Links. But upon reflection, I think I don’t want to use links after all. It removes control in the app itself to control exactly which versions of the dll are currently being built against. So I think this will be my go-forward plan.

No comments:

Post a Comment