Tuesday, March 29, 2011

Snapshot Synchronization with SQL Server Compact 4.0

It has been made clear by the SQL Server Compact team that version 4.0 does not support Sync technologies like Merge Replication and Sync Framework.

Imagine a scenario where you have a desktop or web app, where the data is mix of the user’s own operational data and lookup data from a central data source. How would you enable this using SQL Server Compact 4.0?

RDA (Remote Data Access) is a simple and proven technology, that allows you to “Pull” an entire table from a SQL Server over http (take a snapshot), without much coding effort. I decided to test if this technology would still work with 4.0, and lo an behold, it does. (Notice that RDA support will be removed from SQL Server Compact in a future release)

That means that you can pull down lookup data from a central server to your SQL Server Compact 4.0 database file as needed. I configured my SQL Server Compact ISAPI agent DLL according to my post here, and was able to pull a table to my version 4.0 database file. (Remember to DROP the local table before you Pull).

Here is the small amount of code required (Console application):

using System;
using System.Data.SqlServerCe;

namespace TestRDA40
class Program
static void Main(string[] args)
// Connection String to the SQL Server
string rdaOleDbConnectString = "Data Source=(local);Initial Catalog=ABC; " +
"User Id=xxx;Password=yyy";

// Initialize RDA Object
SqlCeRemoteDataAccess rda = null;

// Try the Pull Operation
rda = new SqlCeRemoteDataAccess(
@"Data Source=C:\data\sqlce\test\nw40.sdf");

rda.Pull("ELMAH", "SELECT * FROM dbo.ELMAH_Error", rdaOleDbConnectString);
catch (SqlCeException ex)


Hope you find this useful.

Monday, March 21, 2011

SQL Server Compact Toolbox 2.1–Visual Guide of new features

Celebrating more that 20.000 downloads, version 2.1 of my SQL Server Compact Toolbox extension for Visual Studio 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.

Generate an Entity Data Model (EDMX) in the current project in any applicable project (WPF, WinForms, Class Library)

As you may know, the tooling support for 4.0 is somewhat limited, and therefore you have to use various workarounds to generate an EDMX for a version 4.0 database in projects that are not Web based. Version 2.1 of my Toolbox adds the ability to run edmgen2 via a friendly dialog to generate and include an EDMX file in any applicable project type.



(I will blog later about how I did this, using the edmgen2 project)

Remove invalid connection definitions from the Toolbox (and Server Explorer)

As you move SQL Server Compact files around and upgrade version 3.5 files to version 4.0, many of the connection entries in Server Explorer / the Toolbox become invalid. Therefore, I have added a menu option to remove broken connections.


Option to display binary data in result


Preventing invalid values in Max Database Size textbox

By using the NumericUpDown control from the Extended WPF Toolkit, it is no longer possible to enter invalid max database size values.


For a similar overview of version 2.0 features, see this blog post.

Updated release of standalone version.

The standalone version for users not running Visual Studio 2010 Pro or higher, is also available in a new release, the main feature being, that it is now a single .exe, with only .NET 4.0 and SQL Server Compact 4.0 RTW required to be present on the system.

As always, please provide feedback, suggestions and reviews at the CodePlex site: http://sqlcetoolbox.codeplex.com

Thursday, March 10, 2011

Visual Studio 2010 Service Pack 1 with support for SQL Server Compact 4.0 released

Visual Studio 2010 SP1 is now available to MSDN subscribers, and includes support for managing SQL Server Compact 4.0 databases in Server Explorer. Notice that you must download the SQL Server Compact tools in order to enable the new provider via Web Platform installer, as I blogged about earlier.

UPDATE: The SQL Server Compact team has posted a blog about the new tools support in SP1, including
- Server Explorer and Query Designer
- Transact-SQL Editor (for VS Premium and higher users)
- Designers in the VB and C# ASP.NET Web application or web site projects
- Click once deployment

Interesting tidbits from the readme:

2.1.4. Visual Studio must be restarted after Visual Studio 2010 SP1 tooling for SQL Server Compact (Compact) 4.0 is installed

When a project that contains a Compact 4.0 .sdf file is opened and neither Compact nor Visual Studio Tools for SQL Server Compact 4.0 (Tools for Compact) is installed, Visual Studio 2010 SP1 asks whether the user wants to install the missing component. If the user says "yes," Web Platform Installer downloads and installs either Compact or Tools for Compact.  However, if the user then tries to open the .sdf file, an "unknown error" occurs because Tools for Compact 3.5 is already loaded and it cannot be unloaded and replaced by Tools for Compact 4.0 until Visual Studio is restarted.

To resolve this issue:

Restart Visual Studio after you install the SQL Server Compact 4.0 tooling.

And from the KB article:

SQL Server CE 4 support

Visual Studio 2010 SP1 enables you to manage Microsoft SQL Server Compact 4.0 SDF files in Solution Explorer and in Server Explorer in the context of web projects. Additionally, Visual Studio 2010 SP1 enables you to use SQL Server Compact 4.0 together with the Microsoft ASP.NET Web Forms in a SQL data source control.
Note SQL Server Compact 4.0 is not included in Visual Studio 2010 SP1, and you must download it separately. For more information, visit the following blog:

VS 2010 SP1 and SQL CE (http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx)

Web PI integration

Support for the Microsoft Web Platform Installer (PI) is added in Visual Studio 2010 SP1 as follows:
  • A new toolbar is added that allows for quick access to start the latest version of Web PI. Additionally, if the Web PI was not installed, it is downloaded first.
  • When you open web projects that use IIS 7.5 Express, SQL Server Compact 4.0, or ASP.NET Razor syntax, a dialog box appears that offers to download and install these components by using Web PI if the components are not already installed.

Deployable dependencies

Some new technology components that are added in Visual Studio 2010 SP1 can be bin-deployed together with an application. Then, you can use the components even when you deploy the application to a server on which those components are not installed. A new dialog box is added in Visual Studio 2010 SP1 that makes it easier to add these deployable dependencies to the web project. To access the dialog box, right-click the project in Solution Explorer, and then select Add Deployable Dependencies. The following components are supported:
  • ASP.NET Web Pages that use Razor Syntax
  • SQL Server Compact 4.0
For more information, visit the following blogs:

BIN Deploying ASP.NET MVC 3 with Razor to a Windows Server without MVC installed (http://www.hanselman.com/blog/BINDeployingASPNETMVC3WithRazorToAWindowsServerWithoutMVCInstalled.aspx)
How to Bin Deploy SQL Compact Edition 4.0 and Razor web projects (http://blogs.msdn.com/b/webdevtools/archive/2011/01/06/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx)

        Monday, March 7, 2011

        Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

        You can use the latest version of the msdeploy command line utility, which is included with WebMatrix, to migrate a SQL Server Compct database to SQL Server, or simply generate a script (in SQL Server T-SQL dialect) of the entire database, both schema and data.

        On my PC, the msdeply.exe version 2.0 is located at C:\Program Files (x86)\IIS\Microsoft Web Deploy V2

        Below are some sample usages involving a SQL Server Compact 4 source database:

        msdeploy -verb:sync
        -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true 

        This will create a script of the entire database in SQL Server T-SQL dialect, both with schema and data.

        msdeploy -verb:sync -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true,scriptdata=False -dest:dbFullSql="c:\data\scriptnw.sql"

        This will create a script of the entire database in SQL Server T-SQL dialect, schema only.

        msdeploy -verb:sync -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true,NoCollation=true,SchemaQualify=false -dest:dbFullSql="c:\data\scriptnw.sql"

        This will create a script of the entire database in SQL Server Compact compatible T-SQL dialect, both with schema and data.

        Unfortunately, the script generated on my machine contains the following statement, which is not valid T-SQL:

        INSERT INTO [Order Details]([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10001,25,9,8,30,0,15)

        7 values to 5 columns – it is a localization issue, that I have reported  here, it can be fixed by changing the decimal point in regional settings.

        You can read more about the supported scripting options here.

        You can read more about the dbFullSql provider and it’s support for SQL Server Compact 4.0 here.