none
SQL Server 2012 Merge Replication with SQL Compact 3.5 SP2

    Question

  • My server uses SQL Server 2012 for merge replication. I was able to create the replication, configure web synchronization, and run snapshot agent without any errors. I'm aware of the fact that SQL Server 2012 no longer support SQL Server Compact, so I used Visual Studio 2017 and SQLite/SQL Server Compact Toolbox to created a SQL Compact 3.5 database and saved it right on the server. However, when I tried to add a new subscription to this database, it gave me this error.

    Sync with SQL Server is 0% complete.
    Sync with SQL Server is 100% complete.

     Error Code: 80070057
     Message   : The server agent could not connect to the publisher.
     Minor Err.: 29049
     Source    : Microsoft SQL Server Compact
    Errors occurred during sync

    I checked the web synchronization and it is running fine (http://servername/folder/sqlcesa35.dll). Firewall should be ok as well since I have the 1433 port opened. On top of it, the SQL CE database is created right on the local server. Replication login user has db_owner to both distribution and replication databases. Is there anything that I missed?

    Previously, with the same setup, I was able to create subscription for the SQL Compact 3.5 when SQL Server 2008 R2. For some reason, the setup does not work with SQL Server 2012 anymore.

    Friday, March 10, 2017 11:44 PM

All replies

  • SQL Server Compact Merge replication with SQL Server 2012 requires the latest SQL Server Compact 3.5SP2 on the cliente side and SQL Server 2012 SP1 with at least the CU 5 (Cumulative Update):

    http://erikej.blogspot.pt/2014/04/merge-replication-with-sql-server.html


    Alberto Silva / www.moving2u.pt - R&D Manager / Former Microsoft MVP (2003-2015)

    Saturday, March 11, 2017 1:16 AM
  • I followed the instruction from Erikej's blog but not success. Here is my setup

    • Web Server (IIS) and SQL Server 2012 SP3 are on the same Windows Server 2012 machine.
    • SQL CE 3.5 at 3.5.8088. I also tried the latest one which is 3.5.8109
    • I created the compact database right on the server, so Server and Client are both using SQL 3.5.8088 for sure

    For some reason, my web server does not like x64 version of SQL CE. With x86 configuration (both Runtime and Server Tool), I can verify the link to sqlcesa35.dll working. I'm not sure if it was the cause. However, with the previous SQL Server 2008 R2, this x86 configuration was working. Any idea?

    Monday, March 13, 2017 10:39 PM