Create a SQL Server 2005 Compact Edition database visually by using SQL Server Management Studio

Microsoft SQL Server 2005 Management Studio (commonly also referred to as SSMS) is a replacement for the Enterprise Manager tool found in earlier versions of SQL Server. As well as administering SQL Server 2005 (or earlier) database instances the tool can be used to administer SQL Server 2005 Compact Edition databases that live on a PDA.

If you don’t have a version of the SQL Server 2005 developer tools installed on your desktop, you can download and install the free SQL Server 2005 Management Studio Express edition (service pack 2 or later), as this product also supports Compact Edition databases.

Creating a database

When SQL Server Management Studio is started on your desktop PC the “Connect to Server” dialog will appear.

Screenshot of SQL Server 2005 Management Studio's Connect To Server dialog

To create a new SQL Server 2005 Compact Edition database,

  1. Select “SQL Server Compact Edition” as the server type
  2. Select the <New database…> option within the Database File combo box

This will display a “Create New SQL Server Compact Edition Database” dialog, as shown below:

Screenshot of Create New SQL Server Compact Edition Database dialog

The most important option to specify is the location of the new database file. Selecting the browse button allows you to graphically select a location on your local harddrive, or an ActiveSync connected PDA. Alternatively you could type in the path manually. To create a database on the PDA you use the prefix “Smart Device” instead of a drive letter. For example “Smart Device\Program Files\MyApplication\MyDatabase.sdf”.

The encryption and sort order properties will not be discussed at this point in time, as I am planning to cover these in greater depth in future blog entries. In the mean time they can be left at their default values.

To complete creating the database we need to select the OK button. Since no password has been specified for the database, a warning that the “password is either not provided or blank” will be displayed. This can be safely ignored by pressing the Yes button.

After SQL Server 2005 Management Studio has created the database you will be returned to the “Connect to Server” dialog, however this time the Database File field is populated with the name of the newly created database.

Opening a database

Once a SQL Server 2005 Compact Edition database has been created it can be opened within SQL Server Management Studio 2005 by using the same “Connect to Server” dialog discussed above (also accessible via the File, Connect Object Explorer… menu option).

To open an existing SQL Server 2005 Compact Edition database,

  1. Select “SQL Server Compact Edition” as the server type
  2. Specify the database filename within the Database File combo box

Recently accessed databases are available in the Database File drop down combo box. To browse the file system to graphically select another database file you can select the <Browse for more…> option within the combo box.

Creating Tables

One of the advantages of using SQL Server Management Studio is its rich support for the management and manipulation of databases and their contents. When targeting SQL Server 2005 Compact Edition databases many Management Studio features are disabled, but the subset provided is still pretty useful.

Screenshot showing how to create a new table via the Object Explorer pane

Within the Object Explorer pane you can view the various objects within your database. To create a new table, you can right click on the Tables node within the Object Explorer and select “New Table”. This will display a dialog which allows you to specify the details of the new table, such as its name and the properties of the columns it contains. Using this view also allows you to rename and delete database objects.

Alternatively you can interact with the database via Transact-SQL queries. The SQL Server 2005 Compact Edition database engine supports a subset of the query language supported by SQL Server 2005 on the desktop. To create a new query select the “New Query” toolbar button (Ctrl + N is the keyboard shortcut), and enter the query in the new window that appears. Pressing F5 or the execute toolbar button will then execute the query and show its results.

To create a new database table we could execute the following query (for more details see the CREATE TABLE (SQL Server Compact Edition) section of SQL Server 2005 Compact Edition Books Online).

CREATE TABLE Photos
(
  id INT NOT NULL IDENTITY PRIMARY KEY,
  name NVARCHAR(100) NULL,
  filename NVARCHAR(256) NOT NULL,
  description NVARCHAR(2048) NULL
)

By default after executing such Data Definition Language (DDL) statements changes in the database schema are not reflected in the Object Explorer pane. In order for the new table to show up you need to right click and select “Refresh”.

3 Responses to “Create a SQL Server 2005 Compact Edition database visually by using SQL Server Management Studio”

  1. Jon Willis says:

    It should be noted that SSMSE does not support connections to SQL Server Compact and cannot be used for mobile development.

    http://msdn.microsoft.com/en-us/library/ms365247(SQL.90).aspx – “In SSMSE, Object Explorer only supports Database Engine instances. The Connect to Server dialog box prevents connecting to other services, such as Analysis Services.”

  2. Hi Jon,

    Have you actually tried it?

    A lot of the screenshots within this blog have been created using SQL Server Management Studio 2005 Express Edition due to that being the only thing installed within one of the virtual machines I regularly use while blogging.

    I admit the documention page you reference is unclear, but I think that is more a documentation issue vs the feature not being present. For example the first sentance of the page explictly mentions that working with SQL Server Compact Edition databases is supported.

    I do know that you need atleast Service Pack 2 of SQL Server Management Studio 2005 Express Edition, as support for Comapct Edition databases wasn’t a supported scenario in earlier service packs. Is it possible you are running a pre SP2 version of the product?

    As for SSMSE 2008 it’s a listed bullet point on their product page at http://www.microsoft.com/express/sql/default.aspx.

    Let me know if you have difficulty getting this working. I much perfer the features of SSMS over the database tools built into visual studio etc.

    Hope this helps,
    Christopher

  3. Mark C says:

    Thanks great post…. I have been gathering information for getting SQL Compact working and Server Management Studio was exactly what I was looking for.

Leave a Reply