The schema script 'xxxxsch' could not be propagated to the subscriber.

Question

I am currently trying to configure a system for Merge Replication with Mobile devices.  My environment is as follows:

DB1:

Windows Server 2003 Standard Edition SP2

SQL Server 2005 Standard Edition SP2

SQL Server Compact Edition 3.5 Server Tools

WEB1:

Windows Server 2003 Standard Edition SP2

IIS 6.0

SQL Server Compact Edition 3.5 Server Tools

 

********N.B. – WEB1 and DB1, are NOT on the same domain and they are NOT on the same subnet.

I am trying to follow the following tutorial to set up Merge Replication:

http://technet.microsoft.com/en-us/library/ms171908(SQL.90).aspx

STEP1: Creating the DB

I created a very simple database that I want to publish articles from using the following script:

USE master;

GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'SQLMobile')

BEGIN

    DROP Database SQLMobile;

END

GO

CREATE DATABASE SQLMobile;

GO

USE SQLMobile;

GO

CREATE TABLE MembershipData (MemberID INTEGER IDENTITY (1,1)

    CONSTRAINT pkMemberID PRIMARY KEY, MemberName NVarChar (50));

CREATE TABLE FlightData (MemberID INTEGER FOREIGN KEY REFERENCES

    MembershipData(MemberID), Destination NVarChar (50), FlightStatus

    NVarChar(50), ArrivalDate DATETIME, FlownMiles INTEGER);

 

I then insert a few rows into each table.

 

 

STEP2: Creating the Publication

 

I create a new publication by right clicking on “Local Publications” in SQL Server Management Studio (I am even doing this on the actual DB1 machine) and go through the New Publication Wizard. I select “SQLMobile” as my DB. ”Merge Publication” as my Publication Type. “SQL Server 2005” and “SQL Server 2005 Compact Edition”, I select all the tables in SQLMobile as articles to be published. No Filter Tables.  Create a snapshot immediately and default schedule are both checked. For run un ther following account I enter:

DB1\snapshot_agent(p@ssword) (a test account I have set up on DB1 and have checked passwords probably 10 times at this point).  I connect to the publisher with sysadmin account (for testing purposed only). Finally, I anme the publication SQLMobile.

 

Next I verify the snapshot has been created and it is located in:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata

 

I then share this folder and verify that the DB1\snapshot_agent has read/write privelages.

 

STEP3: Configuring the Publication for Web Sync

(This now is all done on WEB1)

I create a windows account WEB1\snapshot_agent(p@ssw0rd).  I then log onto WEB1 with the snapshot_agent account and make sure I have read/write privelages to the \\IP OF DB1\repdata folder described in step 3.

 

Next I set up the publication for web sync.  I launch the Configre Web Synchronization Wizard (ConnWiz.exe, SQL CE 3.5). I select SQL Server Compact as the type of subscriber. Create a new virtual directory on WEB1 and anme it SQLMobile, with the path C:\inetpub\wwwroot\Sync.  I am not using SSL for this as this is a test.  I select “Clients will be authenticated” for Client Authentication.  I select “Integrated Windows Authentication” for Authentication access.  I then add WEB\snapshot_agent account to the Directory Access.  In the Snapshot Share Access section I set the path to:

\\IP OF DB1\repdata

 

The virtual directory is then created.  I verify that everything looks ok by navigating to http://sync.XXX.com/SQLMobile/SQLCESA35.dll?diag and see that everything that looks OK, except the SQL Server 200 connection (chich does not matter in this case)

Reconciler Test
Reconciler	Status	ErrorCode
9.0 Database Reconciler	SUCCESS	0x0
8.0 Database Reconciler	FAILURE	0x80004005

 

STEP4: Creating the Subscription

***Note I have tried this step from DB1, WEB1, and a remote machine with the same results

I create a new compact edition DB (.sdf file) on my local machine.  Launch the Add New Subscription Wizard.  To select the publisher I log into DB1 and select the SQLMobile Publication.  Name the subscription “SQLMobileTestSub”.  In the URL for the SQL Server CE virtual directory I enter:  http://sync.XXX.com/SQLMobile/SQLCESA35.dll .  I supply a SQL sysadmin credential for SQL login and try to create the subscription.  The wizard errors out in the “Synchronizing Data” step (although it does get to 100%)with the following error on the wizard:

- Synchronizing Data (100%) (Error)

Messages

· A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)

The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\DB1 _SQLMOBILE\20090805204475\MembershipData_2.sch' could not be propagated to the subscriber.
HRESULT 0x80070003 (0)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
HRESULT 0x80045017 (0)
 

· The operation could not be completed.
 

 

On the Virtual Directory in the log file I see the following error:

2009/08/05 20:58:12 == SQL CE Server Agent Version ==> 3.5.5386.0

2009/08/05 20:58:12 == SQL CE Server Agent Version ==> 3.5.5386.0

2009/08/05 20:58:12 Hr=00000000 SQLCE Server Agent Loaded - Version 3.5.5386.0

  0

2009/08/05 21:03:22 Hr=80040E4D ERR:OpenDB failed getting pub version 28627

2009/08/05 21:05:02 Thread=15C RSCB=2 Command=SYNC Hr=80070003 The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\DB1 \20090805204475\MembershipData_2.sch' could not be propagated to the subscriber. -2147024893

2009/08/05 21:05:02 Thread=15C RSCB=2 Command=SYNC Hr=80045017 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. -2147201001

I see no significant errors in the event viewer on WEB1.

I see the following error on the event viewer in DB1:

Replication-Replication Merge Subsystem: agent 0039007B-0038-0039-3700-320038003500 failed. The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\DB1_SQLMOBILE\20090805204475\MembershipData_2.sch' could not be propagated to the subscriber.

 

One point of note is the athe User in the event is “N//A”, not sure why I assumed it should be the snapshot account.

 

Finally, In the event viewer on DB1 I get the same error as in the wizard:

Error messages:

·          The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\DB1_SQLMOBILE_SQLMOBILE\20090805204475\MembershipData_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024893)
Get help: http://help/MSSQL_REPL-2147024893

·          The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

 

I have been working on this for a few days and can not get past this issue, has anyone been able to work through this? 

 

Any help would be extremely appreciated!!

 

Thanks!

Answer 1

Barak,

Did you resolve this issue? We are having a similar problem.

Thanks,

Jim

Answer 2

Hi Jim

I'm getting the same problem. Have you got any new? I'm looking for an answer for days and I got nothing :(


Thanks

Jesus

---

VB Developer

Answer 3

This is normally a permissions issue. The agent can't get read or list files and folders access to the share/folder where the snapshot is.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hi Hilary,

We are wondering which agent account wouldn't have permissions? We have given everyone full control on the repldata folder on the database server. We can see the share when we are on web server so we were thinking we eliminated any permissions issues. We have also tried creating a compact database on the database server where the publication is at and went through the process (wizard) of setting up the subscription from the SQL Compact 3.5 database to the publisher but, still get the same error

A call to SQL Server Reconciler failed. Try to resynchronize. HRESULT 0x80004005 (29006)

The schema script 'E:\Program Files\Microsoft SQL Server\MSSQL10.XXXISQL01\MSSQL\ReplData\unc\WRXXDSQL01$XXXISQL01_BUY_BUY\20100219060508\Buy_2.sch' could not be propagated to the subscriber. HRESULT 0x80070015 (0)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. HRESULT 0x80045017 (0)

TIA,

Jim

 

Answer 5

Jesus,

Is the snapshot you are trying to retrieve on the database or web server share? We are finding that if the snapshot is on the web server repldata share we can replicate (to Hilary's point about it being a permissions issue).

Jim

Answer 6

We found out that the default location of the publication's snapshot had to be unchecked and we put the UNC of a share on the web server to put the location that you could specify. This post http://msdn.microsoft.com/en-us/library/ms171864.aspx addresses it indirectly in the snapshot section. A good clarification for it would be "If you are using web synchronization then you will need to do this." The lightbulb came on after the fact that when the error message was
Message
Replication-Replication Merge Subsystem: agent 0039007B-0046-0031-4500-320031003300 failed. The schema script 'E:\Program Files\Microsoft SQL Server\MSSQL10.<instance name>\MSSQL\ReplData\unc\<db server>$<instance name>_<publication name>\20100223155934\xxx.sch' could not be propagated to the subscriber." that the web server didn't know where "E:\Program Files...." was. DOH!

As an fyi... Using the registry setting to have verbose logging on for SQLCESA35 helps a lot with debugging.

Jim

Answer 7

This is Known bug you need to install sp
http://support.microsoft.com/default.aspx?scid=kb;en-us;962003&sd=rss&spid=2855

Thanks
Bhushan

Answer 8

Hi freinds,
U are so far from me.ME my problem is in the step3 of Balak.I have a problem when synchronizing web publication.
I begin by giving you an insight into my soft installed:
-sql-server 2005
-windows 7even so IIS7
I created a publication MaPublication.I made a right click on publication-> Release Local> MaPublication then I chose configure synchronization Web.Then, next-> sql server mobile-> created a new directory select default website ->next->in Alias: SQLCE-> 2 messages appear successively OK OK-> next-> clients connect anonymously-> next-> OHH now when I click on the Edit tab to change a user account error message appears:

1. The program can not open the required dialog box because no location has been found.Close this message and try again

What is the problem? Is it a problem IIS??
Thank you for your contribution
Note: the MSDN help don't help me anymore so another propositions