merge replication, subscriber can only download but not upload?

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

|||

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

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

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.

|||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?
|||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.|||Thanks Michael, thank you so much.

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

Thanks for all the comments.
|||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'

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

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.

|||Thanks Mahesh,

I did put that parameter in the script.

Thank you very much!

merge replication, subscriber can only download but not upload?

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

|||

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

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

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.

|||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?
|||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.|||Thanks Michael, thank you so much.

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

Thanks for all the comments.
|||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'

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

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.

|||Thanks Mahesh,

I did put that parameter in the script.

Thank you very much!

Merge Replication With SQL Server Express (Getting Started)

Hi

This is my first time working with replication and I was wondering if someone could point me in the right direction. I am looking for some sample code or a Web page that describes how to do merge replication using SQL Server Express on the client machines and SQL Server 2005 on the backend. If someone could help, I would appreciate it. Thanks.

If you are familiar with replication on other SKUs (such as standard edition), setting up on Express is not that complicated. You can treat Express just as another SKU, with the following exceptions:

1) Express can only be a subscriber.

2) There is no SQL Server Agent on Express. Thus if you have pull subscription, you can use one of the following ways to initiate synchronization on Express:

a) Using Sync Manager

b) Using ActiveX

c) Calling replmerg.exe (for merge subscription) or distrib.exe (for transactional subscription) with the proper parameters directly.

Merge Replication Questions [SQL2k5 non express]

I can choose synchronization direction for articles: a) Bidirectional b) one way

1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/

2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.

Thanks for the answers in advance

I guess that you want to keep same schema's at two or more machines?

I do not know whether you can do it using replication, actually I think that there is no way to do something like that.

What I would do is that I would script database, and make same copies at all locations. Later when you need some updates/changes to schema, you can script those also. Not only that, but you can build your own schema replication system, so everything could go, kind of, semi-automatic.

|||

Sorry, my initial question was not clear.

I would like to replicate all tables in the db, except 1-2 tables and 3-4 columns only.

Let's see the following example:

There are about 30 tables to replicate let's name those T1, T2, T3, ... T30 and the column names are T1C1, T1C2, ... T2C1, T2C2, .... etc

I would like to replicate all tables, except T15 and T16 tables (all columns) and 4 columns T8C4, T8C5, T9C2 and T9C3. But the schema should be the same at all places, so T15 and T16 should be exist at subscribers and publishers and the mentioned columns also, but data should not be replicated to-from that 2 table and from/to that 4 columns.

|||

Now it is much clearer to me.

As I said, you can copy your schema to be same on all databases, but you can filter out your publication so you just replicate tables T1-T14 and T17-T30, and to replicate all columns except T8C4, T8C5, T9C2 and T9C3.

If you then choose to initialize, you will loose tables/columns that are not in replication, but you can add them later.

Or you can make publication, then copy db schema using script to subscriber (so you have rowguid) and choose do not initialize.

Test it, play around with it. Make on your own sql server two tiny db's with two tables (one publisher and one subscriber) and play with it.

|||

THis can be done with replication. What you do is create a publication containing all the tables and columns you want. You can then script out table T15 and T16, put it in a file, and reference it in parameter @.post_snapshot_script for stored procedure sp_addpublication or sp_addmergepublication.

|||

Thanks for the answer. It took a bit longer, because I ran into a little problem, I got the following error message on one of my stored procedure in post-snapshot script:

"The query processor could not produce a query plan. For more information, contact Customer Support Services."

It was because i forgot to include the:

set QUOTED_IDENTIFIER ON

maybe this is a bug of sql2k5 SP1

btw, I moved the T8C4, T8C5, T9C2 and T9C3 columns to separate tables as well, and there are foreign keys pointing back to the original tables PKs

Thanks again for the solution.