How Can I Completely Delete a CE Table?

I have an application created with VS 2005 and VB 2005 using a Compact Edition Database. I created some tables that I no longer use or want. How can I permanently delete them from the database?

Connect to the database a run .ExecuteNonQuery:

Code Snippet

DROP TABLE tablename

You may have to run

Code Snippet

ALTER TABLE tablename DROP CONSTRAINT constraint_name

first, if you have foreign key constraints.

How can I compact MS SQL 2000 Database?

Now my database is contain a big size and still grow up. I think may I can compact it to small size like ms-access.
How can I compact MS SQL 2000 Database?You can shrink the databse by any of the following option.

1.You can use the syntax dbcc shrinkdb <databasename> on query analyzer

or

2.selse sqlserver -> enterprise manager ->select the database -> right clik this and select the option shink

for more information u can use books online shrink database option|||To prevent the database from unwanted growth, you can set the 'Auto shrink' option (in Enterprise Manager, right click on your database, choose properties, select options tag and check the auto shrink option).

How can I access SQL Server Compact Edition with SQL Server Management Studio Express?

Hello,

I just installed SQL Server Compact Edition, since I am considering using it instead of SQL Server Express for a local database in my application. The documentation mentioned that I could use SQL Server Management Studio Express to connect to the Compact Edition and create and manipulate databases.

To try to connect, I run Management Studio and bring up the "Connect to Server" dialog. Unfortunately, the pull-down list of "Server name"s does not include the SQL Server Compact Edition server. I do not know how to type in the server name manually, so I cannot connect.

To install Compact Edition, I downloaded it and ran "SQLServerCE31-EN.msi". This installed, and I assume registered, a number of DLLs in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\".

Is there perhaps an additional step that I left out to complete the installation?

Might I need an upgrade to some other components? My Management Studio Version is:

- Microsoft SQL Server Management Studio Express 9.00.2047.00

I would appreciate any help you can provide.

Thank you,

WTW

The support for SQL Server 2005 Compact Edition was added in Microsoft SQL Server Management Studio Express (SSMSE). SSMSE is available for download at:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

SSMSE shipped with SQL Server 2005 SP2. SQL Server 2005 SP2 information is at

http://technet.microsoft.com/en-us/sqlserver/bb426877.aspx#3

Regards

Ambrish

|||

WTW,

I've written a blog post about this, you can see it at http://arcanecode.wordpress.com/2007/04/11/create-a-sql-server-compact-edition-database-using-sql-server-2005-management-studio/ . I've also written an article that describes all the components you'll want to install to make everything work right, http://arcanecode.wordpress.com/2007/04/24/sql-server-2005-compact-edition-important-component/ .

You might also want to check the Arcane Lessons page for more SSCE tutorials.

Arcane

|||

Thanks for your reply. I uninstalled SSMSE and downloaded and installed it again. However, I still cannot access a SQL Server 2005 Compact Edition database.

When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it. I've tried most of the menus and buttons, and I cannot find a setting for this.

I am missing some other component or configuration option?

Thanks for your help.

WTW

|||

Thanks for your very helpful reply. Thanks to your blog, I now have all of the proper components installed to make Compact Edition work properly. Your blog was the only place where I have found a complete list of all of the necessary components.

I have even purchased the E-book that you recommended, and it is very helpful.

However, I still am not able to access a Compact Edition database through SQL Server Management Studio Express. When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it to Compact Edition or something similar.

The E-book demonstrates how to access a Compact Edition database through Management Studio. However, it shows a "Connect" menu in Management Studio that does not exist on my version. According to the E-book screen shots, the "Connect" menu should allow you to select "SQL Server Mobile", which will then bring up a "Connect to Server" dialog that shows a "Server Type" of "SQL Server Mobile". The next line allows selection of a "Database file" instead of a "Server name".

My version of Management Studio does not have the "Connect" menu. I even tried reinstalling Management Studio from the Microsoft website. However, I discovered that I already had the latest version, 9.00.2047.00.

I would appreciate any additional help you can provide.

Regards,

WTW

|||In typical microsoft style, the documentation is horrible on this. I was only able to get it to work by installing SP2 of SSMSE (of which there is no mention on the regular SSMSE download page) at:

http://www.microsoft.com/downloads/details.aspx?familyid=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en

and the SQL CE SDK found at:

http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en

You will know you have SP2 of SSMSE installed if you version is 9.00.3042.00
|||

SP2 did the trick. Thank you so much.

Regards,

WTW