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 Windows XP 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