Install and Configure SQL Server for SQLCE replication

Garth Tissington

Rate me:

3.50/5 (11 votes)

13 Sep 2003CPOL1 min read

88.3K

After pulling my hair out for several days I finally figured out how to install and configure SQLCE with SQL Server for replication to a Windows CE device. I hope this saves you some pain

Introduction

Let me start by saying that I'm not a SQL guru, I'm not a DBA, I'm a lowly application programmer who just happens to (begrudgingly) need to use SQL server. I thought I was fairly adept and figuring out weird combinations of software until I tried to install SQL Server CE and set it up to replication with my SQL Server on the desktop.

SQLCE and SQL Server, they are supposed to work together, right? They do but there are a few gotchas along the way that you might like to know about. I look forward to hearing other tips or advice on this process.

The instructions

It's pretty simple actually but you have to do it exactly as follows.

Note

SQLCE (Server Tools) will not install without SP1 it will not install with SP3. You must install SQL2k -> SQL2KSP1 --> SQLCE --> SQL2KSP3
SQL Replication will not work if the SQL service is running under the system account. You MUST configure the SQL services to use a DOMAIN account with 'run as service' privilege

Steps

Run SQL 2K Installer.
Change the Instance Name
Do NOT use the System account. It does not work for replication.
The account you use must run as a service
The account must have a password
change the sa password
Install SP1. DO NOT install SP3 yet. SQLCE will not install over SP3
Now Install SQLCE
Now Install SP3

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Garth Tissington

Canada

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

dear author
i am a new user on embedded vc++ / SQL Ce. I am yet to find out whether my SQL CE installation is correct or not . To summarise what I did ...
IIS Is already running ....
1) Installed embedded vc++ 4.0
2) Installed pocket PC 2003 SDK
3) Installed SQL Server CE 2.0 ( I think its SP1). My system is XP . I am able to run MFC applications on the emulator . fine.The SQL Server CE installed the folders . D:/program files/ SQL Server CE 2.0 and in that the following folders /Server /Redist /Samples /Inc /Lib etc . In the start menu
I got Start -->prog files-->SQL Server CE --->SQL Server CE connectivty management . I created a virtual folder and the sql server ce agent is (sscesa20.dll) at d:/prog files/SQL Server CE/Server . Ok . But I am trying to connect to SQL Server database from within the emulator and later on with device ( Pocket PC Casio 800 which I have not yet seen ) ...
This is the piece of code I have written the Pocket PC appliation ..
#include "D:\Program Files\Windows CE Tools\wce420\POCKET PC 2003\Include\Emulator\oledb.h"
#include "D:\Program Files\Windows CE Tools\wce420\POCKET PC 2003\Include\Emulator\oledberr.h"
#include "D:\Program Files\Microsoft SQL Server CE 2.0\Inc\ssceoledb2.h"

IDBDataSourceAdmin *pIDBDataSourceAdmin = NULL;

HRESULT hr= NOERROR;
ULONG cProps = 1;
DBPROP rgProps[1];
DBPROPSET PropSet;

// Create the SQL Server CE provider
//
hr = CoCreateInstance(CLSID_SQLSERVERCE_1_0,NULL,CLSCTX_INPROC_SERVER,
IID_IDBDataSourceAdmin,(void**)&pIDBDataSourceAdmin);
//Set the properties

if(SUCCEEDED(hr))
{
AfxMessageBox(_T("done"));
}
else
{
AfxMessageBox(_T("not done"));
}
for (ULONG i = 0; i < cProps; i++ )
{
VariantInit(&rgProps[i].vValue);
rgProps[i].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[i].colid = DB_NULLID;
}

rgProps[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].vValue.bstrVal = SysAllocString(OLESTR("\\Northwind.sdf"));

PropSet.rgProperties = rgProps;
PropSet.cProperties = cProps;
PropSet.guidPropertySet = DBPROPSET_DBINIT;

hr = pIDBDataSourceAdmin->CreateDataSource(cProps,&PropSet,NULL,IID_IUnknown,NULL);

if(SUCCEEDED(hr))
{
AfxMessageBox(_T(" done "));
}
else
{
AfxMessageBox(_T("not done"));
}
But the CoCreateInstance() and CreateDataSource () functions both are failing . In fact I have compilation errors and changed the installed ssceoledb.h with another latest sscedoledb.h patch from MS site . Still I could nt find out how to get the functions sucessful . And could u pls tell me how I could acheive the following.
1) The methods and code to access SQL Server database from SQL CE or ADO CE (if u know tell me The diffrence between SQL CE and ADO CE usage..most info availabe from MSDN are large and uncompiled )
2) How to get a set of data from SQL Server database into the emulator or device and then update the data and reload it back to SQL server .
I found that eVB has more sample and support than evC++ .
Thats it...will be very helpful if u could shed some light on this ...
with regards and thanks in advance
dharani babu s

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.