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.
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”:
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:
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!!
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
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.
I have loaded 6 million rows with SQLCE 3.5 in 91 seconds, so it all depends! http://sqlcebulkcopy.codeplex.com/
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.
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.)
Erik, thank you for the rapid response. Would datasets and datareaders work if either ASP.NET or WinForms/WPF were used?
Yes, any ADO.NET based data access will work, including Entity Framework 4
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!
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!
Craig: You can only add a 4.0 Data Source to Web Projects - it is "by design" according to the SQL Compact team :-(
Craig: You could use the workarounds I describe here: http://erikej.blogspot.com/2010/11/using-entity-framework-with-sql-server.html
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.
Don't expect things to change before release :-(
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.
Owner: It is Under Products/Database group in Web PI
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?
Products is not a group it is a section under the Product group (see top screenshot) - you are running the latest Web PI?
Got it. Thanks for your help!
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
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
May I expect SQLCE to support stored procedures and triggers?
No, use .NET code or look at LocalDB instead.
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
Cozzbie: I you need help urgently, I suggest you contact Microsoft support
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
Taiwo: As far as I know, VS 2012 only supports SQL CE 4.0
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.
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
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!
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
You're correct I linked to a VS2010 issue; I had assumed it was the same issue that I was having in VS2012.
Thanks.
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
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.
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?
daniel: Try my Bulk Copy library at http://sqlcebulkcopy.codeplex.com
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!
Bryan: Yes, only 3.5
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?
Sheetal: Replid on VS Gallery
I've followed all your instructions but still I can't see Sql server compact in my data source
How can I get it ???
@Radwan: Use VS 2012!
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))"
@aymen - please post an issue here with more details, then I will try to assist you: https://github.com/ErikEJ/SqlCeToolbox/issues/new
You can help me. I do not include sqlce toolbox in visual 2013, connection database have not sql ser er compact
Trung: pls report an issue on github with more info and screenshots
Post a Comment