Everything SQL Server Compact: SQL Server Compact Toolbox 3.2

Friday, January 11, 2013

SQL Server Compact Toolbox 3.2–Visual Guide of new features

After more than 150.000 downloads, version 3.2 of my SQL Server Compact Toolbox extension for Visual Studio 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Export from SQL Server to SQL Server Compact 4.0 in a single workflow

Previously with the Toolbox, in order to export a SQL Server database to SQL Server Compact, you had to export a script, create a new blank 4.0 database file, and then run the script against this new file. This process has been simplified to a single workflow, where you define the database and tables to export, and then specify the 4.0 database file name.

To use the feature, right click the root node in the Toolbox:

Select the server database:

If selecting a LocalDB database, type (localdb)\v11.0 in the server name box, and type the database name, do not pick the database name from the dropdown list and do not press the test connection button. This is caused by the fact that this connection dialog (that supports SQL Server authentication) uses an old API to connect to SQL Server, which only partially works with LocalDB.

Select which tables to be scripted:

And type the name of the new SQL Server Compact database file:

Once the background process completes, you can see in the Visual Studio status bar that the export completed:

As this is a new feature, please provide any suggestion for improving it…

Option to preserve Server schema names as part of Server export files

There is now a new option that enables you to keep the Server schema name (for example dbo) as part of the exported table name:

Once this feature is enabled, the resulting CREATE TABLE script will look like this:

CREATE TABLE [dbo.Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

rather than the usual:

CREATE TABLE [Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

This does not mean that SQL Server Compact now supports or understands the concept of schemas, it simply means that the table name is now: “dbo.Shippers”

Red Gate SmartAssembly integration

As I blogged about earlier, SmartAssembly is now integrated with the Toolbox for error reporting and anonymous feature usage tracking. If you encounter an error that merits being reported to me, so I can fix it in a future release, please do so.

Once an error is encountered, you will see this dialog:

If the error is not expected, click “Send Error Report”:

Enter your email address if desired, and I will contact you regarding the error, and click send:

I have also added an option to opt out of feature tracking:

Improvements

SQL Server tables displayed are properly sorted by schema.name:

Notifying user that 3.5 SP2 is required for many features to work - this has now become an issue on new Windows 8 installation with Visual Studio 2012 only, as SQL Server Compact 3.5 SP2 is not installed on these systems:

Click the red link:

As always, you can use the About box (blue question mark on the toolbar above) to see what SQL Server Compact components you have installed (notice that the Visual Studio 2012 Server Explorer does not support SQL Server Compact 3.5, so no DDEX provider in VS 2012):

Improved SQL Server DGML diagram, the DGML feature now works with AdventureWorks2012.

Improved object tree view performance, in particular for databases with many objects.