Using ExportSQLCE from Visual Studio

Using an external tool from Visual Studio is an easy way to make use of command line utilities like ExportSQLCE. In this post I will show how to add ExportSQLCE to your Visual Studio tool set.

Start Visual Studio, and select the Tools menu, External Tools option.

This will bring up this dialog:

Click Add to add ExportSQLCE:

Click OK, and you now have a new menu option on the Tools menu, called ExportSQLCE.

Try it out:

Fill in the sdf file name and the name of the output file, which will be placed in your VS project folder and click OK.

Notice how the output appears in the VS Output window:

Technorati Tags: SQL Compact,SQL CE

And the cstest.sql file is now available in the project folder (use Show All Files):

SQL Compact data and schema export utility on CodePlex

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

I have made some modifications to the utility I blogged about here and made the code available as a project on CodePlex. Please feel free contribute feature requests, patches and bug reports. The utility exports the object definition from a SQL Compact database, including tables, indexes, foreign keys and other constraints. It also scripts INSERT statements for all the data in the SQL Compact table (currently missing features are support for INSERT to image columns, and proper creation of IDENTITY columns).

New edition of Merge Replication book

Rob Tiffany has begun a new edition of his excellent Merge Replication book, the new title is:

Mobile Data Synchronization with Microsoft SQL Server 2005 and SQL Server Compact > Second Edition. Achieving Global Scalability via Merge Replication Republishing.

Go download and read Chapter 1.

Accessing SQL Compact from SQL Server "Linked Server"

SQL Compact users would like to have access to SQL Compact data (read only) from within SQL Server for various reasons: Import the data to a SQL Server table, use the data in Reporting Services, join SQL Compact data with SQL Server data etc. I have made a sample which I will describe in this blog post, that demonstrates two types of access: Using a SQLCLR table-valued function (UDF) and a SQLCLR stored procedure.

Due to security restrictions of SQLCLR, the solution uses the OLEDB provider and not the ADO.NET provider. This is the cause for the following limitations of the sample: "ntext" and "image" columns are not supported, as data in those BLOB type fields cannot be retrieved from the OLDEB provider.

The sample consists of 2 main methods, the one for the table valued UDF is: GetDataFromSqlCe.

        [SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetDataFromSqlCe(string dataSource)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + dataSource);
            conn.Open();
            using (OleDbCommand cmd = new OleDbCommand("SELECT [Category Id], [Category Name] FROM Categories", conn))
            {
                // Ensure that the connection is closed when the reader is closed
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
 

The sample method uses a SQL statement, which output must match the parameters of the FillRow method, meaning you will have to code a function for each table you would like to expose.

 private static void FillRow(Object obj,
                        out SqlInt32 catId,
                        out SqlString catName
                        )
        {
            DbDataRecord row = (DbDataRecord)obj;
            int column = 0;
            catId = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
            catName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
        }
 

The advantage of using the UDF approach is that you can use the output from the function as if it was an ordinary SQL Server table, since the output is "strongly typed", so to speak.

The second approach GetTable returns a SQL result set, and works for any table in the sdf file (minus ntext and image fields).

public static void GetTable(String connectionString, String tableName)
 

Based on metadata from INFORMATION_SCHEMA.TABLES a SqlMetaData array is constructed and used for defining the SqlRecord to be returned. The records are then dynamically built and returned.

To test the sample, do as follows:

1. Download the zip file below with the source code for the SQLCLR dll file.

2. Open and compile in Visual Studio 2008

3. Use the indcluded install.sql script to configure, install and test the SQLCLR dll on your SQL Server instance.

Hope you will find this useful...

Going to PDC

I am attending PDC in Los Angeles. There are a few sessions relating to SQL Compact, that I will consider attending:

SQL Server Compact: Embedding in Desktop and Device Applications
Wed 10/29 | 3:00 PM-4:15 PM | 402A
Presenter: Steve Lasker

Learn how SQL Server Compact can be used to create data files for your applications, run applications directly from DVD, capture user activity, and sync "back home." Learn the different deployment options, including the newly released 64-bit support and best practices for performance.

Offline-Enabled Data Services and Desktop Applications
Wed 10/29 | 3:00 PM-4:15 PM | 408A
Presenter: Pablo Castro

The ADO.NET Data Services Framework (a.k.a. Project "Astoria") introduced a way of creating and consuming flexible, data-centric REST services. By combining data services with the Microsoft Sync Framework, learn how to create offline-capable applications that have a local replica of their data, how to synchronize that replica with an online data service when a network connection becomes available, and how replicas can be used with the ADO.NET Entity Framework. Also, hear us talk about our plans, see the tools that help client- and server-side setup, and discuss the runtime components and APIs.

Microsoft Sync Framework Advances
Mon 10/27 | 1:45 PM-3:00 PM | 515B
Presenter: Lev Novik

This session shows you how the next version of the Microsoft Sync Framework makes it easier to synchronize distributed copies of data across desktops, devices, services, or anywhere else they may be stored.

Sync Framework: Enterprise Data in the Cloud and on Devices
Tue 10/28 | 5:15 PM-6:30 PM | 408A
Presenter: Liam Cavanagh

See how synchronization plays a pivotal role in transitioning to a managed cloud environment by creating a central hub of information in the cloud. Using synchronization, organizations can enable more efficient mobile and enterprise-to-enterprise scenarios.

Hope to see you there!

All SQL Compact 3.5 SP1 downloads now available

With the current release of Sync Services for Devices, all the SQL Compact 3.5 SP1 downloads are now available here.

Sync Services for Devices released to web

The Sync Services for devices runtime has now been released to web, including an updated sample application. Sync Services requires SQL Compact 3.5 SP1 to be installed on the device. There have been some issues with performance during sync, that have not yet been solved properly, so beware. (RDA and Merge has proprietary support for compression). I have updated my SQL Compact feature matrix accordingly.

SQL Compact and LINQ to SQL performance advise

Technorati Tags: SQL Compact,SQL Server Compact Edition

Matt Manela has posted some good advice on how to avoid a performance penalty when using SQL Compact with LINQ to SQL. SQL Compact works best if there is a initial open connection to the database file available for the lifetime of the application, and Matt's code implements this concept.

SQL Server 2008 Management Studio Express released

As part of the release of SQL Server 2008 Express, SQL Server 2008 Management Studio Express (which can be used for managing and query analyzing SQL Compact 3.5 databases), is now available for download as part of SQL Server Express with Tools. Finally, 9 months after the release of VS 2008 (which included SQL Compact 3.5)

Export SQL Compact 3.5 data and schema

Bembeng Arifin has created this excellent command line utility, which creates a .sql script for migrating SQL Compact 3.5 data to SQL Server (includes CREATE and INSERT statements). Highly recommended - source code also available.

SQL Compact 3.5 SP1 RTM

As SQL Server 2008 has just been released, SQL Compact 3.5 SP1 is now also released. Go get it:
http://msdn.microsoft.com/subscriptions/downloads/default.aspx
http://technet.microsoft.com/subscriptions/downloads/default.aspx

UPDATE: Steve Lasker has more detailed information on his blog, with direct links to the downloads, including 64 bit (x64) downloads.

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.

Sync Framework V1 released

MS Sync Framework, which is also the basis of ADO.NET Sync Services V2 has been released. This blog post has links to the download and other relevant information. Part of the technology used for Sync Framework is of course SQL Compact 3.5.

Intro to Mobile Sync

Christopher Fairbairn has posted a good overview of SQL Compact, and the related synchronization technologies.

News from blog land

A collection of new blog entries relatied to SQL Compact:

Tim Anderson is faced with the classical problem of not knowing where the database and the data resides when using the graphical designers.

On the SQL Compact Blog, there is information about the causes of database file corruption. Most issuse appear to be related to SD Card issues.

Also on the SQL Compact Blog, there is documentetion about Merge Replication support for the new datatypes in SQL Server 2008 (data and spatial types).

Laxmi from the product team blogs about details of the 3.5 SP1 features, in particular Case Sensitivity and 64 bit support.

Finally, the Sync Framework team has published advance documentation on implementing 2-way Sync by extending the VS 2008 SP1 Sync Designer generated code.

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.

SQL Compact 3.5 SP1 news

SQL Compact 3.5 SP1 will contain quite a number of new features. This Service Pack will be released together with Visual Studio 2008 SP1/.NET Framework 3.5 SP1.

The release of SQL Compact 3.5 SP1 will contain the following new features on top of a number of bug fixes:

1. Support for x64 both on the runtime and server tools. (Will be made available as a web download when VS 2008 SP1 is released. Steve Lasker blogs in detail about the support for this new platform.
2. Support for Entity Framework and Entity SQL. Ravi Tandon has more info on the SQL Compact blog. This support is for desktop only - not devices.
3. Support for "Local Database Cache" for device projects (currently available as a CTP). This means that Microsoft Synchronization Services for ADO.NET is now available for devices.
4. The Server Tools (for Merge and RDA) will fully support SQL Server 2008.

UPDATE 15/5 2007: The SP1 beta is now available for download, and Steve Lasker has more details about the SP1 changes and features.

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.

UPDATE: To use a password, use the following connection string:

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;ssce:Database Password=xyz;Data Source=" & SdfPath

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

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.

Mobile sample using SQL Compact

Microsoft has recently released the excellent sample called Windows Mobile Line of Business Solution Accelerator 2008, which includes a sample using SQL Compact 3.5 for local device data storage, and ADO.NET Sync Services for Devices for replication. The data access layer in the sample includes a way (in code), to get two much asked for "features" with SQL Compact, stored procedures and triggers. Highly recommended!

In addition, Rabi Satter, one of the developers of the sample, has been blogging about the design decisions behind the "managed stored procedures" feature.

Rob Tiffany has a detailed walk through with plenty of screen shots.

SQL Compact hotfix released

A hotfix for SQL Compact 3.1 and 3.5 is now available. It solves the following issue related to deletion of rows:

You have an application that inserts many rows into a table. The table is in a database in Microsoft SQL Server 2005 Compact Edition or in Microsoft SQL Server Compact 3.5. The application inserts the rows in multiple threads. Additionally, the application inserts more than 10 rows per second. In this scenario, you intermittently receive the following error message when you try to delete the rows from the table in the database:

Major Error 0x80004005, Minor Error 0. Attempted to divide by zero

My wish list for next version of SQL Compact

(In no particular order)

Data events for data binding or similar (triggers)
- in order to know when to refresh the UI or update a cache.

Proper OLEDB provider Office etc. and support for SQL Server Import/Export wizard/SSIS
- this should become available with SQL Server 2008, I have not (yet) been able to make it work, though.

Better multi version tool support 3.1 vs 3.5 vs 4.0
- we can administer SQL Server 2000 and later from SQL Server 2005 Management Studio, why can't we do the same for SQL Compact 3.0 and later?

LINQ to SQL Compact for Devices
- a lightweight implementation, taking advantage of SqlCeResultSet, for example

Sync Services for Devices
- YES, wish fulfilled, as this is now available as a CTP.

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.

ADO.NET Sync Services for Devices CTP

This is now available as a CTP download. This CTP enables Windows Mobile 5.0 and 6 .NET CF 2.0 based applications to take advantage of the Sync Services API. Sync Services is now truly a potential replacement for RDA and to some extent also Merge Replication, in particular when used with SQL Server 2008 Change Tracking enabled. The download consists of the required device CAB files, a sample which includes a device app, a web service and a WinForms app. Only N-tier solutions are supported, meaning no direct connection to SQL Server is possible (as with the desktop based Sync Services). This makes a lot of sense, and reduces the footprint for devices.

The following is required on the device:
- SQL Server Compact 3.5 RTM
- Sync Services CAB
- .NET Compact Framework 2.0
- Device OS: Windows Mobile 5.0 and 6

Development requirements:
- Visual Studio 2008 Professional or higher, with Smart Device Extensions installed.
- The Sync Designer does NOT work within the device project, but generated code from a WinForms project can be reused.

More on the sample to come later...

SqlCeResultSet enumeration bug

Jim Wilson has discovered a bug when enumerating a SqlCeResultSet, and provides a workaround. This should be fixed in the SP1 release, which will come on the same time as SQL Server 2008.

SQL Compact Device Deployment

This article discusses the various options for deploying the SQL Compact runtime (and Compact Framework) with your application.  There is no simple solution to this issue, but a number of options are available as discussed, the simplest being to deploy the SQL Compact DLL files a content together with your application, but this can be a disk issue, if you have several applications using SQL Compact.

SQL Compact and Entity Framework beta

A beta of the SQL Compact desktop runtime, that allows you to use Entity Framework beta 3 against a SQL Compact 3.5 database is now available for download.

SQL Server Compact 3.5 SP1 Beta release for the ADO.Net Entity Framework Beta 3 enables the following scenarios:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema
• Mappings between the conceptual application model and the storage-specific schema can change without changing the application code
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems
• Multiple application models can be mapped to a single storage schema
• Language-integrated query support provides compile-time syntax validation for queries against a conceptual model

So now SQL Compact supports both LINQ to SQL and LINQ to Entities - desktop only.

SQL Compact 3.5 client agent logging

Client agent logging is now available with SQL Compact 3.5 (not sure if it was available with 3.1 as well).

This logging runs when performing SQL Server Merge Replication and Remote Data Access (RDA).

For server side logging of Merge and RDA, see BOL.

You may find this undocumented feature useful when troubleshooting Merge and RDA issues.

To enable logging, create a text file named "CaLogCfg.txt" in the folder where  sqlceca35.dll resides, on my system it is in: C:\Program Files\Microsoft SQL Server Compact Edition\v3.5.

The file should look like this:

LogLevel=5 
LogFilePath=C:\log.txt 
WininetLog=1

Valid loglevel settings appear to be from 0 to 5, 5 being the most verbose level.

Setting WininetLog=0 disables logging of WinInet API calls, and only logs database engine calls.

A sample log file, with LogLevel=1 and no WinInet logging:

   1:  SQL CE Client Agent Log Start - Version 3.5.5386.0 -----

   2:  SQLCECA: 02/06/2008-11:21:31 CSSCEMerge::SingleRun id=0 hr=0 bread=931 bwritten=255 c_urows=0 c_drows=0 s_urows=2 s_drows=0 md_n=0 md_c=0 md_r=0 app=UpgradeTo35.vshost.exe

   3:  SQLCECA: 02/06/2008-11:21:31 CSSCEMerge::Run totalsynctime=1 uploadtime=1156 applytime=0 id=0 publisher='LEGOLAS' publication='SQL31Pub' initialsync=1 bread=931 bwritten=255 c_urows=0 c_drows=0 s_urows=2 s_drows=0 loops=1 app=UpgradeTo35.vshost.exe

And with max logging:

   1:  SQL CE Client Agent Log Start - Version 3.5.5386.0 -----

   2:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::AddSubscription Start app=UpgradeTo35.vshost.exe

   3:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::Initialize Start app=UpgradeTo35.vshost.exe

   4:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::DetectMove Start app=UpgradeTo35.vshost.exe

   5:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::DetectMove End hr=0 app=UpgradeTo35.vshost.exe

   6:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::Run Start subscriber='Sql35Pub'  publisher='xxx' publisherlogin='sa' publisherpassword='***' proxyserver='' publicationdatabase='SQLCETester' publication='SQL31Pub' url='http://xxx/sqlce35/sqlcesa35.dll' serverconnectionstr='***' PublisherSecurityMode=0  app=UpgradeTo35.vshost.exe

   7:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::Run Start PhysicalMemory=145215488 app=UpgradeTo35.vshost.exe

   8:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::MakeMsg Start app=UpgradeTo35.vshost.exe

   9:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::MakeIntialSyncMsg Start app=UpgradeTo35.vshost.exe

  10:  SQLCECA: 01/30/2008-18:14:56 CSSCEMerge::MakeIntialSyncMsg End hr=0 app=UpgradeTo35.vshost.exe

  11:  SQLCECA: 01/30/2008-18:14:56 InternetOpenW called. lpszAgent=SQLCEReplicationClient dwAccessType=1 lpszProxy= lpszProxyBypass= dwFlags=0 app=UpgradeTo35.vshost.exe

  12:  SQLCECA: 01/30/2008-18:14:56 InternetOpenW ended. retval=cc0004 app=UpgradeTo35.vshost.exe

  13:  SQLCECA: 01/30/2008-18:14:56 InternetConnectW called. handle=cc0004 lpszServerName=legolas nServerPort=50 lpszUserName= lpszPassword=*** dwService=3 dwFlags=0 dwContext=0  app=UpgradeTo35.vshost.exe

  14:  SQLCECA: 01/30/2008-18:14:56 InternetConnectW ended. retval=cc0008 app=UpgradeTo35.vshost.exe

  15:  SQLCECA: 01/30/2008-18:14:56 InternetSetOption called. handle=0 dwOption=6 lpBuffer=8fc480 dwBufferLength=4 app=UpgradeTo35.vshost.exe

  16:  SQLCECA: 01/30/2008-18:14:56 InternetSetOption ended. retval=1 app=UpgradeTo35.vshost.exe

  17:  SQLCECA: 01/30/2008-18:14:56 HttpOpenRequest called. handle=cc0008 lpszVerb=POST  lpszObjectName=/sqlce35/sqlcesa35.dll lpszVersion=HTTP/1.0 lpszReferer=(null) lpszAcceptTypes=433c43c dwFlags=480100 dwContext=0 app=UpgradeTo35.vshost.exe

  18:  SQLCECA: 01/30/2008-18:14:56 HttpOpenRequest ended. retval=cc000c app=UpgradeTo35.vshost.exe

  19:  SQLCECA: 01/30/2008-18:14:56 HttpSendRequestA called. handle=cc000c lpszHeaders=RSCB: 0  RCmd: 8  RExc: 3DF8C204032E_6A1C2DCA-F0EE-48E0-9DC1-14DB10E8D7AA  RBuf: 65536  RCur: 0  RTot: 255  RSub: {78B42E03-43CD-432B-9F39-FA3510FBD313}  RCL: 4  TransportVersion: 3.00   dwHeadersLength=ba lpOptional=45a9eb4 dwOptionalLength=96 app=UpgradeTo35.vshost.exe

  20:  SQLCECA: 01/30/2008-18:14:58 HttpSendRequestA ended. retval=1 app=UpgradeTo35.vshost.exe

  21:  SQLCECA: 01/30/2008-18:14:58 HttpQueryInfoA called. handle=cc000c dwInfoLevel=536870931 lpBuffer=3c4e520 lpdwBufferLength=3c4e510 lpdwIndex=0 app=UpgradeTo35.vshost.exe

Thanks to this MSDN forum post for revealing this feature. And for an example of usage, look here.

x64 and SQL Compact

Several users running various x64 version of Windows are facing problems when trying to develop or deploy solutions based on SQL Compact. The problems comes from the fact the SQL Compact is only supported in 32 bit WoW (Windows on Windows) mode on the x64 platform. This gives problems in two situations: When trying to run .NET Framework based applications, and when trying to use the SQL Compact Synchronization Agent (sqlcesa3x.dll) on x64 version of Internet Information Services (IIS). 

Making .NET applications that depend on SQL Compact work on a x64 system

Any new project you start in Visual Studio is by default targeted to the "Any CPU" platform:

This means that unless modified either by changing the build configuration (if you have built the application yourself) or modifying the .exe file by using corflags.exe (if you have not built the application yourself), the application will execute under the default .NET Framework, which is a x64 version on the Framework on the x64 platform. Since your application depends o several unmanaged DLL files, namely the SQL Compact DLLs, it will not be able to use SQL Compact and fail.

Here is how to change the target platform from within Visual Studio:

(If you do not see this drop down, go to Tools, Options, Projets and Solutions, and make sure "Show advanced build configurations" is checked)

. 

Select the Configuration Manager from the drop down above:

Select <New...> and selcet x86 from the top drop down:

(and click OK and Close)

Now you configuration look like this:

You are good to go, since your application will now be forced to use the 32 bit version of the .NET Framework always, even on a x64 platform.

Forcing 32 bit (if you do not have the source code)

In order to do this, use the .NET Framework SDK tool called corflags.exe.

Launch a Visual Studio command prompt, and you can use corflags.exe:

The flag you need to set is the 32BIT flag, so use a command line like this against your .exe file:

corflags C:\myprogram.exe /32BIT+

Your application will now be forced to use the 32 bit version of the .NET Framework always, even on a x64 platform.

Runnning the Replication Agent on a x64 version of IIS

The x64 version of IIS runs by default in x64 mode. This caused many problems for 32 bit ISAPI DLLs, for example sqlcesa30.dll and sqlcesa35.dll - the SQL Compact replication agent DLL. You need to configure an application pool to run in 32 bit mode. In IIS 7 this is quite simple, as demonstrated here.

In IIS 6, enabling this is slightly more complicated, and forces all application pools on your IIS server to run in 32 bit mode. Windows Server 2003 Service Pack 1 is required for this to work. How to enable this is documented here.

Sort order issue when moving sdf files between platforms

Jim Wilson describes an index rebuild issue that occurs when moving SQL Compact database files from one platform (Vista or Server 2008) to a mobile device.

SQL Compact "workshops"

Jacob Sebastian, blogger, has made 3 introductory workshops on SQL Compact on www.sqlservercentral.com

Architecture Journal 14

The Microsoft "Architecture Journal" issue 14 has "Mobile Architecture" as it's theme. It includes several articles that discuss solutions based on SQL Server Compact. 

Versions...versions

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

LINQ to SQL

Matt Sollars has published an article on CodeProject on using LINQ to SQL with SQL Server Compact Edition, and also covers how to overcome many other LINQ to SQL challenges.

Rob Tiffany merge replication book now available

 

It can be purchased from amazon.com for 30 USD.

Recommended reading for anyone involved with merge replication.