I have made Merge Replication using SQL Server 2000.Windows 2000 Server as Publisher and Windows 2000 Professional as Subscriber.
I am using Pull Subscription for that.
MSmerge_contents table is created on both publisher and subscriber database witch contains the information about the rows that are updated or inserted on any side.
Now we come to the problem.
“tblDefProductPrice” that contains the prices of products. I change the price of a single product so that when synchronization is done subscriber will get the updated price
Changing on row means only one row is updated so MSmerge_contents table should have only one row to propagate to subscriber but it is not when I saw the contents of MSmerge_contents table its showing the whole table with (8,000) rows.
I did not want because its extra work for publisher to publish 8,000 rows instead of propagating single row. In future it may increase to 80,000 that is loss of time and money.
I think you are misunderstanding the system table.
The merge agent will only replicate rows that have changed since the last synchronization - it doesnt, on each sync, replicate the whole MS_MergeContents table
strange...are there any foreign keys enforced on table, for replication?
if you do a select on matches on the rows rowguid, are their more than one match?
are there user triggers on the table?
when synchronization happens, does the agent report that it is delivering those 8000 changes?
Thanks for taking time to give solution to my problem.
I think i am not able to communicate the problem clearly. I will try again.
I have a table "tblProductPrice" in my DB. I insert some of the tuples, say 25, through SQL statement from VB code. When i do replication then the result shows "8000" updates, along with 25 inserts. This is equivalent to the total tuples present in that table. This table has got one foreign key. I have removed that contraint but the result is same.
There is another scenario. There is table "tblProductSeasonFitness". This has got two foreign keys but table has no direct relationship with above mentioned table of price. When i "Insert or Update" few tuples in this table (currently were are updating only two tuples at a time), then after replication there are "8000" updates in "Price" table mentioned above.
I have tried deleting the foreign key constraints but same result
I have deleted the price table, made new table, inserted the data again but same result
Guess we have to assume then that all the rows, at least in SQLs opinin, are being updated. Not sure if you are using .NET, if so, do you grab all the data as a dataset? Maybe when it returns the data it is mistakenly marking it as updated?
Are you sure the query you are sending is only affecting the necessary rows, as I have never had a problem like that in replciation (not that that writes it off!)?
If you have a test environment, try setting it up without replication and maybe use TIMESTAMP columns in the records to test which rows have been updated, after doing the exact same thing as you are doing that causes the problem.
Has your replication subscriber been properly initialized with the schema and data? If not it will regard everthing as a needed repl generation.
Also, test doing the update on the rows straight from Query Analyzer, to see if the prob lies in the App or SQL.
Hopefully by this you can isolate the area of the problem...