Downloading SQL Server Compact 3.5 SP2 for Devices

A number of developers are still, in 2017, developing new solutions for Windows CE / Windows Mobile using Visual Studio 2008 and SQL Server Compact 3.5 SP2. Unfortunately, Microsoft has removed the official download of the SQL CE 3.5 SP2 package for devices, which contains the cab files for installing the SQL CE runtime on various processor flavors of Windows CE / Windows Mobile and also contains a cab file for installing a System.Data.SqlClient (ADO.NET provider for SQL Server), that allows you to access SQL Server directly from a Windows Mobile device.

In this blog post I will describe the steps required to get the Device MSI installed on your development PC. Luckily, it is still currently possible to request the latest updates/hotfixes of the 3.5 SP2 runtime – I have a comprehensive list here.

We can take advantage of the fact, that each “patch” is essentailly a full release of all applicable packages, and that build 8109 is a full re-release of all possible packages.

1: Navigate to the KB page for hotfix 8109: https://support.microsoft.com/en-us/kb/2860310

2: Click the “Hotfix Download Available” link (blue button)

3: Accept the license agreemnt if prompted

4: Expand the list of hotfixes to show all 52 packages by clicking the link “Show hotfixes for all platforms and languages” (this text may be localized)

5: Select (put a checkmark next to) the file named: SSCEDeviceRuntime_ENU

6: Enter your email twice, and press the “Request hotfix” button (button text may be localized)

7: You will now receive an email with a link to the hotfix, download the linked file, named: 465553_ENU_ce_zip.exe

8: Double click the file, and extract it’s compressed contents:
SSCEDeviceRuntime-ENU.msi

9: Double click  SSCEDeviceRuntime-ENU.msi  to install the Devices SDK:

10: Complete the setup, the cab files are now present in the C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Devices folder

SQL Compact 3rd party tools

This is an attempt at a comprehensive list of SQL Server Compact Microsoft and 3rd party tools, both commercial and non-commercial. Please let me know if I have missed anything, and I will add it.

Microsoft supplied tools

Visual Studio Community 2017 free – when used with the SQL Server Compact / SQLite Toolbox: SQL CE 3.5, SQL CE 4.0

Visual Studio 2008 Professional (SQL CE 3.5)

Visual Studio 2005 Standard (SQL CE 3.1)

SQL Server 2008 R2 Management Studio Express (free, SQL CE 3.5 SP2)
Link: http://www.microsoft.com/en-us/download/details.aspx?id=30438
SQL Server 2005 Management Studio Express SP2 (free, SQL CE 3.1)
Link: http://www.microsoft.com/en-us/download/details.aspx?id=15366

New or updated entries are noted with (new) or (updated) ”Last updated” indicates when the tool was last updated by it’s author.

Non-commercial (free) tools

My GitHub Tools

Name: SQL Server Compact Toolbox (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
Added to list: 29th June 2010
”Add-in for Visual Studio 2071/2015/2013/2012 (for 3.5 and 4.0). Standalone app (for 4.0 and 3.5)
Adds several features to help your SQL Server Compact development efforts:
- Scripting of tables, including data.
- Script of entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases, including SQL Azure.
- Import to SQL Compact 3.5 from a server database or CSV file.
- Basic, free form query execution.
- Generation of DGML files for visualizing table relationships
- Database Schema Diff Script
- Detect database file version
- Fix broken connection definitions”

Name: SqlCeScripting.dll (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox  
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
Added to list: 29th June 2010
”Scripting library for inclusion in your own application, enumerates SQL Compact objects and allows scripting of these.”

Name: SqlCeCmd (updated)
Link: https://github.com/ErikEJ/SqlCeCmd
SQL CE Versions: 3.5, 4.0
Last updated: 2015
Platforms: Win32, Win64
”Administer SQL Compact databases directly from the command line, without having Visual Studio or SQL Server Management Studio installed. Only requires NetFx 2.0 and SQL Compact installed. Command line options similar to sqlcmd. Runs scripts generated by ExportSqlCe.”

Name: ExportSqlCe (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox/
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 
”This command line utility helps you better manage your SQL Compact development efforts. If allows you to script schema and data from any SQL Compact database file to a .sql file, which can be used in any context. The utility is developed in C# with Visual Studio 2008. You can use the resulting script for documentation, programmatic scripting of SQL Compact database files, or migration of schema and data to SQL Server (Express) 2005 or later.

Name: Export2SqlCe (updated)
Link: https://github.com/ErikEJ/SqlCeToolbox
SQL Server Versions: 2005, 2008, 2012, 2014 
Last updated: Current
Platforms: Win32
Added to list: 4th March 2010
”A command line utility to script an entire SQL Server 2005 or later database with or without data to a SQL Compact T-SQL script.”

Name: SqlCeBulkCopy (updated) 
Link: https://github.com/ErikEJ/SqlCeBulkCopy
SQL CE Versions: 3.5, 4.0
Last updated: Current 
Platforms: .NET library, .NET CF library 
Added to list: 4th March 2010 
”.NET Library for loading data fast (doing bulk inserts) into a SQL Server Compact database file. Attempts to mimic the SQLClient SqlBulkCopy API. Includes support for BULK INSERTing any IEnumerable”

Query and database management tools

Name: CompactView
Link: http://sourceforge.net/p/compactview/home/Home/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: 2015
Platforms: Win32, Win64
Added to list: 14th November 2011
CompactView is a viewer for Microsoft® SQL Server® Compact Edition (SQLCE) database files (*.sdf). CompactView can open database files of versions 3.1, 3.5 and 4.0

Name: SQL Compact Query Analyzer
Link: http://sqlcequery.codeplex.com/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: 2013
Platforms: Win32, Win64
Added to list: 3rd July 2011 
Features:
- Execute SQL Queries against a SQL Server Compact Edition database
- Table Data Editor to easily edit the contents of the database
- Supports SQLCE 3.0, 3.1, 3.5 and 4.0
- Execute multiple SQL queries (delimited by a semi colon ;)
- Display query result as XML
- Shrink and Compact Databases
- Generate Schema and Data Scripts
- Display database and schema information

Name: LINQPad
Link: http://www.linqpad.net/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th December 2010
Linqpad allows you to query various data sources via LINQ, including SQL Server Compact 3.5 and 4.0. See this blog post.

Name: Database .NET
Link: http://fishcodelib.com/Database.htm
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 20th September 2010
“Database .NET is a simple and intuitive database management tool for multiple databases. Making Local and Remote Database Administration Easier, With it you can Create databases, Design tables, Edit data, Export data, Generate scripts and run SQL queries.”

Name: SQL CE Browser
Link: http://ericwillis.com/notes/2009/12/sql-ce-browser-v-110/
SQL CE Versions: 3.5
Last updated: 2010
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010 
”Open regular and encrypted/passworded databases for viewing or editing. Write and work with any number of queries at once via tabs. SQL syntax highlighting and SQL syntax verification. Explore columns, keys, and indexes“

Name: Mini SQL Query
Link: http://minisqlquery.codeplex.com/ 
SQL CE Versions: 3.5
Last updated: 2014
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010 
”Mini SQL Query is a minimalist SQL query tool for multiple databases (MSSQL, MSSQL CE, SQLite, OLEDB, MS Access/Excel files etc). The tool uses a generic schema engine taking advantage of the ADO.NET providers. Mini SQL Query is also easily extended with plugins.“

Name: SQL Server CE Query Tool
Link: http://cequery.codeplex.com
SQL CE Versions: 3.5, 4.0
Last updated: 2011 
Platforms: Win32, Win64 (?)
”CEQuery is written in C# with the help of Visual Studio 2008 platform. Some of the features of this tool are described below:
User is able to create CE database. If the schema is provided, the tool is able to create the tables and columns. User is able to overview an existing CE database with its table and column structure in tree format. User can design a query by dragging and dropping tables and columns from the database tree. User can manipulate [Insert/Update/Delete] records in a selected table. User is able to generate scripts from a single table or of whole databases. [Both for SQL CE or SQL Server]. User is able to open multiple SQL Server CE files simultaneously. User is able to convert a SQL Server database to a SDF file with schema and data. User is able to change password of a SQL CE DB. The tool is now capable of handling the database with no password.“

Name: SeasonStar Database Management(SSDM)
Link: http://ssdm.codeplex.com/
SQL CE Versions: 3.5?, 4.0 
Last updated: 2011
Platforms: Win32
”With support for all major database formats, top-class export functions and advanced feaures like Linq Data Context availabe, SSDM is probably your best choice for DB management. It's even free software!”

Name: SQL CE Database Editor
Link: http://sqlcedatabaseeditor.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2009 
Platforms: Win32
“Small lightweight application to browse and edit Microsoft SQL CE server databases. Does not require SQL CE server to be installed and can run custom queries.”

Name: DataBoy
Link:http://databoy.codeplex.com/
SQL CE Versions: 3.0, 3.5
Last updated: 2008
Platforms: Win32
“Small footprint, portable and simple database query tool. Support SQL CE 3.1, SQL CE 3.5 and SQL Server.”

Other tools and libraries

Name: Lyare.SqlServerCe.MultiQuery (new)
Link: https://github.com/lokiworld/Lyare.SqlServerCe.MultiQuery
SQL CE Versions: 4.0
Last updated: 2014
Platforms: Win32, Win64
Added to list: 2nd March, 2014 
“An extension to the Microsoft SQL Server Compact Edition client library, which simulates the support of the multiple statements in a single command.”


Name: SQLCE SCRIPTER
Link: http://sqlmgmt.com/sql/SQLCE-Scripter
SQL CE Versions: 3.5, 4.0
Last updated: 2011
Platforms: Win32, Win64
Added to list: 14th November, 2011
“Create SQL schema scripts for tables, indexes and referential relations. Create data scripts so that CE data can be moved to SQL Server or other data store. User can select specific tables only. User can select Script Schema, Script Data or both.”

Name: SQLCE Code Generator
Link: http://sqlcecodegen.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: 2012 
Platforms: Win32, Win64
Added to list: 27th March, 2011
“Contains a stand alone GUI application and a Visual Studio Custom Tool for automatically generating a .NET data access layer code with Unit tests for objects in a SQL Server Compact Edition database. Also generates a Windows Phone DataContext”

Name: OpenNETCF.ORM Framework
Link: http://orm.codeplex.com/
SQL CE Versions: 3.5
Last updated: Current 
Platforms: WinCE, Win32, Win64
Added to list: 27th March, 2011
“The OpenNETCF.ORM library is a lightweight object-relational mapping framework designed for use with the .NET Compact Framework.The framework is extensible to allow any type of backing store. An initial implementation using SQL Server Compact is provided.”

Name: SQL Metal Open Source User Interface
Link: http://sqlmetalosui.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2008 
Platforms: Win32
“This project provides an user interface for SqlMetal.exe tool.
SQLMetalOSUI allows users to generate code and mapping files for their SQL Server Compact Edition databases. SQL Server Compact Edition 3.5 is fully compatible with LINQ to SQL but the designer does not. This project will be focused to reach this initial purpose.”

Commercial tools

Name: MS Compact Maestro (new)
Link: http://www.sqlmaestro.com/products/mssql/compact_maestro/ 
SQL CE Versions: 3.5, 4.0
Last updated: 2013
Platforms: Win32, Win64
”MS Compact Maestro is a top-class SQL Server Compact management and development tool.”

Name: SDF Viewer 
Link: http://www.flyhoward.com/SDF_Viewer.aspx
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32
“The simple way to create, view, edit or export the data in your Windows Mobile or Pocket PC device's SQLCE database, from the convenience of your Desktop. Run SQL commands on your SQLCE databases, and view the results instantly.”

Name: RemoteSqlCe
Link: http://www.gui-innovations.com/html/remotesqlce.html
SQL CE Versions: 2.0, 3.0
Last updated: 2007
Platforms: Windows CE, Win32
”SqlServerCE is a great database for Pocket PC’s, but designing, maintaining and querying databases on the PPC, especially whilst developing, can be wearing. We developed this tool to help us when developing for SqlServerCE. With RemoteSQLCe you can completely manage your SqlServerCE database from the desktop. RemoteSQLCE works in the cradle, and over ethernet/bluetooth.” 

SQL Server Compact books

A number of SQL Compact (or a least SQL Compact related) books have appeared on the market.

I will attempt to keep this list of books up to date as new material appears.

“Building Standalone Data-Driven Applications using SQL Server Compact 4.0 and Visual Studio 2010”

“Microsoft SQL Server Compact 4.0 (SQL Compact onwards) is a free SQL Server embedded database ideal for building standalone desktop, web, and mobile applications that need local data storage.”

http://www.c-sharpcorner.com/Ebooks/

 

“Microsoft SQL Server Compact 4.0 Books Online”

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. Download the documentation to learn about SQL Server Compact 4.0 and to build applications for it.

http://www.microsoft.com/download/en/details.aspx?id=21880

“La réplication du fusion avec SQL Server Compact: Implémentation and conseils”

“Free eBook in French about implementation and advise regarding Merge Replication – in many ways similar to the Rob Tiffany book.”

http://www.wygwam.com/Documents/ReplicationFusionSQLServerCompact.pdf

“.NET Compact Framework 3.5 Data Driven Applications”

“Develop data-driven mobile applications from the ground up on top of the Oracle Lite and SQL Server Compact”

http://www.packtpub.com/data-driven-applications-with-.net-compact-framework-3-5/book

 

“Microsoft SQL Server Compact 3.5 Service Pack 2 Books Online”

This download contains the Books Online for the SP2 version of SQL Server Compact 3.5.

http://www.microsoft.com/downloads/details.aspx?familyid=746C3A6E-FFB1-4C92-93FA-B3BA41FDE681&displaylang=en

“SQL Server MVP Deep Dives”
This book with contribution from 53 SQL Server MVPs includes 2 chapters relating to SQL Compact:
Placing SQL Server in your pocket by Christopher Fairbairn and Mobile data strategies by John Baird
http://www.manning.com/nielsen/

 

"Enterprise Data Synchronization with SQL Server 2008 and SQL Server Compact 3.5"

An update of the previous book, equally excellent and hands-on.

Available from Amazon

 

"Microsoft Mobile Development Handbook"

This book covers development using .NET Compact Framework 2.0 in broad terms, and has two chapters relating to SQL Compact:

Chapter 3 describes how to persist data using SQL Compact and how to bind SQL Compact data to UI elements and chapter 7 briefly covers using RDA and merge replication with SQL Compact.

http://www.microsoft.com/MSPress/books/10294.aspx

 

"Windows Mobile Data Synchronization with SQL Server 2005 and SQL Server Compact 3.1"

I have already recommended the excellent Merge Replication book by Rob Tiffany here:

http://erikej.blogspot.com/2007/10/rob-tiffany-publishes-mobile-merge.html

Available from amazon

"Microsoft® SQL Server 2005 Compact Edition"

This book covers the product in full, including a chapter on Native development, and several chapters on replication. The main perspective of this this book is a DBA approach, with only a single chapter covering development per se. There are also useful chapters on security and performance tuning.

http://www.informit.com/store/product.aspx?isbn=0672329220

 

"Hitchhiker's Guide to Microsoft SQL Server 2005 Compact Edition"
For desktop developers diving into SQL Compact, the e-book by Bill Vaughn is an excellent introduction, which covers many aspects and pitfalls of developing with SQL Compact edition in a desktop application.

http://www.hitchhikerguides.net/EBooks/5582.aspx

 

SQL Server Compact version detector

With three current SQL Server Compact versions (file formats) available 
3.0/3.1 on Windows Mobile 6.x devices,
3.5 with Visual Studio 2008 and 2010,
and 4.0 currently available as a CTP (Community Technical Preview),
it can sometimes be hard to tell what the file version of a SQL Compact file in your possession is.

To help you, I have created a small utility that you can place on your desktop and drop any SQL Server Compact file on.

You can download a .zip file with the utility here. And the source code for the utility is available here.

Place the SQL Compact Detector.exe on your desktop, and drop a SQL Server Compact file on it!

SQL Compact 3.5 OLEDB C++ sample now available

Finally, the SQL Compact 3.5 OLEDB C++ Northwind sample is available for download here: http://code.msdn.microsoft.com/sqlce

SQL Compact versions on Windows Mobile

Since version 6 of Windows Mobile, every version of Windows Mobile (6, 6.1, 6.1.4, 6.5 and 6.5.3) have included SQL Server Compact Edition version 3.1. But each OS version has contained a newer build of 3.1. The desktop RTM version of SQL Compact was 3.0.5300.0.

The table below summarizes the various OS version and the corresponding SQL Compact build number:

 

Windows Mobile version	SQL Compact build
6.0	3.0.5258.0
6.1	3.0.5258.0
6.1.4	3.0.5258.0
6.5	3.0.5300.8
6.5.3	3.0.5300.16

Look here for a list of hotfixes and corresponding build numbers.

Crystal Reports with SQL Compact

I stumbled across this post by Jason Irwin, that describes how to solve the mystery of connecting Crystal Reports to data from a SQL Compact file – by elegantly using a dataset as the “middleman” or common language, if you like.

SQL Compact Schema and Data Export utility updated

Version 1.9 fixes 3 bugs reported by users. Thanks and keep looking!

Fixed date formatting bug in INSERT statements (thanks to user spainb)
Fixed missing update and delete rules on multi field foreign keys (thanks to user samunro)
Fixed too many Primary and Foreign keys returned bug (thanks to user samunro)

The utility has now been downloaded more than 1000 times – thanks for your support, and hope you find it useful.

ExportSqlCe version 1.7 released

This latest release contains the following changes and improvements:

Added Update and Delete Rules on Foreign Keys
Improved error handling
Fixed output file naming bug
Binary and varbinary columns were missing size in CREATE TABLE statement

- and it now has a nice icon :-)

Go and get it here.

Inserting images from VBA

Inserting images into a SQL Compact from VB(A) does not appear to be possible using vanilla VBA code (please correct me if I am wrong). In order to work around this limitation, it is possible to create a function in C#, and call this function from VBA. This excellent post got me going. This is the VBA Code:

Sub InsertBlob()
    
    Dim IMG_FILE As String
    Dim intFile As Integer
    Dim ImgBuff() As Byte
    Dim ImgLen As Long
    Dim ret As String
    
    IMG_FILE = "C:\ErikEJ.jpg"
    'Read/Store GIF file in ByteArray
    intFile = FreeFile
    Open IMG_FILE For Binary As #intFile
    ImgLen = LOF(intFile)
    ReDim ImgBuff(ImgLen) As Byte
    Get #intFile, , ImgBuff()
    Close #intFile
       
    Dim sqlCeUtil As New SqlCompactUtility
    ret = sqlCeUtil.SaveBlob(ImgBuff(), "Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture) VALUES (@Blob)", "@Blob")
 
End Sub

First a Byte array with the image contents is created. Then the .NET based method (SaveBlob) is invoked.

The SaveBlob method takes 4 parameters:

1. The byte array

2. The SQL Compact connection string

3. The Insert statement

4. The parameter name

In order for this to work, add a reference to the .NET dll library from the VB(A) project, the code of which is shown below (created per the instructions in the "A Basic Walk Through" part of the post mentioned above). If you have followed the instruction in the blog, the DLL should appear in the Add Reference dialog once built.

using System;
using System.Data.SqlServerCe;
using System.Runtime.InteropServices;

namespace SqlCompactInterop
{
    public class SqlCompactUtility
    {
        //SaveBlob(data, @"Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture, Id) VALUES (@Blob, 2)", "@Blob");
        public string SaveBlob(byte[] data, string connectionString, string insertStatement, string parameterName)
        {
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(connectionString))
                {
                    SqlCeCommand cmd = new SqlCeCommand(insertStatement);
                    cmd.Parameters.Add(new SqlCeParameter(parameterName, System.Data.SqlDbType.Image, data.Length));
                    cmd.Parameters[parameterName].Value = data;
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    return string.Empty;
                }
            }
            catch (SqlCeException e)
            {
                return ShowErrors(e);
            }
        }

        // Error handling routine that generates an error message
        private string ShowErrors(SqlCeException e)
        {
            SqlCeErrorCollection errorCollection = e.Errors;

            System.Text.StringBuilder bld = new System.Text.StringBuilder();
            Exception inner = e.InnerException;

            if (null != inner)
            {
                bld.Append(inner.ToString());
            }
            // Enumerate the errors to a message box.
            foreach (SqlCeError err in errorCollection)
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X"));
                bld.Append("\n Message   : " + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source    : " + err.Source);

                // Enumerate each numeric parameter for the error.
                foreach (int numPar in err.NumericErrorParameters)
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }

                // Enumerate each string parameter for the error.
                foreach (string errPar in err.ErrorParameters)
                {
                    if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
                }

             
            }
            return bld.ToString();
        }
    
    }

}
 

"Hope this helps"

ExportSqlCE - new release with support for SQL Compact 3.0/3.1

The post title says it all! - ExportSqlCE release 1.6 is now available at CodePlex. The reason for the new release is support for exporting SQL Server 2005 Mobile Edition (3.0) and SQL Server Compact Edition (3.1) sdf files. Identity columns are not included in the CREATE TABLE statement, as "SET IDENTITY_INSERT" is not available with SQL Compact 3.0/3.1. Support for 3.1 is enabled by ading a new solution and project file to the source, which in turn generates a new exe: ExportSqlCe31.exe. This allows both versions to share a single codebase, but reference different versions of System.Data.SqlServerCe, with conditional compilation symbols. Please try it out and let me know what you think.

Mærker fra Technorati: SQL Compact,SQLCE,SQL CE

ExportSqlCe version 1.5 released

The latest version of the ExportSqlCe command line utility for scripting entire SQL Compact database files has been released on CodePlex.

Since I last blogged about this utility, the following missing features have been added:

Unicode support – the script file is now in Unicode format, which means that all text data is scripted “properly”, also for non-English text.

Image/binary/varbinary in INSERTs – binary and image fields are now scripted with their proper contents (as a hex string), and not “System.Byte[]”

INSERT with IDENTITY columns – CREATE TABLE now properly scripts IDENTITY columns, and using the SET IDENTITY INSERT feature of SQL Compact 3.5, it is now possible to do inserts on the table with the IDENTITY column.Numeric fraction and precision

INSERT statement improvements – INSERTs are now scripted without single quotes around numeric fields, which improves readability, INSERT performance and script size.
Support for rowversion – rowversion (timestamp) is a read-only column and cannot be updated. Therefore the generated script has been improved to exclude rowversion fields in INSERT statements.
Foreign keys with multiple fields – were not properly scripted as reported (and partly fixed) by user hugo on CodePlex.
Handle merge replicated databases – Merge replicated database files contains a number of system fields and tables. These should not be scripted – this  has been implemented.
Handle large tables – scripts of large tables are now flushed to disk in several files, to save memory.

Go and get the latest release/source, and give feedback at http://www.codeplex.com/ExportSqlCE

Technorati Tags: SQL Compact,SQLCE,SQL CE,SQL Server Compact Edition

Merge Replication and ISA Server

There is very limited, if no, information available regrading setting up Merge Replication with and ISA Server in the middle. This has now been remedied by this excellent post, from Breeze Training.

Windows Mobile platform support

SQL Compact is now being included in various versions of Windows Mobile. It can be difficult to determine which Windows Mobile version contains which version of SQL Compact. There is an overview available here. The overview also contains information about EDB/CEDB, and overview over platforms supported by the development tools.

Import SQL Compact data to Excel

I have made the small VBA sample below, to demonstrate how you can easily import data from a SQL Compact database file using the OLEDB provider included with SQL Compact and some good old VBA code.

Before using this sample, add a reference to ADO (Microsoft ActiveX Data Objects) in your Excel VBA project, as shown below:

Hope you find it useful.

Option Explicit

Const SdfPath = "C:\Northwind.sdf"

Sub ImportSqlCeData()
Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset
Dim rRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Dim cmd As New ADODB.Command
Set pRS = New ADODB.Recordset

Set rRS = New ADODB.Recordset
Dim rcmd As New ADODB.Command

' For 3.0 use PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & SdfPath
pConn.Open
    
rcmd.ActiveConnection = pConn
cmd.ActiveConnection = pConn
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
Set pRS = cmd.Execute
' Open the recordset
Dim ws As Worksheet
Dim rng As Range

Dim i As Long
Dim row As Long

While Not pRS.EOF
    row = 1
    Set ws = Application.ActiveWorkbook.Worksheets.Add()
    ws.Name = pRS(0).Value
    rcmd.CommandText = "SELECT * FROM [" & pRS(0).Value & "]"
    Set rRS = rcmd.Execute
    ' Headers
    For i = 0 To rRS.Fields.Count - 1
        Set rng = ws.Cells(row, i + 1)
        rng.Value = rRS.Fields(i).Name
    Next i
    ' Data rows
    row = 2
    While Not rRS.EOF
        For i = 0 To rRS.Fields.Count - 1
            Set rng = ws.Cells(row, i + 1)
            If (rRS.Fields(i).Type = adLongVarBinary) Then
                rng.Value = "<Binary - not supported>"
            Else
                rng.Value = rRS(i).Value
            End If
        Next i
        rRS.MoveNext
        row = row + 1
    Wend
    
    pRS.MoveNext
Wend

End Sub

Testing an sdf file for version info

Christ Tacke from opennetcf.org has put together a nice little piece of code to test the SQL Compact database version by looking at the binary contents of the file, thus avoiding program flow based on catching exceptions.

SQL Connections - keeping open and what about threads...

I stumbled across this approach to handling the fact that it is recommended to always keep a SQL Compact connection object open (SqlCeConnection), and also handle that fact that Any instance or static members of type SqlCeConnection are not guaranteed to be thread safe. Looks like a sensible approach.

Versions...versions

The SQL Compact team blog has made information on connectivity tools and version compatibility available, similar to this page for older versions.

SQL Compact 3.5 testing

I have begun testing SQL Compact 3.5 (as part of VS 2008 beta 2) and will start to post my findings in this blog.