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 ?
View 1 RepliesWe have merge replication running with anamous subscribers
We have generested lots of views tables and stored procedures like
sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
ctsv_07BA7383A12B4654B4D3A69B3053B227
aonflict_DH_tblReplicationRegion
How do we get rud of these I am concerned it will fill up the publisher database
Can any one advise
Hi all,
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.
Any Ideas?
Thanks in advance,
Michael
Hello everybody,
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.
Thanks in advance for any advice
hi all
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????
Thanks
Hi Everyone!
Here another post on replication.
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 this the good way of doing it?
Is there any other way?
Thanks !
SQL2K SP4
Windows 2000 server
Merge Replication
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.
exec sp_helpmergeconflictrows @conflict_table = 'CMCustomer'
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'd be thankful if you guide me.
Regards,
Hi..
I have configured publisher, distributer,subscriber... and tried to replicate it... it has done successfully...
When i try to add datas to the publisher ... it says
"conflict error"
can you please tell me how to check the replication is successfull or not.
and i want to add datas to publisher so that it should frequently updated in subscriber...
Hi
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?
Thanks in advance!
Paul
Hi all,
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!!
Thanx in advance.
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 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
2) A business logic handler written in VB .Net
Here's hoping
aero1
Hi all,
I have a query that I need to run where I join two tables that both
reside on different servers. I use an INNER JOIN statement to attempt
to join these tables, but for some reason I am getting the following
error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst
Hi
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?
Thanks in advance
Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot
the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.
this is incredibly cumbersome and time consuming and i know there must be a better way to do this.
i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?
Hi all
i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.
can anyone help me? :confused:
Thanx
M.J.Danesh
Hi,
How frequently will the history tables get deleted in Merge Replication and Transactional Replication with updatable subscriptions??
When transactional replication with updatable subscriptions is running in the continuous mode, will the history tables get deleted frequently??
Parameters :
Version : SQL Server 2005 SP1
Mode : Continuous running mode
Subscription type : Pull (for transactional)
Regards,
Swapna.B.
I have setup a transactional replication from oracle to MSSQL2005. One of the publication includes 26 articles but I found that there are only 24 articles replicated to the subscriber DB. I found that the repldata folder includes only files for 24 articles only. Besides, I double check the publication properties, totally, there 26 articles. Please advise. Thanks in advise.
View 2 Replies View RelatedHi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View Related
Hello,
How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.
Thanks,
Kanna.
Hi, eveyone..
I have a problem.
One of developer deleted Merge Replication Job.
However there is no doc for that replication.
What I want to know is like this:
which tables are in that replication?
Is there any system or user tables track down these replication as well as configuration in that replication?
Thanks in advance:-))
Jay
A set of tables are published for replication, when I have to unpublish the tables in order to update their structures (i.e. drop and recreate), an error comes up to say that the tables are still marked for replication. I check sysarticles and no tables are listed and even if I completely take the database off of replication, still there are problems. I have looked at the stored procedures that publish/unpublish the selected articles and see that a bit in sysobjects.category is set/unset by a constant of 32. But when unpublishing, it is like this does not work. I have to go into the sysobjects table renumber the category number and try again to drop/recreate and still the problem occurs, so I have to do this once more and then it will work. VERY FRUSTRATING and TIME CONSUMING. I find it hard to believe that this is how to maintain this. Does anyone have any ideas? I unpublish the articles through the replication interface, so it should work.
HELP PLEASE!!
We need to replicate data from a live database to a reporting database nightly.
There is data which is only needed for the reporting function - namely the selection criteria for reports which are to be run nightly or monthly.
Is it possible to have extra tables in the target database, holding the selection criteria?
If not, we'll have the reports running heterogeneous queries between the target database and a separate database with the selection criteria in it.
HiI tried posting this query in microsoft.public.sqlserver.programming but gotno response.I am new to replication but I am trying to setup up a transactionalreplication of tables from one database to another in MSSQL 2000 (SP2).My target tables have primary keys defined. Under publication properties Igo to the snapshot tab and for each table I clear the check box that says"Drop the existing table and re-create-it" and "clustered Indexes." On thispage the nothing is checked. for each table.Whenever the subscription is reinitialized it drops the primary keys on mytarget tablesand replaces them with a unique clustered index on the column that used tobethe Primary key.Is this normal? Is there anyway to stop it from doing this?I don't plan to send the snapshot more than once and let transactionalreplication take over for keeping my source and target in sync, but if Iever have to reinitialize the subscription, it would seem that I (orsomeone) willhave to take the a second step of manually dropping these clustered indexesand recreating the primary keys on the target table.Thanks in advance.---Dick Christoph---Dick ChristophJoin Bytes!612-724-9282
View 2 Replies View RelatedDoes anyone know if there's an SQL command i can run that will listthe tables in a database that have an identity column set to NOT FORREPLICATION?Many thanksDan Williams.
View 2 Replies View RelatedThere is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
publication 436456F0-F5AD-E411-80CF-5CF3FC1D2D76
subscriptionD824D120-23AD-E411-80E3-00155D0E1001
conflict tables 689C6A61-5359-4BB5-BECD-B03F5F94D79A
I am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.
For example:
Publisher database tables that are being replicated:
Table1
Table2
Table3
and I am going to create new tables in publisher database
Table1_new
Table2_new
Table3_new
Move data from
Table1--->Table1_new
Table2--->Table2_new
Table3--->Table3_new
Drop constraints from and then tables (does this require articles to be removed from replication?)
Table1
Table2
Table3
Rename
Table1_new to Table1
Table2_new to Table2
Table3_new to Table3
Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?
Hello all,
here is a stored procedure I have:
CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@GuichetId int,
@Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)
IF (dbo.GetSiGuichetEnRegle(@GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @GuichetID, @Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @GuichetID, @Validateur
SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO
The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.
For me, it shall also depend on
AnotherTable
spU_GUI_AppliquePerteFinancement
spU_GUI_AppliquePerteAgrement
FormateNoms
Apparently the dependencies are not calculated correctly because I'm using a temporary table.
My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.
Do you have any idea on how I can force the dependencies to be calculated correctly ?
Thanks
hi all,
Could we configure a merge replication such as replicate the master table and its all related tables (relation deep could be 1.)
We dont want to manually find master table relations and configure replication for the related tables.
please help for that configuration
kinds
Hi All
I am a newbie to replication. I just want to know Is there any way to publish tables (articles) without primary keys in transactional replication.
Early Thanks,
Salman Shehbaz.
Regarding Snapshot replication....
If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....
Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?