Configuring Visual Studio 2005 for use with SQL Server 2005 Compact Edition
A number of people have been a bit confused how to get SQL Server 2005 Compact Edition (also known as version 3.1) working with Visual Studio 2005. The fact that we enabled a new product mid-steam within a Visual Studio ship cycle was quite an accomplishment, but there were a few cracks that people fell in, so let me do a quick recap of what you need to do. Note that the order isn't actually all the important. We've designed the packages to be installed in any order.
First, some pre-requisites
Visual Studio Standard, Pro, Enterprise or above required - Visual Studio Express SKUs will not work with SQL Server Compact Tools
Because SQL Server Mobile (predecessor to SQL Server Compact) was shipped as part of the Smart Device Programmability, there was no free Express SKU that targeted device programmability. You can use SQL Server Compact in the Express products, but you will be limited to code scenarios. The tooling experiences are limited to the standard and above SKU's.
I'm happy to say that Visual Basic & Visual C# 2008 Express SKU will include SQL Server Compact 3.5 as part of their default experience. In addition, post B2, the SQLce runtime and tools will always be installed, so you won't have to go through this experience.
Smart Device Programmability must be installed to enable SQL Server Compact tools
If your installed VS 2005 with the default options, you can skip this step as you already have Smart Devices installed.
If you chose to customize the VS setup, then you may likely not have Smart Devices installed, so please follow these steps to confirm you have what you need before installing VS SP1.
1: Verify Smart Devices for Visual Studio are installed
The easiest way to verify that Smart Devices are installed is to simply create a new project, and see if you have Smart Devices as an option for creating new projects.
2: Verify SQL Server Mobile tools for Visual Studio are installed
The next thing you'll want to confirm is the SQL Server Mobile tools for Visual Studio are installed. The SQL Server Compact Tools for Visual Studio SP1 package is a patch to the existing tools, so unless you have the SQL Mobile Tools package installed, you'll be unable to update to SQL Server Compact.
If you've attempted to install the SQLce Tools and received this warning, this is why:
- Open the Control Panel and launch Add or Remove Programs in WindowsXP or Programs & Features in Windows Vista
- Look for an entry named: Microsoft SQL Server Mobile Tools for Visual Studio
The exact title may be slightly different. While writing this I couldn't find any machines that don't already have VS SP1 installed. - If you have this entry, proceed, as you're fine
- If you don't have this entry, but you do have Smart Devices, then you likely uninstalled the SQL Mobile tools. Re-run VS setup and chose to reinstall the Smart Devices option
2a: Installing Smart Devices
- Run the VS 2005 Setup
- Chose Customize
- Under Visual Basic, and/or C#, choose Smart Device Programmability
This will install the Smart Device Templates and the tools for SQL Server Mobile - the pre-requisite for upgrading to SQL Server Compact
1: Install Visual Studio 2005 Service Pack 1
You'll first want to install VS 2005 SP1. Vista developers should use this update specific to running VS on Vista. In addition to all the other benefits of SP1, the benefits for SQLce include:
- Server Explorer "Add Connection" refers to SQL Server Compact Edition, instead of SQL Server Mobile
- Auto addition of the SQLce Click Once bootsrapper when a .sdf file is added to your Windows Forms project
- Newly created connection strings for SQLce databases will use the |DataDirectory| macro easing directory management of data files on each end users machine
Just installing VS 2005 SP1 doesn't actually get you the updated SQLce 3.1 engine. If you just install VS SP1 you may think you have SQLce installed as the Add Connection dialog will list SQL Server 2005 Compact Edition, but it's just a resource string that's part of Visual Studio. You'll need these next two packages as well. Navigate to www.Microsoft.com/SQL/Editions/Compact/Downloads.mspx and you'll see several additional packages available.
2: Install SQL Server 2005 Compact Edition Embedded Engine
The first most important one is the actual SQL Server 2005 Compact Edition engine. This is version 3.1 that unlocks the power of SQL Mobile for all Windows desktop operating systems. Note, this is not used for the Windows Mobile platform. It's the desktop runtime.
- Full support for all current Windows Desktop operating systems
- Enhancements allowing private deployment of the SQLce engine, or central/gac'd installation
- Addition of the |DataDirectory| macro for locating where your applications data directory exists on each users end machine
- Some minor performance enhancements that take advantage of the desktop environment compared to the more constrained Windows Mobile environment
3: Install SQL Server 2005 Compact Edition Tools for Visual Studio 2005 Service Pack 1
Once you have the unlocked engine, you'll want to get the updated tools. This package includes bug fixes and new deployment features for SQLce under Click Once including the following:
- Updates the table editor, database creation and other related SQLce dialogs
- Addition of a Click Once bootsrapper for pre-reqing SQL Server Compact when deployed with Click Once
4: Install SQL Server Compact Documentation
Lastly, you may want the updated documentation for the newly enabled scenarios
A quick verification
With these steps you should now have SQL Server 2005 Compact Edition full installed and ready to go. To verify everything went well, you can check Add Remove Programs for the following entries:
- Microsoft SQL Server 2005 Compact Edition [ENU]
- Microsoft SQL Server 2005 Compact Edition Books Online [ENU]
- Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005 [ENU]
The [ENU] suffix indicates these are the English versions. Server Tools is an optional component required when using Merge Replication or Remote Data Access (RDA)
You can also check to see if the following directory exists:
\Program Files\Microsoft SQL Server Compact Edition\v3.1
This directory includes the native runtime and managed provider. Note, you no longer need to manually add a file based reference to the System.Data.SqlServerCe.dll. When you install the SQL Server Compact Tools for Visual Studio 2005 SP1, a entry is made in the registry enabling the Add Reference dialog to properly display the System.Data.SqlServerCe reference in the .NET tab.
Happy coding,
Steve
Comments
Anonymous
August 06, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/08/06/Anonymous
August 06, 2007
The comment has been removedAnonymous
August 07, 2007
While I am not (yet) much of an expert on databases, I was excited to see Steve Lasker post instructionsAnonymous
August 08, 2007
Bill Vaughn graciously gave me a bunch of feedback on some gotchas that he and others have ran into. So, I’ve updated the post with several images, verification steps, qualifiers for Express and the Visual Studio SP1 for Vista link. Thanks again BillAnonymous
September 03, 2007
Thanks for the article. I have a problem though. I've followed all the steps, but in the Add Reference screen I still see the SqlServerCe dll with version 3.0.3600.0 even though the actual file version is 3.0.5300.0! Then when I add the reference it tries to reference 3.0.3600.0. I installed Sql Compact using the cab file and I get a runtime error because it looks for version 3600, but I only have 5300 on my device. Any idea how I can get rid of the 3.0.3600.0 reference in Visual Studio?Anonymous
September 03, 2007
Hi Jeffery, The solution I described is actually for desktop scenarios. The SQLce 3.1 release was really the 3.0 SQL Mobile release for the desktop. There wasn't anything substantial, other than some bug fixes for the device version. For devices, you don't want to manually reference the files in the c:program files directory as those are for desktop, and will vconfuse the fx runtime trying to resolve the desktop managed provider and desktop ado.net stack. SteveAnonymous
September 03, 2007
Hi Steve, I thought I knew I understood how it worked, but now I'm not so sure anymore :). When I installed the Sql Compact tools, it also updated the cab files in c:program filesmicrosoft visual studio 8smartdevicessdksql servermobilev3.0 etc. When I use these cab files to deploy, it deploys the Sql Compact Core and not Sql Mobile 2005 anymore. And because the reference is still to version 3600 it fails. I could just keep deploying Sql Mobile 2005 to the device and not upgrade. Are you saying there's no substantial differences between the two for devices? Because then I might as well stay with 2005. But still, this confuses me a lot. Thanks, JeffryAnonymous
September 09, 2007
Hello, I have smart devices programing installed (I can choose any smart device application project from visual studio create project option), I have also SP1 installed for visual studio, but I still have error that tools can not be installed. I've also rerun setu and checked if smart devices are selected and everything is correct. But I still have that error. Have you any idea why it can happen? Thanks MarcinAnonymous
September 12, 2007
Hi Marcin, Can you verify you have the SQL Server Mobile tools installed? You should see it in Add/Remove programs. The SQL Server Compact Tools are an update to the SQL Mobile tools. So, if you uninstalled the SQL Mobile tools, or somehow didn't get them installed, the update to SQL Server Compact tools won't work. SteveAnonymous
September 30, 2007
Sql server Ce 3.1 .. istallazione faticosa.Anonymous
September 30, 2007
Stamane debbo fare un lavoro urgent per un cliente, sviluppo su mobile e necessità di sql server, vadoAnonymous
October 02, 2007
Thank you so much! I was tearing my hair out trying to get it to work.Anonymous
December 07, 2007
Is it possible to use the SQL Server 2005 Compact Edititon 3.5 with Visual Studio 2005 ? And if yes pleas let me know how. Thanks Regards StephanAnonymous
December 08, 2007
Hi Stephan, Yes, you can use SQL Server Compact 3.5 with Visual studio 2005... sort of. ...meaning, you can use the code scenarios, add references, instance objects, etc. However, tooling for SQL Server Compact 3.5 is limited to VS 2008. SteveAnonymous
December 17, 2007
Hello, You are saying SQL Server CE 3.5 can be used with VS 2005? The reason is that the mobile I am deploying too has its own SDK (C++) that can only integrate with VS2005 (no VS2008 upgrade as yet); What do you mean by tooling? Can I use SQL Server 2005 Management Studio to access CE 3.5 as the "tooling".Anonymous
December 18, 2007
Hi Ekoseos, What I mean by "tooling" is the VS Designers including the DataSet designer, Server Explorer and Connection Dialogs. Basically any tool (VS or Managmenet Studio) that has to display UI to communicate with SQLce will only be enabled as described: VS/Management Studio 2005 = SQL Server Compact 3.1 VS/Management Studio 2008 = SQL Server Compact 3.5 Hope that helps explain, SteveAnonymous
January 02, 2008
So to work with the DB files for SqlCE (3.5) we need to install the Express version of C# (as an example) and edit them from there, correct? I guess you might tell us just to upgrade to VS 2008 :) ?Anonymous
January 09, 2008
Hi Peter, ummmm, yes. Due to tight schedules and resource constraints, we were unable to support multi-targeting of tooling. ...but, we did get the express tools integrated with SQLce 3.5, so you don't have to purchase the professional product just to get started. SteveAnonymous
January 22, 2008
Can SQL Compact edition work with Visual Studio Tools for Office. I thought that VSTO sat above Visual Studio Standard and along-side VS Pro but the SQL Ce tools are not available during the installation process.Anonymous
February 13, 2009
The comment has been removedAnonymous
April 25, 2009
That is the point > VS/Management Studio 2005 = SQL Server Compact 3.1 VS/Management Studio 2008 = SQL Server Compact 3.5 Thanks...Anonymous
April 25, 2009
And you are totally right that, The SQLce 3.1 release was really the 3.0 SQL Mobile release for the desktop...Anonymous
October 02, 2009
The comment has been removedAnonymous
December 21, 2011
Hi all, I have a doubt that, when we install windows setup file in clients machine where will be the database installed? I mean I have included the .sdf file in prerequisities. Thanks in advance.