September 12, 2010 at 11:23 am
I have set up database ReplTesting to replicate a publication of three articles (Production.Location, Production.Product, Production.ProductInventory), and successfully set up database SubsTesting as a subscriber.
I 'viewed synconization status', clicked 'start' to initiate the Distribution Agent and synchronize the Publisher and Subscriber, and the agent completed the replication. However, when I execute the following queries, only the first executes:
USE ReplTesting
UPDATE Production.Product
SET Name=Name+'updated at publisher'
WHERE ProductID=2
USE SubsTesting
UPDATE Production.Product
SET Name=Name+'updated at subscriber'
WHERE ProductID=2
I receive the following message:
"(1 row(s) affected)
(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure MSmerge_upd_8D36E7D5AB984908BE1698E315A40F23, Line 40
Invalid object name 'dbo.MSmerge_genvw_8D36E7D5AB984908BE1698E315A40F23'.
"
Viewing the two Product tables confirms that the publisher has been updated and the subscriber hasn't.
When manually attempting to modify subscriber's product table I receive a similar message:
"No row was updated
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Invalid object name 'dbo.MSmerge_genvw_8D36E7D5AB984908BE1698E315A40F23'
Correct the errors and retry or press ESC to cancel the change(s).
Thanks,
Mark:-)
September 17, 2010 at 8:41 am
It's been a while since I've played with replication, but I'll give this a shot.
How is your replication set up? Do you have Updating Subscribers? If not, chances are your replication is set up with one-way data flow, from Publisher to Subscriber.
September 17, 2010 at 8:54 am
I think assumed that a two-way data-flow would be the default state for a 'merge' replication set-up. The text book talked about a two-way data flow being the main feature & benefit of the merge rep tool. I'll have to take a closer look at this...
This might sound a little dim, but could you suggest where I look to verify and alter this setting?
Thanks for responding; this discussion will fuel my search for a solution this evening. I spent the whole of last weekend getting no-where with this.
cheers,
mark
September 17, 2010 at 11:44 am
Mark,
Making assumptions with SQL Server is the quickest way of losing a database. Be careful about that.
Did you set up replication yourself or are you working with someone else's setup?
If you set it up, tell me what model you're using and what type you're using. Describe it all from Publisher to Distributor to Subscriber. If someone else set it up, I'm not sure where to tell you to look. I'm going to have to back through the books myself to find it. I'll post when I do.
The biggest thing to remember is that Merge does not mean both sets of data are updating. Merge means that if two different publishers or subscribers are trying to update the same piece of data, the one with the highest rank ID (some sort of unique timestamp GUID) wins the argument and gets to update the data. You can't update on both sides like you're trying to do.
Google "SQL Server 2005 Replication Basics" (without the quotes) and it comes with a whole list of articles you should start reading.
September 17, 2010 at 4:36 pm
Okay, It looks like it's publication type. Hover your mouse over the publication in SSMS (under Replication -> Local Publications) and Publication Type is fourth down.
I'm walking through this myself. Both Merge and Transactional Replication with Updating Subscribers (the type I'd been thinking of in my first post) have the same description in the lower half of the window:
Merge publication:
The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.
Are you working with Compact Edition?
Now, at in one of the publication setup screens, I'm seeing Article Properties and a check box that says Table Article is download only, which may prevent you from updating at the subscriber.
So I set up replication between AdventureWorks (publisher) and AdventureWorks_BT (subscriber) for Person.Contact and Person.Address. Merge replication with publisher, distributor and subscriber on the same instance. I marked Person.Contact as a Download Only table Article, but not Person.Address. When I made changes to the Suffix column of the Person.Contact table on both the publication DB and the subscription DB, it allowed me to change both, no errors.
I also changed a field in Person.Address on both dbs with no problem.
You're going to have to provide more details (see my last post) before I can further assist you in troubleshooting this problem.
BTW, Baya Dewald has an excellent article on setting up replication that might assist you with troubleshooting (by walking through replication step by step)
September 18, 2010 at 12:20 pm
Thanks Brandie,
Mercifully this is just a test database, and the setup is based on the 'Configuring Conflict Resolution for Merge Replication' chapter of the MCTS 70-431 textbook. The purpose of the chapter is to guide the reader through the configuration of merge replication and to demonstrate that the conflict resolution setting work by simultaneously changing the same record on the product table at the publisher and subscriber.
I'm using Enterprise Ed. on Win Server 2003, on a Virtual PC.
I found that Publication Properties/Articles/Article Properties/Properties/Syncronization Direction, for all three articles in the publication, is set to 'Bidirectional' (which sounds right for merge replication?).
I have now deleted the configuration and will construct it again, and post the steps taken:
Create new subscription database:
Right-click the Databases folder and choose 'New Database'
In the Database text box, type 'SubTesting' as the database name
Click OK
Configure Distribution:
Right-click Replication folder and choose 'Configure Distribution'
Select 'SERVER1 will act as its own Distributor'; SQL Server will create a distribution database and log'
Set SnapShot folder as 'C:\ReplicationPractice\AWsnapshot'
Distribution database name: distribution
Folder for the distribution database file and log: C:\ReplicationPractice\Distribution
Publisher: SERVER1
Distribution Database: distribution
Wizard actions: configure distribution box ticked
Configure a Merge Publication:
Expand the Replication folder and right-click Local Publications
Choose 'New Publication'
Publication Database: AdventureWorks
Publicaton Type: Merge Publication
Subscriber Types: SQL Server 2005
Articles page: Tables 'Location, Product & ProductInventory' Selected
(The Product table will use the standard conflict resolver and the Location and ProductInventory tables will use the COM-based resolvers.)
Location table selected
Article Properties/Set Properties of Highlighted Table Article/Resolver tab
Select a Custom Resolver (Registered At the Distributor) selected
Selected 'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver.
In the Enter Information Needed By The Resolver text box 'ModifiedDate' entered
Clicked OK
ProductInventory table selected
Article Properties/Set Properties of Highlighted Table Article/Resolver tab
Select a Custom Resolver (Registered At the Distributor) selected
Selected SQL Server Minimum Conflict Resolver
In the Enter Information Needed By The Resolver text box 'Quantity' entered
Clicked OK
Article issue warning displayed about unique identifier columns to be added to tables
Filter Table Rows, click next
SnapShot Agent Page: both boxes ticked (Create SnapShot Immediately & Schedule SnapShot...)
Schedule set to run weekly on Sundays at 00:00
Agent Security page : Security Settings clicked
'Run Under The SQL Server Agent Service Account' selected
Under 'Connect To Publisher' section 'By Impersonating the Process Account' selected
Wizard Actions Page: both boxes ticked (Create the publication & Generate a script file with steps...)
Script File Properties page set to 'C:\ReplicationPractice\Scripts\CreateInventoryPublication.sql'
If file exists 'Overwrite existing...' selected
File format: international text(Unicode) selected
Publication name: Inventory
Subscribe to the Merge Publication:
Expand the Replication, Local Publication folder
Right-click 'Inventory' pubication
Selected 'New Subscription'
Inventory publication selected
Merge Agent Location: 'Run all agent at the Distributor...' selected
Subscribers Page: Subscriber=SERVER1 Subscription Database= SubsTesting
Merge Agent Security page: 'Run Under The SQL Server Agent Security Account' selected
Connect to the Publisher and Distributor: = by Impersonating the Process Account
Connect to the Subscriber = by Impersonating the Process Account
Syncronization Schedule: Define Schedule = weekly/Sunday/00:30
Initialize Subscriptions page: default 'Initialize Immediately' left as-is
Subscription Type page: Subscriber = SERVER1, Subscriber Type = Server, Priority for Conflict Resolution = 75.00
Wizard Actions page: both boxes ticked (Create the subscription(s) & Generate a script file with steps...)
Script File Properties page: file name set to 'C:\ReplicationPractice\Scripts\CreateProductsSubscriptions.sql', and 'Overwrite existing file..' selected
=====================
So, here we go again...I'm going to have a bite to eat and then I'll try simultaneously updating the 'Product' table at Publisher and Subscriber, and we'll see if the conflict resolution works as it should...
Thanks again for all your feedback, Brandie.
Mark 🙂
September 18, 2010 at 2:57 pm
Ok,
So again I execute:
USE AdventureWorks
UPDATE Production.Product
SET Name=Name+'Updated at Publisher'
WHERE ProductID=1
USE SubTesting
UPDATE Production.Product
SET Name=Name+'Updated at Subscriber'
WHERE ProductID=1
and receive the following message:
"(1 row(s) affected)
(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure tuid_ProductAudit, Line 5
Invalid object name 'Production.ProductAudit'."
And the Subscriber Product table is not updated.
I gather from a google search that this message could suggest that there is an issue with the table's owner...
September 18, 2010 at 2:59 pm
The owner of the Distribution DB is 'sa'...
September 18, 2010 at 4:44 pm
Oh, it appears that if I now 'View Syncronization Status' and press 'Start' then the changes in both tables are replicated, producing the intended 'conflict'. So the "Msg 208, Level 16, State 1, Procedure tuid_ProductAudit, Line 5
Invalid object name 'Production.ProductAudit'" message isn't an indication that merge replication isn't configured correctly...
September 18, 2010 at 4:49 pm
mmm. In the Conflict Viewer, the Conflict Loser displays the following error message:
A row update at 'SERVER1.AdventureWorks' could not be propagated to 'SERVER1.SubTesting'. This failure can be caused by a constraint violation. Invalid object name 'Production.ProductAudit'.
So there is still an issue here.
Sorry for the scatter-gun approach to posting.
mark 🙂
September 20, 2010 at 10:13 am
cranmer.mark (9/18/2010)
Thanks Brandie,Mercifully this is just a test database, and the setup is based on the 'Configuring Conflict Resolution for Merge Replication' chapter of the MCTS 70-431 textbook.
AHA! Did you get the errata for that book?
ERRATA Pg. 755, Practice 4:
The 2nd UPDATE statement in step 7 will fail if the trigger tuid_ProductAudit created in Chapter 9, Lesson 3, Practice 1, still exists on the table Production.Product when the ReplTesting database is first created.
Before attempting to perform Practice 4, you need to delete the trigger tuid_ProductAudit from SubsTesting.Production.Product in order to successfully complete the practice.
Easy solution. Better still, YOU haven't done anything wrong. It's all them. @=)
FYI: Always check the publisher's website for errata before running through the exercises or using the book for study purposes.
September 20, 2010 at 11:25 am
oh, what a gaff!
That's a proper newbie mistake; running round in circles assuming you've missed something. A very salutary lesson for any other newcomers to IT training...
big thanks for your help.
Mark 🙂
September 20, 2010 at 11:37 am
You're very welcome. I'm so sorry it took so long and glad that you posted you were working out of that book.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply