Can I XCopy SQL CE 3.5? yes and no, see message.

Question

I was hoping to upgrade my SQL CE from 3 to 3.5 for my PPC and desktop applications.  However, when I went to XCopy to another desktop I got errors.  First, error was that the System.Data.SqlServerCe.dll is not enough, so I copied the other dlls too.  Then I got provider not installed error.  I do not want to install, I just want to XCopy.

So I searched the newsgroups and online help. I tested using ClickOnce and that worked only because it installed SQL CE for me... but I want to just XCopy.

Next I commented out my code that uses LINQ and just used the old data adapter way to get data and it worked.

I use the same connection string:

"Data Source=C:=test.sdf;Password=sa2008;Persist Security Info=True;Encryption Mode=PPC2003 Compatibility;Max Database Size=4000"

 

1. Using old way to get data works with XCopy

2. Using LINQ way to get data DOES NOT work with XCopy

 

Seems that LINQ requires that SQL CE be installed on the desktop to work. 

Is this true?

 

(I know LINQ is not support with SQL CE at this time, but it's sure nice to use)

One other problem I noticed is that I can compile and run my LINQ to SQL CE solution just fine in VS.Net 2008 express, but the same solution gives me a compile error without details as to why when compiled in VS.Net 2008 Pro.

 

,,,Bydia

Answer 1

Did the target machine have .NET Framework 3.5 installed ? 3.5 is required for LINQ to work - and LINQ is desktop only.

What is the exact error message - "Then I got provider not installed error." ? 

 

Answer 2

Yes, the target machine has .Net 3.5 installed, it's SQL CE that I don't want to install, just xCopy.  In fact the application works until I select a SQL CE db and try to init the connection string of my LINQ object.  The code is ok, because it works on my development system.

 

The following files are in the folder of the app:

System.Data.SqlServerCe.dll

sqlcese35.dll

sqlceqp35.dll

sqlceoledb35.dll

sqlceme35.dll

sqlceer35EN.dll

sqlcecompact35.dll

sqlceca35.dll

 

If I don't use linq those work, with old style data adater access.

If I install SQL CE on the target system, then all works, if I remove it then LINQ fails.

 

The exact message is:

Cannot open "C:\Backups\CDCache\Test\Test.sdf". Provider 'System.Data.SqlServerCe.3.5' not installed.

Details:

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.InvalidOperationException: Cannot open See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.InvalidOperationException: Cannot open C:\Backups\CDCache\Test\Test.sdf. Provider 'System.Data.SqlServerCe.3.5' not installed.
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Initialize(IDataServices dataServices, Object connection)
   at System.Data.Linq.DataContext.Init(Object connection, MappingSource mapping)
   at System.Data.Linq.DataContext..ctor(String fileOrServerOrConnection, MappingSource mapping)
   at Data.FS0100.MiFS..ctor(String connection)
   at Data.FS0100.FileSystem.Open(String path)
   at TheKitchenSyncPreview.FormMain.OpenProject()
   at TheKitchenSyncPreview.FormMain.btnOpen_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
TheKitchenSyncPreview
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Backups/CDCache/Test/TheKitchenSyncPreview.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Data.FS0100
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Backups/CDCache/Test/Data.FS0100.DLL
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Data.SqlServerCe
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.5386.0
    CodeBase: file:///C:/Backups/CDCache/Test/System.Data.SqlServerCe.DLL
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.Core
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.21022.8 built by: RTM
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Core/3.5.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Data.Linq
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.21022.8
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Data.Linq/3.5.0.0__b77a5c561934e089/System.Data.Linq.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

. Provider 'System.Data.SqlServerCe.3.5' not installed.
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Initialize(IDataServices dataServices, Object connection)
   at System.Data.Linq.DataContext.Init(Object connection, MappingSource mapping)
   at System.Data.Linq.DataContext..ctor(String fileOrServerOrConnection, MappingSource mapping)
   at Data.FS0100.MiFS..ctor(String connection)
   at Data.FS0100.FileSystem.Open(String path)
   at TheKitchenSyncPreview.FormMain.OpenProject()
   at TheKitchenSyncPreview.FormMain.btnOpen_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
TheKitchenSyncPreview
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Backups/CDCache/Test/TheKitchenSyncPreview.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Data.FS0100
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Backups/CDCache/Test/Data.FS0100.DLL
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Data.SqlServerCe
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.5386.0
    CodeBase: file:///C:/Backups/CDCache/Test/System.Data.SqlServerCe.DLL
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 (REDBITS.050727-1400)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.Core
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.21022.8 built by: RTM
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Core/3.5.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Data.Linq
    Assembly Version: 3.5.0.0
    Win32 Version: 3.5.21022.8
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Data.Linq/3.5.0.0__b77a5c561934e089/System.Data.Linq.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

 

Answer 3

Try adding the below entry as one of the DbProviderFactories in the Machine.config file located in the <windir>\microsoft.net\framework\v2.0.50727\config directory and give a try with your LINQ application.

 

<add name="Microsoft SQL Server Compact Data Provider" 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.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

 

Hope it works.

Answer 4

Thank-you, that did it.... Hope it will be included in the future so that my XCopy does not have to mess with the Maching.config file.

 

 

Answer 5

 

This entry is added as part of the SQLCE Runtime setup. As LINQ depends on it, you need to add the entry manually in case you are following the XCopy approach.

 

Above entry makes sense only when SQLCE is installed on the machine. So, you either need to use the MSI or manually create the entry yourself.

Answer 6

You don't need to mess with the machine.config file, just put the new section in your app.config instead:

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.3.5" />
      <add name="Microsoft SQL Server Compact Data Provider" 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.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

The "<remove invariant=..." makes sure the configuration works on machines that already have this setting registered in machine.config.

Hope it helps,

/Andreas

Answer 7

Just a note for any one who wants to XCopy deploy SQL Server Compact on a Webserver AND wants to use LINQ:
If your Webserver's machine config doesn't have an entry for System.Data.SqlServerCe.3.5, you will have to modify your Web.Config file, by putting this XML fragment just under configuration node:

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.3.5" />
      <add name="Microsoft SQL Server Compact Data Provider" 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.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

Make sure you match the version number and public key of your deployed dll.

Have fun!

Answer 8

hello,

I have added the DbProviderFactories in the application config file as you have proposed.
The assemby System.Data.SqlServerCe.dll is located (copied) in the applications root path.
I have looked up the PublicKeyToken and version in GAC and set it in the app.config.

But at the moment that the application is started an exception is thrown, telling me that the assembly "System.Data.SqlServerCe.dll " or one of its reffered assemblies cannot be loaded.
So what am I missing? Is only a xcopy of the System.Data.SqlServerCe.dll not enough?

Hope you have any clues!

Thanks,

Bas

---

Bas

Answer 9

Bas, this is an old post so I'm sure you got the answer already, but if you are using 3.5 SP1, then the Version should be 3.5.1.0 in your Add node, instead of the original 3.5.0.0.

I've built a wix installer using this and it works fine.

Tom