Merge replication via cradle using ActiveSync

Hello. Is there any possibility to connect and proceed merge replication between SQL Mobile and SQL Server 2000 SP4 using ActiveSync? I need to deal with this situation:
- mobile application is working using self .sdf database but from time to time it connects to SQL Server and do some synchronization (synch or asynch, really doesn't matter). But, when there is no network connection (broken, suspended, wifi broken etc.) I have to create an option - Pocket PC will land into cradle and I have to synch all changes made in SDF and SQL Server using ActiveSync (or something like that - via USB).
So my question is : is there any simple mechanism to do this? Or I have to write some Active Sync manager which will be dealing with my replication? (pretty hard to do). Awaiting answer - Maciej Wysocki.

PS. I am using Windows CE 3.0 but I can setup Windows Mobile 5.0 too, I am just wondering if there is any chance to make this type of synch. working.

The merge replication over ActiveSync works since it uses http/https and ActiveSync provides pass thru http connections. You do not have to write anything for ActiveSync. Just cradle the device and try to sync it should work.If you have any problems, let me know, we can try to figure it out

Manish

|||Ok. Thanks for reply. I wanna know one more thing which I really need. Is there any chance to fire synchronization everytime when Pocket PC is put into cradle? I know that I can create a thread ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=596053&SiteID=1 ) with checking section, but it's not good solution (every new thread = less performance). So putting Pocket into cradle fires any event? Thanks in advance. Maciej Wysocki

PS. If getting Pocket out of cradle is detectable too I will be glad to get to know how can I check it.

Merge replication Primary Key violations

I have a Merge Replication Problem and I'm not sure how to proceed.

Given the following:

Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue

The replication is configured to resolve to the greater integer value.

Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)

One of the inserts will get to the Central Server first.
The other will form a conflict.

A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.

So now I have two different value fields and a conflict that cannot be resolved.

Is this fixable?
Do I need to make changes at athe Architechture level?

Hi Brian,

if you need to store both records from all subscribers, You should add a column for client id .So once tables replicates from all subscribers each client has their own id.

Example: if client 1 has the code 100 and client 2 has the code 200

the table at subscriber will be : 100,'Fred',1 (Record comes from client 1)

200,'Fred',1 (Record comes from client 2)

if you need to resolve the conflict and to store 1 record only you should assign priority for each subscriber

For more details check the following URL : http://support.microsoft.com/kb/315514

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I need to store only the higher of the two.

If the record already exists and both try to update the record, only the higher is stored, as desired.

But when the record does not exist, both subscribers do an insert, and it does not store only the higher.

Reading all the KB articles on merge replication, it appears that Updates and Inserts are supposed to be resolved the same.

I can't use subscriber priority, as I don't care WHERE the record came from, only the value stored.