Friday, May 25, 2012

Private deployment of SQL Server Compact 3.5 SP2

The information found in the official documentation is not very extensive, and this blog post hopes to extend on the information found there. I have already blogged about private deployment with SQL Server Compact 4.0, and have an overview post here. 

SQL Server Compact 3.5 SP2 requires the following software:

The OS must be Windows XP SP3 or higher:

For applications targeting .NET 3.5 SP1, no additional software is required.

For applications targeting .NET 4.0, either .NET Framework 3.5 SP1 or the VC++ 2005 SP1 redistributable (for x86 and/or x64) is required.

Make sure the 3.5 SP2 runtime is properly installed, on x64 machines you must install both the x86 and x64 runtimes.

Let us assume that the requirements above are fulfilled (notice that Windows 7 includes .NET 3.5 SP1). So what else is required – let’s make a Console app and find out! Our goal is to create an application, that runs without SQL Server Compact 3.5 SP2 already installed, on both x64 and x86 systems. Notice that the instructions below works, no matter if your application targets “x86” (the 32 bit .NET Framework on all platforms, “Any CPU” (either the 32 or 64 bit .NET Framework), or x64 (the 64 bit Framework exclusively).

In Visual Studio, create a new Console project:

image

Now we must include the unmanaged SQL Server Compact C++ runtime files, each set of files in their own folder, which are platform specific. So create 2 folders in the project, one named x86 for the 32 bit files, and one named AMD64 (not x64!) for the 64 bit files.
NOTE: This convention, based on the value of the PROCESSOR_ARCHITECTURE environment variable is a special SQL Server Compact feature.

image 

Now we must locate the required files. If you are using a 32 bit machine, only the 32 bit files are installed on your machine, and you must manually extract the 64 bit files to a folder as described here. I am using (like most these days) a x64 machine, and it has the  most recent files for both platforms already installed. Make sure that all files you include have the exact same file version, or you will fail. The 3.5 SP2 file version is 3.5.8080.0, you can view the file version in Windows Explorer.

The files in the “C:\Program Files” folder are all 64 bit files, and the files in the “C:\Program Files (x86)” folder are all 32 bit files (on x64 systems)

The files you need to add are:
sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll

So, add the files from C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 to the AMD64 project folder, using Add, Existing Item (make sure to change the filter to “All files”):

image

Make sure all files are included with Build Action = Content, and Copy to Output Directory = Copy Always:

image

Then add files from C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5 to the x86 project folder, in the same way:

image

Finally, add the ADO.NET provider (System.Data.SqlServerCe.dll) to the project root, add this file form the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Private (!) folder. Also set this file as Content, Copy Always:

image

Now add a reference to the ADO.NET provider in the root project folder:

image

Make sure the Version (Assembly Version) is 3.5.1.50, that indicates that it is the correct file:

image

Now build the project, and look in the bin/debug folder, to make sure all files are copied with the project output. You can now test that private deployment works either by uninstalling the 3.5 SP2 runtimes or on a PC without the runtimes installed.

If you are using only ADO.NET “Classic” (no LINQ to SQL or Entity Framework), this is all you need for private deployment. If you initialize a LINQ to SQL DataContext with a SqlCeConnection object, as I describe here, no additional configuration is required.

If you depend on the DbProvider API (LINQ to SQL and Entity Framework does), you must add the following to your project’s app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add
name="Microsoft SQL Server Compact Data Provider 3.5"
invariant="System.Data.SqlServerCe.3.5"
description=".NET Framework Data Provider for Microsoft SQL Server Compact"
type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
/>
</DbProviderFactories>
</system.data>
</configuration>

UPDATE Feb 2013: Entity Framework private deployment is ONLY supported with Entity Framework 1.0, so below will not work in VS 2010/VS 2012 (EF 4.0 and EF 5.0)


If you use Entity Framework, you must add the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Private\System.Data.SqlServerCe.Entity.dll to your project root as content, and have a configuration like the following (as described by the SQL Compact Team here)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add name="Microsoft SQL Server Compact Data Provider 3.5" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly xmlns="">
<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="3.5.1.0-3.5.1.50" newVersion="3.5.1.50" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>


Hope this was useful!

41 comments:

Jen's Kitchen said...

It is a great guide.Thank you! I have a question here: right now I am working on a windows application written in C++ (MFC), and i want to deploy SQL compact with it. So that every time used exchanged data with the embedded SQL compact, the data will be synchronized with the SQL database on a remote server. The main reason to do so is to speed up the data storage speed. Is there a way to deploy SQL compact with C++ project? All the guides I found so far use C# project.

ErikEJ said...

Hi Jen: I am not sure about C++, but I think you can simply include all the files with your app.

DocSnyder said...

Hello,

I've created a WPf project with SQL Server CE SP1, without the private deployment stuff you mentioned here. Everything was fine and I could start the application on my dev pc. Now I updated the CE framework to 3.5 SP2, and put all the files and references as you described here. But now I get this error message on my dev pc:

[A]System.Data.SqlServerCe.SqlCeConnection cannot be cast to [B]System.Data.SqlServerCe.SqlCeConnection. Type A originates from 'System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Users\michael\Documents\Visual Studio 2010\Projects\ComicBookCollector\ComicBookCollector\bin\Debug\System.Data.SqlServerCe.dll'. Type B originates from 'System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\System.Data.SqlServerCe\3.5.1.0__89845dcd8080cc91\System.Data.SqlServerCe.dll'.

And I cannot execute the program on another pc (just copied the whole bin folder) without SSCE installed, there I get an error with some signature properties like:

EventType = clr20r3
P1 = myapp.exe
P4 = system.data.sqlserverce.entity
P5 = system.windows.markup.xamlparse (because the main window constructor wants to create the datacontext)

I have created the database using your sql server compact extension in vs 2010.

Do you know whats wrong here? How can I prevent my application to use the old version of SQLServerCE dll in the GAC or how to remove it from / update in GAC?

Regards, Michael

ErikEJ said...

Doc: Looks like you project or a dependent dll/project references the older version

DocSnyder said...

Hi Erik,

I have solved the problem. It seems that in the config file the mapping to the newer version was wrong, I changed the attribute value 3.5.1.0-3.5.1.50 to just 3.5.1.50 and everything is fine.

Regards, Michael

Unknown said...

Thank you Erik! This is useful.
I have followed your guide, built the project, and it went well. However, after I input the "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" into the connectionString, the screen showed the following error warning message:

The 'Microsoft.SQLSERVER.CE.OLEDB.3.5' provider is not registered on the local machine.

Here is my source code(C#):

//System.Data.Common.DbProviderFactory dpf = System.Data.SqlServerCe.SqlCeProviderFactory.Instance;
//string strConn = "Data Source=CkmainInsurance.sdf;SSCE:Max Database Size=512";
System.Data.Common.DbProviderFactory dpf = System.Data.OleDb.OleDbFactory.Instance;
string strConn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=CkmainInsurance.sdf;SSCE:Max Database Size=512";

using (IDbConnection conn = dpf.CreateConnection())
{
conn.ConnectionString = strConn;
conn.Open();
string strSQL = "select * from AllProducts";

IDbDataAdapter da = dpf.CreateDataAdapter();
da.SelectCommand = dpf.CreateCommand();
da.SelectCommand.CommandText = strSQL;
da.SelectCommand.Connection = conn;

DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
}

ErikEJ said...

You cannot use the OLEDB provider with private deployment, only the ADO.NET provider, and using the OLEDB provider form .NET with SQL Compact is not supported.

Unknown said...

thanks but i have this exception

System.Data.SqlServerCe.SqlCeException was unhandled
Message=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.
Source=""
HResult=-1
NativeError=-1
StackTrace:
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
at System.Data.SqlServerCe.SqlCeConnection..ctor()
at System.Data.SqlServerCe.SqlCeProviderFactory.CreateConnection()
at System.Data.EntityClient.EntityConnection.GetStoreConnection(DbProviderFactory factory)
at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName)
at DAL.OimDBEntities..ctor()
at DAL.OimRepository..ctor()
at Microsoft.Rtc.Collaboration.Sample.SubscribePresenceView.UCMASampleSubscribePresenceView.Subscribe()
at Microsoft.Rtc.Collaboration.Sample.SubscribePresenceView.UCMASampleSubscribePresenceView.Run()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.DllNotFoundException
Message=Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source=System.Data.SqlServerCe
TypeName=""
StackTrace:
at System.Data.SqlServerCe.NativeMethods.GetSqlCeVersionInfo(IntPtr& pwszVersion)
at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath, Int32 moduleVersion)
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
InnerException:


ErikEJ said...

Tarek: Please share more information, is all DLL files present and correct under the .exs folder? What type of app? Did you install/extarct both x86 and x64 MSIs?

Unknown said...

Huge help. Thanks for putting this out there.

nmichalodi said...

I am having problems in building a setup project for my application deploying privately SQL Server 3.5 SP2. I have followed your steps and if I copy the Release/Debug folder to another pc it works great! When I use either InstallShieldLE or WIX I get an System.Data.SqlServerCe error. Is there anything in app.config that I should take in mind?

ErikEJ said...

nmichael: What is the error? and is the correct version of the dll present in the project root and included as content?

nmichalodi said...

Thank you for your reply. I have build an .NET 3.5 application. All the dll versions are correct, I did the steps again exactly as you mention in your article. when I attempt to execute the installed application file I get the following error:

Περιγραφή:
Stopped working

Υπογραφή προβλήματος:
Όνομα συμβάντος προβλήματος: CLR20r3
Υπογραφή προβλήματος 01: beautysalonmanagement.exe
Υπογραφή προβλήματος 02: 1.0.0.0
Υπογραφή προβλήματος 03: 51a725ff
Υπογραφή προβλήματος 04: System.Data.SqlServerCe
Υπογραφή προβλήματος 05: 3.5.1.50
Υπογραφή προβλήματος 06: 4b743b2f
Υπογραφή προβλήματος 07: 15c
Υπογραφή προβλήματος 08: 12
Υπογραφή προβλήματος 09: System.Data.SqlServerCe.SqlCe
Έκδοση λειτουργικού συστήματος: 6.1.7601.2.1.0.256.48

If I copy the Debug/Release folder the application works perfectly.....

If you have any ideas I would be greatfull....

ErikEJ said...

nmichael: I am not good with greek, and the comments here is probably not the best forum for ClickOnce support. Suggest you start by implementing proper execption logging/handling, and maybe ask in the MSDN form.

nmichalodi said...

Have you successfully created a setup project with the private deployment of SQL Server Compact 3.5 SP2? I mean private deployment of SQL Server Compact 3.5 SP2 is compatible with setup project right?

nmichalodi said...

Have you successfully created a setup project with the private deployment of SQL Server Compact 3.5 SP2? I mean private deployment of SQL Server Compact 3.5 SP2 is compatible with setup project right?

ErikEJ said...

nmichael: Sure, it is compatible. It is just a bunch of files, make sure you do not includes SQL Server Compact as a dependency...

nmichalodi said...

Thank you for your help Erik! Everything is as it should be, your instruction steps in privately deploying SQL CE work like a charm! Indeed one can create a setup project without any hassle. My problem was with the Windows Program Files read/write permissions! thanks for your time again!

Maui said...

Hi, thanks for the great article! I still have an issue when launching the application on x86 systems. I get an DllNotFoundException for the sqlceme35.dll. I checked the application's directory, but all of the .dll files are copied correctly (to both, x86 and AMD64 folder).

Has anyone the same issue or an idea what the problem might be?

ErikEJ said...

Maui: wrong dll file in x86 folder, or using wrong Ado.net dll. You can email me your project, and I will have a look.

Maui said...

Hi thanks for the quick answer. I can't email the project but what about the wrong ado.net .dll file? I added the file from Program Files/Microsoft SQL Server Compact/v3.5/Private
Do I have to add the x86 version as well? Maybe I didn't get it right from the project

Unknown said...

Hi Eric! Your article is great and helped me a lot, thanks! But I got the same DllNotFoundException for the sqlceme35.dll as Maui. I running winXP on my virtual machine. Do you have any solution for this problem?
What do you mean by "using wrong Ado.net dll"? And where exactly in my project's output folder should be amd64 and x86 directories?
I also have tested my app on other machine running Win7 and without SQL Server CE installed. App worked only in release, but it worked!!

ErikEJ said...

Elena: What is the Platform target of your project?

Unknown said...

I tried AnyCPU and x86 as well, with the same result

ErikEJ said...

Elena: Probably best to email me a repro project, or start an MSDN thread - also see my blog post here: http://erikej.blogspot.dk/2013/10/sql-server-compact-4-desktop-app-with.html (Easily applicable to 3.5 as well)

Unknown said...

Thank you for your help by e-mail! My problem really was that I used EF 4 with SqlServerCE 3.5! After I took 4.0 version there was only one thing that I had to do - install on my WinXP virtual machine Visual C++ 2008 Runtime Libraries, on other machines all works perfectly!

ErikEJ said...

Elena: Glad you got it working, notice that the VC++ runtime is always installed with .NET 3.5 SP1

İstanbullu said...

Hello Erik, Thank you for your informative blog entry. I did what you explained step by step even several times but failed every time. When I debug my project, execution stops at SQLCeConnection class' Open() method throwing "Unspecified error"
I don't know where to start searching for this error.

ErikEJ said...

Istan: I would be happy to have a look at your project...

İstanbullu said...

Thank you for your fast reply. How can I send you my project?

ErikEJ said...

Istan: Email me (conatct info in my profile here) or share via OneDrive or similar

Unknown said...

Hi Erik,I checked SqlServerCe properties, My assembly version is 3.5.1.0 and I m getting following error msg 'Unable to load native components'. What should I do?

ErikEJ said...

Srinivas: then you are using the wrong dll, assembly version must be 3,5,1,50!

Unknown said...

Thanx a lot fr ur guidance Erik, I made all the required changes, It perfectly works with x86 mode, but when I change Target Platform to 'ANY CPU',it displays following error, "Unable to load dll'sqlceme35.dll"The specified module could not be found",Could you please tell me what is to e done?

ErikEJ said...

Srini: just stay with x86, and avoid many issues! http://erikej.blogspot.dk/2013/10/sql-server-compact-4-desktop-app-with.html

Unknown said...

Thank u Erik. But in my project, The target platform must be 'ANY CPU' as it's the client requirement.I'm using VS2005 n CE3.5, o.s is Windows 8.1.is thr any possibility to make my app. work on 'ANY CPU'?

ErikEJ said...

Srini: you are the technician, not the client, and x86 will Work with any cpu! Yes, it is possible, but many circumstances can make any cpu break, in particular with 3,5 SP2! Will be happy to assist further on consultancy basis!

Unknown said...

thanx a lot Erik ! Wil refer ur articles regarding this issue!

Unknown said...

I am using a private install for SQL CE 3.5, but KB974247 causes some problem. I would like to use DBProvider and AssemblyBinding in my app config to override the GAC, but I can't seem to get it to work. Is it possible?

ErikEJ said...

John: I doubt it, may work better with 4,0 perhaps

Unknown said...

I have two apps that use 3.5 and the first I upgraded to 4.0 with no problems. The second one I struggled for two days with it until I came across your post. I copied the two folders and set the dll's to copy always and it worked like a charm. Thanks so much!!!!!!!

Zath