Saturday, February 23, 2008

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.

Wednesday, February 20, 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.

Wednesday, February 6, 2008

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:


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.