Tuesday, December 14, 2010

Visual Studio Tools for SQL Server Compact 4 now available

The “Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 CTP2” (nice shot name) has just been released. It can be installed from the Web Platform Installer 3.0 or directly from here. UPDATED link points to RTM tools.

image

After installing the SQL Server Compact 4 tools, you now have the same Server Explorer features that you currently have for SQL Server Compact 3.5 SP2.

Here are the Data Sources available from “Add Connection”:

image

The Server Explorer tools provides features such as:
Create, Upgrade, Shrink, Repair, Password protect database files.
Define tables, browse/edit/query data, drop tables, manage relationships, manage indexes.

You can read more about the expected release of SQL Server Compact 4 and the related tools here, and there is more information about VS 2010 SP1 here.

My SQL Server Compact Toolbox add-in has also just been updated with release 1.7 to include the new SQL Server Compact 4 Data Sources from Server Explorer. Just to summarize, the Toolbox supplements the Server Explorer tools, and includes the following features:
- Script tables, including data
- Script entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases (in SQL Server Compact T-SQL format)
- Import to SQL Server Compact from a SQL Server 2005/2008 database or a CSV file
- Migrate for SQL Server to SQL Server Compact
- Migrate from SQL Server Compact to SQL Server and SQL Azure
- Basic, free form query execution (not constrained like the one in Server Explorer)
- Parse SQL scripts
- Rename tables
- Create database diff scripts
- Display graphical estimated execution plan (with SSMS 2008 or VS 2001 Premium)
- Generate detailed DGML files for visualizing table columns and relationships (requires VS 2010 Premium or higher to view)
- Create and manage Merge Replication subscriptions
- Community driven CodePlex project, open source

UPDATE: The SQL Server Compact team has posted a short blog about the VS Tools.

46 comments:

Tristan said...

Hello Erick:
I would like to know if you have some results of:
1) Maximun database size that SQL CE 4 supports
2) The speed compared to SQLite.
Thanks!!

ErikEJ said...

1) Max database size for SQL Server Compact is 4 GB per database file (your application could connect to more files)
2) I have not done any testing, you could do your own based on these scripts, provided you have SQlite installed: http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison

Tristan said...

Thanks Erick, I made some tests with SQL CE 4 and SQLite for .NET (http://sqlite.phxsoftware.com/).
I loaded more than 6 million records under the same conditions with the next results:
SQLite: loaded in 2 minutes with final size of 406MB
SQLCE4: loaded of 11 minutes and final size of 526MB
Best regards.

ErikEJ said...

I have loaded 6 million rows with SQLCE 3.5 in 91 seconds, so it all depends! http://sqlcebulkcopy.codeplex.com/

Rod Mac said...

If I install a SQL Compact 4.0 database on a client machine and then on a website, can the data access technology be the same (regardless of whether my SL app is OOB or in browser) and what common data access technologies will be supported if there are more than one? Thank you.

ErikEJ said...

Rod: Silverlight cannot access a local SQL Server Compact Database. For Silverlight some type of data acesss over http is required (Web Service, OData, WCF etc.)

Rod Mac said...

Erik, thank you for the rapid response. Would datasets and datareaders work if either ASP.NET or WinForms/WPF were used?

ErikEJ said...

Yes, any ADO.NET based data access will work, including Entity Framework 4

Rod Mac said...

I must try this EF4 stuff. I hope one day SQL Server Compact will be a true (disconnected) mobile solution in conjunction with SL because I can't see WPF running on slates/tablets - or should I say my expectation is that SL will leapfrog WPF to become a cross Windows (not cross browser) UI. Cheers Erik!

Unknown said...

Eric,

I wonder if I am missing a step. I am running VS2010 Professional. I installed VS2010 SP1 Beta, SQLCE 4.0 CTP2 and VS Tools for SSCE4 in that order). When I go to add a DataSource to my project, I do not see "Microsoft SQL Server Compact 4.0" (although 3.5 is still there). If I go to Add New Item I can select "SQL Server Compact 4.0 Local Database" but I get an error informing me that this database version is not supported by the project type.

I realize these are all CTP and Beta versions but it seems like I am missing something somewhere. I am running Windows 7 x64 if that is relevant.

Thanks!

ErikEJ said...

Craig: You can only add a 4.0 Data Source to Web Projects - it is "by design" according to the SQL Compact team :-(

ErikEJ said...

Craig: You could use the workarounds I describe here: http://erikej.blogspot.com/2010/11/using-entity-framework-with-sql-server.html

Unknown said...

Erik,

Thanks for the quick reply. That's an unfortunate limitation. Hopefully the full release isn't too far off and we'll be able the have full use of SQLCE 4. Until then, I'll give the workaround a shot.

ErikEJ said...

Don't expect things to change before release :-(

Owner said...

I just applied VS2010 SP1 RTM on my PC. I still can't create new SQL CE 4.0 database. Where can I download Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0? When I run Web Platform Installer, it does not show this item.

ErikEJ said...

Owner: It is Under Products/Database group in Web PI

Owner said...

Erik, I still don't see SQL CE Tools in the Web PI's group you mentioned. There is Products group but no Database group.

To be exact, I installed VS2010 SP1, then I installed SQL CE 4.0 runtime, then launched Web PI. I see VS2010 SP1 as one of the items listed in Products group but no SQL CE 4.0 Tools.

Would you be able to advise on this?

ErikEJ said...

Products is not a group it is a section under the Product group (see top screenshot) - you are running the latest Web PI?

Owner said...

Got it. Thanks for your help!

Josue Panameño said...

Hi!!! I have a problem when I try to install this tool, ever appears a messagebox with the text "To run this application, you must first install Microsoft Visual Studio 2010 SP1..." but I already installed the SP1.
Help me please!!! I spent a week reinstalling everything :S

ErikEJ said...

These 2 links have installtion troubleshooting advice: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=dda9dc83-f59a-4eca-b792-dd1d9629b6e7 (the readme) and http://blogs.msdn.com/b/sqlservercompact/archive/2011/03/15/sql-server-compact-4-0-tooling-support-in-visual-studio-2010-sp1-and-visual-web-developer-express-2010-sp1.aspx

gireesh said...

May I expect SQLCE to support stored procedures and triggers?

ErikEJ said...

No, use .NET code or look at LocalDB instead.

Cozzbie said...

There's nothing concrete in any of the links provided that settles this problem. I have the same issue and would please like a detailed walk-through to solving it because a maintainance project just landed on my table and I need this to work. Help need please. Thanks

ErikEJ said...

Cozzbie: I you need help urgently, I suggest you contact Microsoft support

Unknown said...

BTW do you know how I can get tool support in VS 2012 for SQL Server Compact 4 database? I continue to see "SQL Server Compact 3.5 local database" in VS 2012 when I want to add a "Local Database" project item.

Thanks.
Taiwo

ErikEJ said...

Taiwo: As far as I know, VS 2012 only supports SQL CE 4.0

Unknown said...

Thanks for the quick feedback Eric.

I'm using VS 2012 Premium with Update 1. When adding (and you select) a “Local Database” as a project item in the "Add new Item" dialog, in the top right showing a description of the selected item it states “An empty SQL Server Compact 3.5 database for local data”. Perhaps it's a mislabeling in VS 2012...what does your VS 2012 UI show? I'd send a screenshot if I could.

ErikEJ said...

Taiwo: weird - Mine simply says: "An empty SQL Server Compact Edition database for local data" - somehow your install is broken... And only 4.0 is supported by VS 2012

Unknown said...

Thanks Erik. However, it seems I'm not the only one with the issue. See http://stackoverflow.com/questions/10150537/sql-server-compact-4-0-does-not-show-in-visual-studio-2010.

Best regards!

ErikEJ said...

Taiwo: But that link Refers to VS 2010, and you talk about VS 2012 (so far) - for VS 2010 issues, see this link http://blogs.msdn.com/b/sqlservercompact/archive/2011/03/15/sql-server-compact-4-0-tooling-support-in-visual-studio-2010-sp1-and-visual-web-developer-express-2010-sp1.aspx

Unknown said...

You're correct I linked to a VS2010 issue; I had assumed it was the same issue that I was having in VS2012.

Thanks.

Wireless Task said...

I want to give an option in my standalone vb.net datbase program to empty the database (after making backups)
all this to avoid reaching them 4gb, they can then start fresh, like after every year or so..
Is this the usual workaround for standalone database programs? or is there any other solution?

thank you

ErikEJ said...

Wireless: Yes, that would be an option, or store BLOB data in file system, or use SQL Server Express (limit 10 GB) or use more database files.

Unknown said...

Hi my name is Daniel.
I use SQL Server Compact 3.5 on WindowsMobile 6.1. I have written a Program in VisualBasic 2008. Intelegent Device. There is a WindowsForm with a Button, on press import the file.csv. I will import csv to sdf. In this moment I use StreamReader than I split every Value and set it in a insert query. It works but it is toooo slow. 3000 thousand entrys takes 15 minutes. Can you help me?

ErikEJ said...

daniel: Try my Bulk Copy library at http://sqlcebulkcopy.codeplex.com

Brayanadams said...

Hello Erik,

I was trying to use SQL Server Compact ToolBox to create a merge replication subscription for SqlCe 4.0, but I only see "Tables" folder in my database, no "Subscriptions" folders as I have seen in some screenshots.

Is that only allowed to SqlCe 3.5?

Thanks!

ErikEJ said...

Bryan: Yes, only 3.5

Unknown said...

Hi Erik - This is a great tool and is helpful in my work

I have couple of questions on data diff feature.
1. I used this option and see the schema changes captured, I am unable to capture the delta data changes. Is there an option for that?

2. I wanted to convert from .mdf to .sdf file. On choosing to do this for both schema and data, it generated multiple files with 18528KB size. Is there a way to restrict the script to a single file?

3. Since the last run of the script, is there a way to find out the differential updates and generate only the delta script other than doing the .sdf file compare?

ErikEJ said...

Sheetal: Replid on VS Gallery

Unknown said...

I've followed all your instructions but still I can't see Sql server compact in my data source
How can I get it ???

ErikEJ said...

@Radwan: Use VS 2012!

aymen said...

i'm always getting an error saying "an error occurred while retrieving the information from the database: The specified type is not supported by this selector" while trying to create a dataset for a report viewer
i'm using your plug in "SQL Server Compact 4.0 (Simple by ErikEJ) (SQL Server Compact 4.0 (Simple by ErikEJ))"

ErikEJ said...

@aymen - please post an issue here with more details, then I will try to assist you: https://github.com/ErikEJ/SqlCeToolbox/issues/new

Unknown said...

You can help me. I do not include sqlce toolbox in visual 2013, connection database have not sql ser er compact

ErikEJ said...

Trung: pls report an issue on github with more info and screenshots