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.
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?