How to deploy the SQLServer Compact Edition software locally

If you are using a SQLServer Compact Edition database, you will need to deploy the software for it. You can deploy it as a prerequisite, but this requires the customer to install one more thing without clicking Cancel. He must also have administrative privileges.

Additionally, version control is more difficult with ClickOnce deployment, because prerequisites are not handled by the incremental updates, and you can never get all of your customers to reinstall a prerequisite. At least one of them will be out of town, and when they come back, they’ll have too much e-mail to deal with and won’t take the time to follow your instructions to update their application.

A better idea is to deploy the assemblies required locally. This video explains all.

In the interest of copy and paste and making your life easier, here are the 7 dll’s you need to locate and add to your project. You should be able to find them in C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 (or v3.0). If you are using 3.0, substitute “30” for “35” in the names of these dll’s.

  • sqlceca35.dll
  • sqlcecompact35.dll
  • sqlceer35EN.dl
  • sqlceme35.dll
  • sqlceoledb35.dll
  • sqlceqp35.dll
  • sqlcese35.dll

Set the Build Action to “Content”, and “Copy to Output Directory” to “copy always” on these entries in Solution Explorer. On the reference to the System.Data.SqlServerCe.dll in your project, set “Copy Local” to “true”.

In other news about SQLCE databases (or any database, for that matter), if you are deploying one with ClickOnce deployment as data, did you know if you open the database it changes the timestamp, and deploys a new version next time you deploy an update? On the client side, it then copies the previous version to the ApplicationData\pre folder, and puts the new one to the ApplicationData folder. If you’re not handling this in your code, it looks like the user has lost his data. This kind of thing tends to annoy customers, which is always a bad thing that can lead to a loss of cash flow, if you know what I mean.

For a easier way to handle updates and let your database change only when you intend it to, you might want to check out my blog entry titled Where do I put my data to keep it safe from ClickOnce updates? I think the title is self-explanatory.

Tags: , ,

38 Responses to “How to deploy the SQLServer Compact Edition software locally”

  1. Steve Clements Says:

    It doesnt work for me, the build works and publish includes the required files, but when the app runs I get “the specified store provider cannot be found” exception.

    I am using the Entity Framework as well, so I have also included the SQLCE dll for that as well. ??

    • robindotnet Says:

      I responded to you via e-mail, and will post some comments here for posterity. You need to check your connection string. Plus, the Entity Framework is part of .NET, so you need to be sure you are targeting the right version of the framework and including it as a prerequisite so your user has it installed. There is no SQLCE dll specifically for the EF, so I’m not sure what you’re referencing.

  2. Kevin Warner Says:

    I have a vb application using a SQLCE 3.5 sp1 database using Linq with a dbml. I have my solution setup as you described in this article. All of the files get copied to the output directory as expected. I am using a “My.Settings” ConnectionString. When I run it on my development machine with SQLCE installed, it runs great. When I try to install and run it on a machine without SQLCE installed, I get the following error:
    An error occured in the following file – SqlCeFailure
    Cannot open ‘|DataDirectory|\OpusDb.sdf’. Provider ‘System.Data.SqlServerCe.3.5′ not installed.

    What am I missing?

    • robindotnet Says:

      This is the bit you are missing: [On the reference to the System.Data.SqlServerCe.dll in your project, set “Copy Local” to “true”.] This is the last dll that you need, and setting copylocal to true means it will include it in the deployment.

      • Kevin Warner Says:

        I had that set correctly. I fixed it by setting up the “” section of the app.config file

  3. Torben Says:

    Hi Robin,

    thank you very much for that article.

    So if I got the point right, it should be enough to take care that the 7 specified dlls plus System.Data.SqlServerCe.dll should be at the applications location? We use not the VS.NET-deployment but a third-party-tool to create our msi-files so it should be enough to ensure that those 7 files plus System.Data.SqlServerCe.dll are available after install process, right?

    All the best,
    Torben

    • robindotnet Says:

      Hi, You’re welcome. Yes, you don’t have to deploy SQLServerCE as a prerequisite, you can just include those assemblies in the same folder as your executable, regardless of deployment method, and it will work.
      Robin

  4. qi24 Says:

    Hello Robin,

    Thanks for taking the time to send me the app.config XML data, and writing the article ‘How to deploy the SQLServer Compact Edition software locally’. It’s very helpful.

    Best regards,
    Qi24

  5. TimothyP Says:

    Thnx for the tip (through twitter / delegatevoid),
    will be able to use this :-)

  6. chech Says:

    Hello Robin,

    Thank you for that article!
    Can you send me the ‘section of the app.config file’ mentioned on the Kevin Warner comment. I think I have the same problem.

    Best regards,
    Ch.-

  7. Add SQL Compact Edition dlls to project installer (VS) | DeveloperQuestion.com Says:

    […] followed this instructions http://robindotnet.wordpress.com/2010/02/28/how-to-deploy-the-sqlserver-compact-edition-software-loc… but did not […]

  8. Eric Lamkin Says:

    Robin,

    I know it has been several months since you helped me with this idea in the Microsoft Forums.
    I’ve incorporated the idea of bundling the CE .dll files and while it appears to work when deployed to XP systems, it fails for version incompatibility reasons in Windows 7 and I suspect the failure I get when deploying to Vista is due to this as well. For reference, my application was developed on XP. When bundling the CE .dll files can it only be done to like OSs?

    Here are the error details when I move the VB project to Windows 7.

    InnerException: System.Data.SqlServerCe.SqlCeException
    Message=The application is trying to load native components of version 5692 that are incompatible with ADO.NET provider of version 8080. Incompatible versions can lead to application instability and hence loading of SQL Server Compact has been aborted. 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.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)

    Eric

    • Eric Lamkin Says:

      Indeed, when I ‘un-do’ the bundling of the CE .dll files the version mismatch error does not occur.
      When I pull up the properties for the CE .dll files on what the system has installed and what the program was bundling I find the versions 3.5.8080.0 and 3.5.5692.0 respectively.
      While KB974247 speaks mainly to mismatch due to 32 vs 64 bit versions, the two here are both 32 bit versions.

      The project reference to System.Data.SqlServerCe.dll is found on path C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop. This also is currently version 3.5.8080.0. This may be what the bundled .dll files were out of sync with. Unfortunately, I no longer have access to the older version of this .dll or I would substitute it to see if that cured the issue. If it had, possibly bundling that, adding a reference to it in the references section and removing the reference to the file on the above noted, standard, path would allow this method to work. As it is, it appears there is a flaw in this scheme which leads to an incompatibility that will prevent use of an application utilizing this scheme on systems with an installed SQL ServerCE version that differs from the one complied with the application.

      Just now, I found version 3.5.5692.0 for SQL Server Compact 3.5 SP1 at the link below.

      http://www.microsoft.com/downloads/en/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9

      Is there a way to extract the System.Data.SqlServerCe.dll file from the downloaded SSCERuntime-ENU-x86.msi without running the installation?

      Eric

      • robindotnet Says:

        I doubt there’s a way to extract one file from the MSI other than installing it. Can’t you just upgrade all of them? They need to all come from the same version of SQLServer CE.

    • robindotnet Says:

      Can you please post your question in the MSDN ClickOnce forum? That way other people can chime in.
      I would check that you have the same version of .NET installed on both machines that you are targeting, and that the version of SqlCE you are using is supported on that version of .NET. That incompatibility version error is suspicious. Did you look at the KB article?

  9. monkeyshots Says:

    Is it really required to publish 7 dll files ? What do they all do ? Why is there not just one dll ?

    • robindotnet Says:

      Yes, you have to publish all of the dll’s or it will not work. Note that they are strongly named, so after the first install, it will compare the files against the ones on the server, and if you haven’t changed them, it will use the local copies instead of copying them over again. Microsoft would be be better able to handle the questions about what each one does.

  10. seekaye Says:

    Thank you for this video
    Different versions of sql compact and stupid error message and incoherent articles from microsoft were stressing me out
    this sorted it – many thanks

  11. Craig Says:

    Thankyou for this!

    You should be writing for MS online help!!!! Soooo much easier to understand

  12. makoho Says:

    Works like a charm, and saves an additional installation on the target machine.
    Thanks!

  13. shehroze ajaml Says:

    can we export data from application file folder of c# windows form app with local database (sql server compact)….?….if not then how can we export or import the data…???
    plz reply…
    i will be grateful to you…

    • robindotnet Says:

      You can import and export data to/from a SQLCE database in a CLickOnce app the same way you would do it if it wasn’t a ClickOnce app. I’m not sure what capabilities SQLCE supports — you might have to write your own code to read the table and dump it in CSV format, or read a CSV file and import it and write the records to the table.

  14. Chase Says:

    Wow, thanks for the great article, It helped me understand a deployment issue which clouded my last few days! I got an error message when my application tried to connect to the .sdf file stating “Cannot find the database file”. Turning on the “Copy Local” for the reference of System.Data.SqlServerCe solved the issue!

  15. citkar Says:

    thanks for your guide .
    is there any version problem?
    when i was deploy .mdf file it’s not work in many computer.it’s make a message like that “sql server version 612 support 650 upper ”
    please can you tell me is support any version of sql server 2005 or 2008

  16. Todde Says:

    This is perfect and helps me a lot, thank you very much.

  17. Martijn Says:

    Hi,

    I am quite new to this, but i am working on a small application wich works with sql compact 3.5. Now i`m trying to implement my application on a desktop which has no SQL server running.

    So my question is, do i have to install sql server for this to work? Or can it work without sql server installed on the target machine ?

    i hope you can help me out.

    Thnx in advance!

  18. Aishwarya Says:

    thanx for the help, vedio demo made the job much easier….

  19. How to deploy SQL Server Compact Edition 4.0? | Ask Programming & Technology Says:

    […] people have examples of it using simple xcopy deployment, but they don’t go into the details required to complete the […]

  20. Edmond Haddad Says:

    Thank You Very Much For Your Helpful Explanation.

  21. Elena Says:

    Thank you! This article just saved some hours of my life :-)!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: