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 GoldMail 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: ClickOnce, SQLCE, SQLServerCE
April 9, 2010 at 7:13 am |
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. ??
April 16, 2010 at 5:30 pm |
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.
June 3, 2010 at 8:44 am |
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?
June 4, 2010 at 1:05 am |
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.
June 4, 2010 at 2:23 am |
I had that set correctly. I fixed it by setting up the “” section of the app.config file
July 6, 2010 at 12:05 pm |
Well that’s not helpful- why did Kevin Warners last msg lose the info inside his double quotes?
July 11, 2010 at 4:05 pm
His response was XML, and it won’t let him put that in there. I’ll work on it later. In the meantime, I’ve e-mailed it to you.
July 26, 2010 at 7:16 am |
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
July 28, 2010 at 10:26 pm |
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
September 6, 2010 at 11:30 am |
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
September 6, 2010 at 11:32 am |
You’re welcome. I’m glad it was helpful to you.
Robin
October 29, 2010 at 11:47 am |
Thnx for the tip (through twitter / delegatevoid),
will be able to use this
November 28, 2010 at 3:04 am |
You’re welcome; I’m glad it was helpful.
November 25, 2010 at 5:27 am |
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.-
November 28, 2010 at 3:17 am |
I’ve stuffed the XML into a Word document. Try downloading this and let me know if it works okay.
November 28, 2010 at 12:15 am |
[...] followed this instructions http://robindotnet.wordpress.com/2010/02/28/how-to-deploy-the-sqlserver-compact-edition-software-loc… but did not [...]
November 28, 2010 at 8:28 pm |
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
November 29, 2010 at 10:20 am |
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
January 11, 2011 at 4:27 am |
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.
January 11, 2011 at 4:25 am |
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?
February 2, 2011 at 10:15 am |
Robin,
Here is the thread I started in the SQL Server CE forum.
http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/a2ac166a-6af9-47af-a26d-a10382f1c1a4
I am hoping there is a way to develop a VS program using SQL_CE that will run on any .NET platform.
Eric
January 29, 2011 at 1:18 pm |
Is it really required to publish 7 dll files ? What do they all do ? Why is there not just one dll ?
January 30, 2011 at 12:59 am |
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.
June 30, 2011 at 2:32 am |
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
November 30, 2011 at 3:12 pm |
Thankyou for this!
You should be writing for MS online help!!!! Soooo much easier to understand
January 25, 2012 at 1:28 am |
Works like a charm, and saves an additional installation on the target machine.
Thanks!
February 11, 2012 at 10:21 am |
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…
February 12, 2012 at 8:46 pm |
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.
March 17, 2012 at 9:29 am |
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!
September 16, 2012 at 10:56 pm |
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
October 8, 2012 at 9:48 pm |
An mdf file is SQLServer Express file, not a SQLServer Compact Edition file. You have to install SQLServer Express as a prerequisite to use that file.
October 5, 2012 at 2:19 am |
This is perfect and helps me a lot, thank you very much.
October 8, 2012 at 7:25 am |
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!
October 8, 2012 at 9:57 pm |
If you are using SQLCE, you don’t have to install SQLServer for it to work. Including the dll’s with your deployment is sufficient.