Create a SQL Server Compact Edition Database Using SQL Server 2005 Management Studio

Yesterday I showed you how to create a SQL Server Compact Edition database, using Visual Studio. Today I’ll show you how to create the same database using SQL Server 2005 Management Studio.

When you launch SQL Server Management Studio, you are shown the normal “Connect to Server” dialog. First, you need to change the Server Type to “SQL Server Compact Edition”. Now in the Database File area, click on the drop down and pick “<New Database…>”.

[Picture of SSMS 01]

After picking the new database option, you should see a familiar looking dialog. Enter the name for your database, or browse for it. Like I said yesterday, I always encrypt and use a password, if you want to as well check Encrypt on and enter a password. Remember if you do choose to encrypt, you must enter a password.  

[Picture of SSMS 02]

Like yesterday, if you use a weak password you are warned. Either click no and enter a stronger password or click Yes to continue.

[Picture of SSMS 03]

Now your database has been created, and you are brought back to the original “Connect to Server” dialog you saw. SQL Server Management Studio has helpfully filled in the Database File name for you as well as the password. I clicked “Remember Password” on then clicked the Connect button.

[Picture of SSMS 04]

You are now brought to the standard SQL Server Management Studio interface.

[Picture of SSMS 05]

From here on, you’ll find things work almost identically to Visual Studio. If you right click on the Tables, you’ll see the same menu. If you pick “New Table” you will see the same dialog as yesterday. Rather than repeating myself, I’ll suggest you take a look at yesterday’s blog entry for more details on creating tables and adding rows.

I can hear the questions now: Why would you use SQL Server Management Studio over Visual Studio to create and manage your SSCE database?

It really comes down to a matter of your viewpoint. If you are a long time developer, you will be more comfortable with Visual Studio and thus want to use it to handle your SSCE database. On the other hand, if you are a DBA and have been assigned the task of managing the SSCE databases, you will be more familiar with management studio and thus want to use it as your tool of choice.

So there you go, yet another way to create a SQL Server Compact Edition database. Which tool you use is up to you!


7 thoughts on “Create a SQL Server Compact Edition Database Using SQL Server 2005 Management Studio

  1. In SQL Server 2005 Management Studio, I don’t get the option of selecting SQL Server Compact Edition in the Connect to Server dialog box.
    I didn’t install anything specific related to SQL Server Compact Edition. Just installed the components that came with Visual Basic Express.

  2. Sankar,

    At the very top / left of this page is a link named “Arcane Lessons”. Go to it and scroll down a little and you’ll find “Getting Started with SQL Server Compact Edition”. There’s multiple lessons but the first, installing the bits, is the lesson you need. It will tell you what to download to get the required dlls.

    Fair warining, I’ve not tried this with Express, so I’m not sure if SSCE works with the Express editions or not.


  3. i’ve already install sql server 2005,included visual studio 2005,without sql server compact edition
    now i want to use sql server compact edition 3.5, to create application with windows mobile 6,so i install visual studio 2008,included sql server compact 3.5
    my problem is my data in application,saved in sql server compact 3.5 in emulator, how to sychronize the data to the pc?

  4. Hi i want to create script to create\modify tables in existing SQL Server Compact Edition Database.
    What tools are required to complete this.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s