When conflict occur, I want the user to be able to select witch row to keep and witch to delete.
I've look for system stored procedure, that could help me do the resolution but I've founded nothing.
So I've thought that I could do it by hand, with UPDATE/DELETE query
Take the row in the %TABLE_NAME%_Conflict table and copy it into the real table, than delete the row.
I have been evaluating merge replication, 'push' subscription on SQL Server 2014. If the default resolver is used (I refer to @article_resolver parameter of sp_addmergearticle), all seems to work as expected. However if I use "Microsoft SQL Server Subscriber Always Wins Conflict Resolver" (or any other MS standard resolver for that matter), if the Subscriber is on a different machine, the merge agent invariably gives the following error: "The process could not initialize <resolver_name>. Verify that the component is registered correctly."
This does not happen if the Subscriber is on the same machine as the Publisher and Distributor.
The problem seemed to exist in SQL Server 2008 according to some posts but it has been apparently fixed since then. I've tried the following:
- @partition_options = 0, as was suggested somewhere.
- Copied ssrpub.dll (the resolver dll) to the Subscriber machine (should not really matter as this is 'push' subscription?)
- Registered ssrpub.dll with regsvr32 on the Publisher/Distributor machine.
I've also run sp_enumcustomresolvers on the Publisher machine, and it happily showed all standard resolver, including the "Microsoft SQL Server Subscriber Always Wins Conflict Resolver".
Another thought is, I'm using SQL Server Express as the Subscriber (on the remote machine). Perhaps it does not support custom resolvers? (I'm using the full SQL Server in the 'local subscriber' variant, which does work OK as I mentioned before).
Note also that if I create a new publication via SSMS, the Resolver tab of the Article Properties dialog is empty, i.e. it does not list any resolver. The same tab contains the full list of resolvers though, if opened for an existing publication.
I have created a custom conflict resolver for an article that is using Merge replication. I can register my custom resolver OK, I can change my merge article to use my new custom resolver, but when a conflict occures the replication monitor shows the following errors:
Error messages:
No signature was present in the subject. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2146762496) Get help: http://help/MSSQL_REPL-2146762496
The Custom Resolver Component for article 'BRANCH' does not have a valid digital signature. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147198713) Get help: http://help/MSSQL_REPL-2147198713
At first I thought it was because my assembly wasn't signed, so I signed it. I still get the same error.
I have various debug statements in the assembly and I can see that Initialize and HandleChangeStates gets called ok, but no other overrides.
I'm trying to setup the Interactive Conflict Resolver in my C# app via RMO. (using SQL Server 2005)
The only article I can find on MSDN or anywhere is involing T-SQL. Can someone please shed some light on this for me specificaly on the subscriber side.
Hi, I'm replicating a database between two instances of Sql 2000 using Merge Replication. I have no custom resolvers at present but I'm seeing something unexpected.
I need to write a custom conflict resolver for an application using merge replication. It's relatively simple logic - compare the two rows from the publisher and the subscriber and the winner is based on the value of one particular column.
Reading BOL gives me the input parameter list for the sp, and specifies that the output should be exactly the winning row. What is doesn't give is any example of how to do this, in particular how to access the two rows in conflict so that they can be compared.
I can do this by dynamically building SELECT statements, one connecting to the table locally on the publisher and another connecting to the subscriber using the form SERVER.DATABASE.OWNER.TABLE, but this requires me to explicitly have the subscriber as a linked server to do this. An example of my revolting code is appended to this post.
Is this what I have to do, or is there some table I can access on the publisher that has the conflicting rows in so I can compare them without going back to the subscriber?
Thanks for your help
Richard
---- code sample --
ALTER PROCEDURE [dbo].[prRep_ResolveInventoryConflicts]
CREATE TABLE #ConflictingRows ( [Machine] [sysname]NOT NULL, [LocationID] [int] NOT NULL, [PartGUID] [uniqueidentifier] NOT NULL, [Qty] [int] NOT NULL , [LastUpdatedDtm] [datetime] NOT NULL, [LastUpdatedUser] [varchar](50) NOT NULL , [rowguid] [uniqueidentifier] NOT NULL
)
-- Build the T-SQL To run against publisher and subscriber
SET @Publisher = @@Servername SET @qryPublisher = 'INSERT INTO #ConflictingRows SELECT '+@Publisher+' as Machine, * FROM '+@tableowner+'.'+@tablename+ 'WHERE rowguid = '+CAST(@rowguid as varchar(40)) +';'
SET @qrySubscriber = 'INSERT INTO #ConflictingRows SELECT '+@subscriber+ ' AS Machine, * FROM '+@subscriber+'.'+@subscriber_db+'.'+@tableowner+'.'+@tablename+ 'WHERE rowguid = ' + CAST(@rowguid as varchar(40)) +';'
-- execute the stored procedures
EXECUTE @qryPublisher; EXECUTE @qrySubscriber;
-- Compare the two rows and return the winning row, ie the one that was last updated by a human checking the inventory
SELECT @RecentCheck = MAX(LastUpdatedDtm) FROM #ConflictingRows
SELECT TOP 1 * FROM #ConflictingRows where LastUpdatedDtm=@RecentCheck;
I'm trying to create a custom conflict resolver for SQL Server 2000 in C# but i can't seem to find any source code examples of a *.dll where i can actually see what needs to be done.
I am working on a COM-based custom conflict resolver (vb.net) for a merge replication problem that I am having. This is what I am trying to accomplish. 1. Have a Last_Upd_Dt column. 2. When there is a conflict between publisher and subscriber on a column, the winner is the most recent date in Last_Upd_Dt and want the value for that column from that source. 3. If there is no conflict on a column and the column was updated by either the publisher or subscriber, I want to keep the change made by the publisher or the subscriber. (i.e publisher made change in col #1, subscriber made change in col #2, no conflict, the resulting row will now have the col #1 value from publisher, col # 2 will have value from subscriber. Essentially merging the changes.)
Does anyone have any examples of a COM-based Conflict Resolver that implements ICustomResolver for SQL Server 2005? Can this be done using the Business Logic Handler (examples?) ?
I see that MS has a conflict viewer that can be accessed by managment studio, however I would like to create one with some customized options. Is there a sample of code somewhere to start this? I could really find anything in BOL. Thanks in advance.
I have a custom conflict resolver - which fires on update conflicts (using row level tracking)
I have had a couple of occasions when the resolver has failed with the following error:
"The schema of the custom Dataset object implemented in the business logic handler does not match the schema of the source Dataset object. Verify that the custom Dataset object has been correctly defined"
In both cases I found that the row for which a conflict was being handled was not a conflict at all. One was a straightforward non conflicting update at the publisher and the other was a similar update at the subscriber.
I got round the problem by temporarily using a fix version of the conflict resolver dll that either set the custom Dataset to the publisher dataset or the subscriber dataset - depending on where the update had occurred.
When the first error (publisher update) occurred - the resolver code was basing the custom dataset on the publisher dataset - which was presumably empty - so I changed the code to base the custom dataset on the subscriber dataset. The second error therefore occurred when the custom dataset was based on the subscriber dataset - which again was presumably empty
Note that the tables involved in each occasion were different and neither table is filtered.
Is there a known bug in this area?
I am considering trying to change the resolver code to identify false conflicts in order to workround the problem - but this would be difficult to test as I can't reproduce the problem
I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values. The values saved to the test table are correct but some of the values saved as the conflict winter are truncated.
Example a char(3) filed is updated at the subscriber as €˜111€™ and updated at the publisher as €˜222€™, in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as €™11 €˜, if I use the publisher value the conflict resolver updates the field as €™22 €˜. Now the same records is saved to the test table correctly as either €˜111€™ or €˜222€™ depending on the logic I used. So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated. Has anybody run into this before and what steps can I take to avoid this.
I'm trying to create a custom resolver for merge replication exactly like in the MS example.
It seems to work, but only ONE time. If I change, insert or delete a record in a table the second time, the subscriber monitor comes with the following errors:
Error messages:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
The Merge Agent encountered an error when executing code in the 'UpdateHandler' method implemented in the business logic handler 'D:Program FilesMicrosoft SQL Server90COMMyResolver.dll'. Ensure that the overridden 'UpdateHandler' method has been properly implemented in the business logic handler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
This last error is of course dependant on my action (update, delete, insert).
My code is -exactly- like the example (I just stripped out the log message).
Does anyone know why I am "trying to read or write protected memory" ?
The thing is that I'm trying to create an application that detects if a table changes. Is this the right way to do this anyway or are there better solutions?
I have some rogue replication conflict tables that I can't delete because they are system tables. They are names aonflict_<tablename> and bonflict_<tablename>. They are definitely not needed anymore. How can I delete them ? Does anyone know how to delete a system table ?
Is there a way to replicate all data in a table but on certain columns avoid throwing a conflict? I have serveral SQL servers running Tx-Rep and almost all of the tables contain a DateModified field. I don't really need to check for conflicts on this data but want to keep it in tact as much as possible.
we are running business critical application in 24*7 enviroment. Application is written in java and use JDBC 1.2 to read and write data into SQL 2005 SP2. We also use push transactional replication and as a part of this replication we run snapshot agent regulary.
Problem is that our java application receives regulary error: "Could not complete cursor operation because the table schema changed after the cursor was declared." After some further investigation with Profiler we found out, that snapshot agent calls sp_MSreplupdateschema with high probability this procedure changes at least modify_date of some tables included in replication. In profiler we also found that JDBC broadly uses cursors to access data. After the call sp_MSreplupdateschema all cursores declared before the call failed.
We tried to setup JDBC driver not to use cursors by setting SelectMethod=direct in configurations file. But this started deadlocks.
From my point of view it`s legitim to use cursors and snapshot agent together. The question is why snapshot agent modify table schema and how to solve this problem?
I send same question into Replication forum. Till now without any answer. I hope to have more luck there.
let's say i have three publications running on a network of one central server and four shops.
- a merge replication that keeps the price-table up to date between all shops and the central server
- a transactional replication of the arrived_orders-table from the central server to the shop in case
- a transactional replication of the sold_products-table fromt he shops to the central server.
the whole replication network runs on a distribution database that is located on the central server.
now, i've noticed already a few times, when a conflict occurs in one of the replications, also all other replications stop working. (for example when the transactional replication can not insert a line in the sold_products table because of a violation against a UNIQUE constraint in the central server).
Is it normal that also the merge replication and the other transactional replication don't do their inserts, updates etc any more????
How can I get sql server to log to the windows event long when a conflict occurs so that I can set up a notification alert. I have gone to the replication alerts in enterprise manager and set one up but it is never triggered and there is never anything in the windows event log.
Msg 207, Level 16, State 1, Line 1 Invalid column name 'origin_datasource_id'.
When I run the "Microsoft Replication Conflict View" I am prompted to pick a table. Here are the details of the error message I receive.
===================================
CMCustomer is neither a DataColumn nor a DataRelation for table summary. (System.Data)
------------------------------ Program Location:
at System.Data.DataRowView.get_Item(String property) at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetWinnerSQL(DataRow loserRow, Boolean blockFetch) at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetSourceRow() at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetailData() at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetail() at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.summaryInfoGrid_SelectionChanged(Object sender, SelectionChangedEventArgs args) at Microsoft.SqlServer.Management.UI.Grid.GridControl.OnSelectionChanged(BlockOfCellsCollection selectedCells) at Microsoft.SqlServer.Management.UI.Grid.GridControl.SelectedCellsInternal(BlockOfCellsCollection col, Boolean bSet) at Microsoft.SqlServer.Management.UI.Grid.GridControl.set_SelectedCells(BlockOfCellsCollection value) at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.ResetSummaryGrid() at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.LoadConflict()
When I try to "Remove" then conflict (Conflict type - "4(Update/Delete, Update wins)") I receive the following...
===================================
Column 'CMCustomer' does not belong to table summary. (System.Data)
------------------------------ Program Location:
at System.Data.DataRow.GetDataColumn(String columnName) at System.Data.DataRow.get_Item(String columnName) at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.SetLogInfo(DataRow logRow, String sql) at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.btnClearUpper_Click(Object sender, EventArgs eg)
I am using MSDE 2000 for my replication. the problem is that althought at time creating publication it inserts GUID into every table but if one table in 2 nodes has same primary key, it inserts only one row ( accorrding to prority ). there are some Conflict Reslover methods that can be used for this purpose. i wanted to ask that is there any other way for me to resolve this conflict. i am asking for a new way because my database schema has been created and a lot of coding behalf of that schema has been done.
I faced with strange problem. From time to time, during syncronization I get the following errors at the subscriber:
ERROR: -2147200992
SOURCE: Merge Replication Provider
TEXT: The merge process could not store conflict information for article 'Products'. Check the publication properties to determine where conflicts records are stored. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
ERROR: 2812
SOURCE: OFF1SQLEXPRESS TEXT: Could not find stored procedure 'MSmerge_cft_sp_49D28CEAFF8A4ACC5C5462B5027D4E4D'.
ERROR: -2147199481
SOURCE: Merge Replication Provider
TEXT: 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.
After I refresh the snaphot at the publisher, these errors disappear and the syncronization is completed without any problem. And I can sync easy again week or two..
What is the problem? What is the right solution?
Yep, I can continue to refresh the snapshot each time when the error appears, but.. maybe there is another approach?
i think i have serious problem. i have almost 20,000 rows with the following conflict on a table (generated by merge replication over a period of around 3 weeks) :
'The row was updated at 'SubscriberServer3.DatabaseA' but could not be updated 'at 'PublicationServer1.DatabaseA'. Metadata mismatch'
i have 20 remote subscribers & 1 publisher. The above message originates from practically everywhere.
Besides dropping(not practical at the moment) & recreating replication to get rid of them, what else can i do to resolve this problem?? Do i have to resolve this problem row by row?? i'm at my wit's end. Please help!!
I have gone through BOL and various online resources including this one and I can't find any examples on how to use custom conflict resolvers to generate a result row which is a combination of the rows to be merged.
BOL says it can be done, but suitable examples are missing. I have found various posts requesting examples from up to a year ago, but can see no replies with relevant information
In particular I would like to see examples of
1) A stored procedure based custom conflict resolver
We have merge replication running with anamous subscribers We have generested lots of views tables and stored procedures like sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
Sorry for asking what may be a rookie question... We have configured transactional peer-to-peer replication in a testing environment with two servers, each publishing and subscribing to each other. If we write a piece of code that updates the same row on both servers with different values, we are not seeing data conflict issues. The data updated on server 1 propagates to server 2, but at the same time data updated on server 2 is updated on server 1. This leaves the records on each server out of data.
I expected my test case to produce errors, but instead I got inconsistent data. How do you turn on data consistency checking?
I'm having this problem for a while now and can't find any solid answer on the net for it.
I have 2 server in diffrent location ( +- 1200k's) away from eachother. When the msmerge agent run it return the following error and return the status as successfull with the following error information:
(The colum information specified for the MS SQL Server maximum conflict resolver) Resolver is either missing or incorrect. I've change every single article to "Latest chagne wins". Any suggestion. I happend when the power failed and the server got restarted.
here is the context of our problem: - Publisher : version SQL Server 2005 SP2, table in SQL Server 2000 (80) compatibility level, publication in SQL Server 2000 compatibility level - Distributor : SQL Server 2005 SP2 - Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem!
The synchonizations stop to work correctly. For all of them we've got the same error: quote:Replprov.dll , 2007/09/25 14:26:07.591, 4000, 16890, S1, ERROR: ErrNo = 0x80045017, ErrSrc = <null>, ErrType = 8, ErrStr = The schema script ' declare @cmd nvarchar(1000) set @cmd='exec sys.sp_MSchangearticleresolver @article_resolver=@ar, @resolver_clsid=@rc, @artid=@ai, @resolver_info=@ri' exec dbo.sp_executesql @cmd, N'@ar> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie..." mailto:N?@ar">N'@armailto:N'@ar">N'@ar</A< A>> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie...
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
I have created a custom resolver in VB .net (VS2005). The resolver works ( Both Publisher and Subscriber are SQL 2005 ), but I need to be able to debug the code within the resolver. How do I do this? I have tried a number of things without success.
version SQL Server 2005 SP2 table in SQL Server 2000 (80) compatibility level publication in SQL Server 2000 compatibility level - Distributor : SQL Server 2005 SP2 - Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem! The synchonizations stop to work correctly. For all of them we've got the same error:
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
Does anybody know if there are any conflicts using an XP computer as server and running SQL 7.0 on it?
I have a laptop using Windows XP, that I also have installed SQL 7.0 on (desktop version). I use it both as a client and as a server. As a client I am connecting to the NT server and it is working perfectly. When I try to run my SQL developed software against the local server though, I get a message saying "-2147217900 - Duplicate record or Record is in use. The request for procedure "TABLE NAME" failed because "TABLE NAME" is a table object"
why do i get collation conflict when i used temp table ??Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.i solved it by using COLLATE Latin1_General_CI_AS (the column name)will i have collation conflicts again when i put my web app on a web hosting company??
I have 2 tables Users and DVDTestResults these tables have a relation over UserID Users.UserID DVDTestResults.UserName
both char
But when I try to insert in DVDTestResults I am having an error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DVDTestResults_Users'. The conflict occurred in database 'Test', table 'Users', column 'UserID'. The statement has been terminated.
