WII


Hi,

I need a urgent help! The problem is that every synchronization only transfer data from subscriber to publisher, but not the other direction. The publisher is sql server 2005 standard edition, and the subscriber is 2005 express. Is that any stored-procedure to deal with such a problem

Thanks for any commnet.



Re: merge replication, subscriber can only download but not upload??

Greg Y


can you describe your problem in more detail - is this a filtered publication, or are there any other publication/article properties that are set that we should know of Can you describer the changes made at the publisher that should be arriving at the subscriber




Re: merge replication, subscriber can only download but not upload??

WII

Thx for reply.

The publication is not filtered, and just a normal, standard merge replication. The situation is that I prepared each subscriber locally with the publisher, and they were running well when testing. After that, I took them to different remote locations. The subscribers now are communicating with the publisher by adsl VPN tunnel. What happened is that some of the subscribers only can download changes from the publisher, but cannot upload the changes to the publisher. So what i can do is to delete the subscriptions and re-create them. After that, they are working well.

I really want to know what on earth the problem is.

Thx for any consideration.







Re: merge replication, subscriber can only download but not upload??

enaksanamun

Heloo WII,

There is an option which is like "Subscribers download-only, prohibit changes" while creating the publication.

Its default is "Bidirectional".

The Merge Agent which is at the subscriber may not be working. Check out its History by clicking on its job and selecting View History.

Ekrem Onsoy






Re: merge replication, subscriber can only download but not upload??

WII

Thanks, Ekrem.

But most of other subscribers can upload changes to the publisher. So I'm really confused what's going on with the ones that not working properly.

BTW, does the replication on SQL 2005 express change a lot 'coz our system is working fine with the combination of sql server 2000 standard & MSDE.

Anyone can recommend some articles or books about the sql server 2005 merge replication the more detailed the better.

Thanks a lot.




Re: merge replication, subscriber can only download but not upload??

Michael Hotek

No. You will see virtually the same thing regardless of whether it is Express Edition, Workgroup, Standard, etc. You're going to have to provide a lot more detail on this.

1. What is your configuration

2. Are the subscribers actually connecting to the publisher and staying connected long enough to complete a synch cycle (upload first, resolve conflicts, and then download changes)

3. Are there any error messages

The more information that you give us, the better we can help.






Re: merge replication, subscriber can only download but not upload??

WII

Thanks Michael,

Because I'm new to SQL Server, actually I'm quite clear where to find the useful information. so sorry about that.

1. I'm using pull replication, non-filtered publication.

2.yes, i think so. the publisher and subscribers are connected by dedicated VPN tunnels.

3.yes, heaps....after each synchronization, each subscriber got the same error message "The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963). Get help: http://help/MSSQL_REPL-2147200963".
And in the SQL Server Log, i can find such kind of error message "Subscriber 'xxx' subscription to article 'docket_items' in publication 'yyy' failed data validation." Even I re-created the subscription from the scratch, it still came out. So I guess something wrong with the publisher




Re: merge replication, subscriber can only download but not upload??

Michael Hotek

We're going to need a lot more information than you could possible add to a forum post. Please open a support case with Microsoft and be prepared to send them backups of the publisher, subscriber, msdb, and distribution databases along with error logs and event logs. They'll have more specific information as well when you get to a support engineer.




Re: merge replication, subscriber can only download but not upload??

WII

Thanks Michael, thank you so much.

I think you are right. I'll do that.

Thanks for all the comments.




Re: merge replication, subscriber can only download but not upload??

WII

Hi guys,

I finally found out the error messages though the verbose log.

Here is part of it:

2007-05-18 11:01:57.062 Percent Complete: 0
2007-05-18 11:01:57.062 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2007-05-18 11:01:57.140 Percent Complete: 0
2007-05-18 11:01:57.140 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'cash_breakup' failed data validation (rowcount and checksum). Rowcount actual: 268, expected: 0.
2007-05-18 11:01:57.218 Percent Complete: 0
2007-05-18 11:01:57.218 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'docket_canceled' failed data validation (rowcount and checksum). Rowcount actual: 17, expected: 0.
2007-05-18 11:01:57.281 Percent Complete: 0
2007-05-18 11:01:57.296 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'docket_reprinted' failed data validation (rowcount and checksum). Rowcount actual: 484, expected: 0.
2007-05-18 11:01:57.375 Percent Complete: 0
2007-05-18 11:01:57.375 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'banked_amounts' failed data validation (rowcount and checksum). Rowcount actual: 2224, expected: 0.
2007-05-18 11:01:57.453 Percent Complete: 0
2007-05-18 11:01:57.453 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'docket_payments' failed data validation (rowcount and checksum). Rowcount actual: 8732, expected: 0.
2007-05-18 11:01:57.546 Percent Complete: 0
2007-05-18 11:01:57.546 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'credit_notes' failed data validation (rowcount and checksum). Rowcount actual: 856, expected: 0.
2007-05-18 11:01:57.625 Percent Complete: 0
2007-05-18 11:01:57.625 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'gift_vouchers' failed data validation (rowcount and checksum). Rowcount actual: 605, expected: 0.
2007-05-18 11:01:57.703 Percent Complete: 0
2007-05-18 11:01:57.703 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'laybys' failed data validation (rowcount and checksum). Rowcount actual: 576, expected: 0.
2007-05-18 11:01:57.781 Percent Complete: 0
2007-05-18 11:01:57.781 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'store_received_in' failed data validation (rowcount and checksum). Rowcount actual: 1107, expected: 0.
2007-05-18 11:01:57.859 Percent Complete: 0
2007-05-18 11:01:57.859 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'customers' failed data validation (rowcount and checksum). Rowcount actual: 4748, expected: 0.
2007-05-18 11:01:57.953 Percent Complete: 0
2007-05-18 11:01:57.953 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'stations' failed data validation (rowcount and checksum). Rowcount actual: 28, expected: 0.
2007-05-18 11:01:58.015 Percent Complete: 0
2007-05-18 11:01:58.015 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'dockets' failed data validation (rowcount and checksum). Rowcount actual: 14389, expected: 0.
2007-05-18 11:01:58.093 Percent Complete: 0
2007-05-18 11:01:58.093 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'docket_items' failed data validation (rowcount and checksum). Rowcount actual: 12414, expected: 0.
2007-05-18 11:01:58.171 Percent Complete: 0
2007-05-18 11:01:58.171 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'stocktake_items' failed data validation (rowcount and checksum). Rowcount actual: 80076, expected: 0.
2007-05-18 11:01:58.250 Percent Complete: 0
2007-05-18 11:01:58.250 Category:NULL
Source: Merge Process
Number: -2147200953
Message: Article 'store_received_items' failed data validation (rowcount and checksum). Rowcount actual: 25773, expected: 0.
2007-05-18 11:01:58.296 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.296 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.296 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.312 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.328 Disconnecting from OLE DB Distributor 'SURF-SERVER'
2007-05-18 11:01:58.328 Disconnecting from OLE DB Distributor 'SURF-SERVER'
2007-05-18 11:01:58.328 The merge process could not set the status of the subscription correctly.
2007-05-18 11:01:58.343 OLE DB Subscriber 'SURF-PSS1': {call sys.sp_MSadd_merge_history90 ( , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , )}
2007-05-18 11:01:58.343 [100%] Percent Complete: 100
2007-05-18 11:01:58.343 The process was successfully stopped.
2007-05-18 11:01:58.343 OLE DB Distributor 'SURF-SERVER': {call sys.sp_MSadd_merge_history90 ( , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , )}
2007-05-18 11:01:58.484 The Merge Agent was unable to update information about the last synchronization at the Subscriber. Ensure that the subscription exists at the Subscriber, and restart the Merge Agent.
2007-05-18 11:01:58.578 Percent Complete: 0
2007-05-18 11:01:58.578 Category:NULL
Source: Merge Replication Provider
Number: -2147200963
Message: The process was successfully stopped.
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Subscriber 'SURF-PSS1'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.578 Disconnecting from OLE DB Publisher 'SURF-SERVER'
2007-05-18 11:01:58.593 Disconnecting from OLE DB Distributor 'SURF-SERVER'
2007-05-18 11:01:58.593 Disconnecting from OLE DB Distributor 'SURF-SERVER'





Re: merge replication, subscriber can only download but not upload??

WII

These are the articles that failed the data validation, and there are much more other articles passed the data validation.

I'm just wondering that why not keep replicating when the row count is different between the subscriber and the publisher. isn't the replication's purpose to make them same

I'll appreciate any comment. Thank you. I'm really desperate now.




Re: merge replication, subscriber can only download but not upload??

Mahesh Dudgikar - MSFT

If you do not care about the validations, then you should look at your merge agent job and remove tihs part:

"-Validate 3". What this tells the merge agent is to do a validation and stop if there are errors.

Remove this and it will continue to pass.

However please do look at the real reason why there are differences between the publisher and the subscriber in the first place.






Re: merge replication, subscriber can only download but not upload??

WII

Thanks Mahesh,

I did put that parameter in the script.

Thank you very much!