SQL Compact data and schema script utility (ExportSqlCe) version 2.5 released

Version 2.5 of my CodePlex SQL Server Compact schema and data script toolset has been released. Go get it!

The project now includes a command line utility to script a SQL Server 2005/2008 database in SQL Compact SQL script.

The SQL Server Management Studio add-in has been updated to support the version of SQL Server Management Studio that comes with SQL Server 2008 R2.

Finally, 3 scripting engine bugs have been fixed, thanks to user feedback!

Other notable changes are noted below:

Release Notes

This release contains 4 downloadable files:
- SSMS 2008 scripting add-in  (Documentation)
- SQL Server 2005/2008 command line utility to generate a script with schema and data (or schema only)
- SQL Compact 3.5 command line utility to generate a script with schema and data
- SQL Compact 3.1 command line utility to generate a script with schema and data

Features added to SSMS add-in:
"Show Table Data" - also for INFORMATION_SCHEMA views (read-only)
"Show Table Data" - F3 search added

New command line utility:
Export2SqlCE - for scripting SQL Server 2005/2008 data and schema (or schema only) using SQL Server Compact syntax
(please test against your SQL Server databases, and provide feedback)

Fixes:
Fixed issue with ROWGUIDCOL not being scripted on "uniqueidentifier NOT NULL" columns (generator)
Fixed IDENTITY seed wrong when scripting schema and data (generator)
Fixed missing ; (semicolon) at end of IDENTITY_INSERT statement (generator)

Other:
Support for SQL Server 2008 R2 Management Studio (add-in)
Improved import error handling (add-in)
Updated Eqatec Monitor.dll (add-in)
"About" added to "Show table data" (add-in)

IIS 7.5 (Windows Server 2008 R2) and SQL Compact issue

“SqlCeException: The operating system does not support the Encryption Mode provided” or "You do not have permission to view this directory or page because of the access control list (ACL) configuration or encryption settings for this resource on the Web server."

This error is thrown by the SQL Server Compact replication agent (sscesa35.dll), because of increased security settings in IIS 7.5, the version of Internet Information Services included in Windows Server 2008 R2 and Win 7 – luckily a solution is available.

UPDATE 29/1 2010: This problem (and solution) also applies to Windows 7, which also includes IIS 7.5

New SQL Server Compact training video

A new training video covering SQL Compact development for Windows Mobile, including how to avoid databases being overwritten, and using SqlCeResultSet, has been released.

“Data storage is a requirement in just about every application, and mobile apps are certainly no exception. In this session you will get familiar with the basic programming techniques necessary to use SQL Server Compact Edition with your Windows Mobile application”

Coming soon – script SQL Server 2008 (and 2005) databases for SQL Compact

In the upcoming release of the ExportSqlCe project, a new command line utility, named Export2SqlCe, will be included. This command line utility allows you to script all tables and constraints in a SQL Server 2005/2008 database, and the output will be SQL Server Compact compatible T-SQL.

 

The tool will allow you to move schema and both schema/data from SQL Server to SQL Server Compact.

Currently, the tool will break if any of your tables contains the following SQL Server data type :

sql_variant

If you would like to test the tool, let me know and I can provide you a test build (you can also download the VS 2008 source and build the tool from here.)

SQL Server Compact Bulk Insert Library

Some developers are faced with issues when it comes to loading data into their SQL Server Compact databases. The current synchronization solutions like Merge and Sync Framework do not always fit the bill. In order to ease the pain, I have developed a SQL Server Compact bulk insert library, which mimics the well-know SqlBulkCopy API – the name of the library is of course SqlCeBulkCopy .

The initial beta release is now available on CodePlex as open source, currently ColumnMappings are not implemented (any volunteers?).

Some timings from testing - load 2 column table with no constraints/indexes:

1000000 (1 million) rows: 15 seconds = 66666 rows/second

5000000 (5 million) rows: 82 seconds = 60975 rows/second

Sample usage of the API: - Currently source and destination column ordinals must match

        using ErikEJ.SqlCe;

        private static void DoBulkCopy(bool keepNulls, IDataReader reader)
        {
            SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
            if (keepNulls)
            {
                options = options = SqlCeBulkCopyOptions.KeepNulls;
            }
            using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
            {
                bc.DestinationTableName = "tblDoctor";
                bc.WriteToServer(reader);
            }
        }

Hope you will find it useful, and please provide any comments here.

Sync SQL Compact with SQL Azure and Oracle

There have been a number of new releases from the Sync Framework team to assist is developing off-line applications using Sync Framework and SQL Compact:

Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit)

That’s a long name – what it covers in relateion to SQL Compact is the following:

SqlAzureSyncProvider – Sync Framework 2.0 provider that support SQL Azure

Sql Azure Offline Visual Studio plug-in – allows you to take a SQL Azure database offline – similar to Local Data Cache already in Visual Studio.

You can download the tool from here. Please note that Sync Framework 2.0 is also required.

The Sync Framework team has blogged about the tool here, and in addition, Mahjayar from the team has blogged about going the other way sync direction wise  (from SQL Compact to Azure)

Database Sync – SQL Compact and Oracle (!!)

A sample demonstrating how to synchronize data between SQL Compact and an Oracle database has been released. The times they are a-changing!