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:

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.

2 comments:

Ahmed Amin said...

it doesn't work with me do you know why ?

ErikEJ said...

Nope, please contact me by email and provide some sample code...