Analyzing SQL Server Compact queries using Visual Studio 2010 Premium/Ultimate

f you are the happy owner of Visual Studio 2010 Premium or Ultimate, there is a hidden tool that allows you to run and analyze queries against SQL Server Compact 3.5 and 4.0 databases. (Support for 4.0 requires Visual Studio 2010 SP1 + the SQL Server Compact Tools update). This blog post will walk through how to access and use this “hidden” tool.

NOTE: If you only have Visual Studio Professional, you can use my SQL Server Compact Toolbox in combination with the free SQL Server 2008 R2 Management Studio Express to perform similar query analysis.

To access the tool, go to the Data menu, and select Transact-SQL Editor, New Query Connection… (The tool is part of the so-called “Data Dude” features)


In the Connect to Server dialog, select SQL Server Compact:


You can select an existing database, or even create a new one. This dialog will automatically detect if the specified file is a version 3.5 or 4.0 file.


Once connected, you can perform functions similar to what you may know from SQL Server Management Studio:



New release of ExportSqlCe SSMS add-in and command line utilities–Script to SQL Azure

The new release (version 3.5) of my scripting utilities for SQL Server Compact includes a couple of bug fixes and a single new feature: Script Schema and Data for SQL Azure.


This new feature allows you to migrate your SQL Server Compact solution to a SQL Azure database. The SQL Azure compatible script includes the following changes compared to the standard, SQL Server Compact and SQL Server compatible script:

– Primary keys are scripted before any INSERTs, as a clustered index is required on all SQL Azure tables.

– INSERTs are batched (1000 per GO), to improve performance when running the script against a SQL Azure database.

– ROWGUIDCOL property is not supported, so it is not included.

The SQL Azure script is also SQL Server Compatible, but not SQL Server Compact compatible.The matrix below illustrates your options:

Script option SQL Server and SQL Server Compact SQL Azure and SQL Server
Schema and Data



Schema and Data for SQL Azure



Schema and Data with BLOB files

X (Compact)
(requires SqlCeCmd)


Schema only



Happy scripting!