Conflicts In Updateable Subscriptions
Sep 11, 2007
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
View 3 Replies
ADVERTISEMENT
Oct 28, 2006
Replication Type: Transactional With Updateable Subscriptions
SQL Server Version: SQL Server 2005 Enterprise Edition
Publisher, Subscriber1 and Subscriber2 all on same SQL Server
Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?
Example:
Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3).
Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1).
Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100.
Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.
I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.
Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?
View 3 Replies
View Related
May 16, 2007
Greetings,
We periodically get the following message in our three server peer-to-peer transactional replication environment:
"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?
Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.
Thanks,
BCB
View 9 Replies
View Related
Jan 18, 2007
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
View 1 Replies
View Related
May 28, 2002
Hello-
I have a view that used to be updateable (except for a few fields, but that is fine). When I use the results of a CASE statement as one of the field values, the entire view is not updateable. The view is pasted below. Does anyone know how to keep my view updateable and, somehow, add this new field? Thank you.
-Eric
SELECT
DATEPART(yy, Orders.NextShipDate) AS year,
DATEPART(mm, Orders.NextShipDate) AS month,
DATEPART(dd, Orders.NextShipDate) AS day,
People.LastName,
People.FirstName,
Customers.CustomerID,
Orders.NextShipDate,
Customers.Custom,
Orders.AllocationTemp,
Orders.ShippingTemp,
Orders.HorseName,
Orders.PriorShipDate,
Customers.Username,
Customers.Password,
Orders.OrderID,
Orders.Deleted,
Orders.Deadbeat
FROM
People
INNER JOIN Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ON People.PeopleID = Customers.PeopleID
WHERE
(CONVERT(DATETIME, CONVERT(CHAR(10), Orders.NextShipDate, 101)) <= DATEADD(d, 7, CURRENT_TIMESTAMP)) AND (Orders.Deleted = 0) AND
(Orders.Deadbeat = 0) AND
(Customers.Deleted = 0) AND
(Customers.Pending = 0) AND
(Orders.OrderID NOT IN
(SELECT Orders.OrderID
FROM Items INNER JOIN Products ON Items.ProductID = Products.ProductID INNER JOIN Orders ON Items.OrderID = Orders.OrderID
WHERE
(Items.Dose = 0) AND
(Products.IsPharmaceutical = 1) AND
(CONVERT(DATETIME, CONVERT(CHAR(10), Items.Created, 101)) > DATEADD(d, - 5, CURRENT_TIMESTAMP)) AND
(Items.Deleted = 0))) AND
EXISTS (SELECT ItemID FROM Items WHERE Items.OrderID = Orders.OrderID AND Deleted = 0)
View 1 Replies
View Related
Oct 11, 2007
I could have swore I posted this earlier today, but it must not have went through...anyway here goes again.
Is there any way to tell if a view is updateable (updates the underlying table(s))?
Thanks,
Van
View 3 Replies
View Related
Mar 24, 2008
I have a view that combines several tables, and lastly one column that is determined by a subquery.
i.e.
CREATE VIEW [View]
AS
SELECT
[TableA].*,
[TableB].*,
(SELECT TOP 1 [Column] FROM [TableC] ORDER BY [Column2])
AS [SpecialColumn]
FROM [TableA] INNER JOIN [TableB]
ON [TableA].[ColumnID] = [TableB].[ColumnID]
I have made INSTEAD OF UPDATE and INSTEAD OF INSERT triggers for this view that will then allow you to modify [View], and it will handle everything, adding/modifying the data in the three [TableX] tables accordingly.
In SQL Management Studio, the following code works fine, and is what is expected...
UPDATE [View] SET [SpecialColumn] = 1 WHERE [ColumnID] = @DesiredID
This will call my instead of trigger and update my special column accordingly exactly how I want it to.
Now, the problem lies in the application layer running on top of the database and how it accesses the view. Any call from the application to update [SpecialColumn] returns an OleDB error stating that [SpecialColumn] can not be modified. I assume that sql server is identifying the column as some sort of a derived column that normally wouldn't be able to be updated, and OleDB is restricting the column to just read only. Is there some way to override that in my view to say that the column is not read-only? Is this an OleDB restriction that can't be handled in the database/view itself?
I've also tried creating a stored procedure that called the exact same
UPDATE [View]
SET [SpecialColumn] = @param2
WHERE [ColumnID] = @param1
and it also works within management studio, but still gives a similar OleDB error about how the stored procedure attempts to update a derived column that can not be modified.
I have a solution where my stored procedure calls the exact same code as the instead of triggers to update the underlying tables of the view manually, however, I'm not asking for a solution like this to my problem, because this works fine, I simply am not happy having to do band-aid workarounds and am wondering if this is a common problem in front-ends and whether it can be handled on the database layer.
View 1 Replies
View Related
Aug 14, 2005
I am hoping to create an updatable view from two tables joined on twofields. I have two questions;1. How can I enforce a one to one relation in sql server 2005 based on twofields? Do I need to create one explicitly under relations? Do I cerate anindex on both fields? Is there a step by step process I can look at tocreate one-one relations?2. Is a view updateable if there is a where clause on one of the fields inthe join/relation?Thank again.Regards
View 1 Replies
View Related
Mar 23, 2007
How do I make a view non updateable? I want to create a view so that Iwill not be able to update, insert or delete the view so that basetable is not affected (except dropping the view itself). Thanks.
View 4 Replies
View Related
Mar 26, 2000
I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net
View 2 Replies
View Related
Mar 6, 2007
Will Sql Express handle being a subscriber for an updateable transactional publication from Sql Server 2005?
View 6 Replies
View Related
Feb 14, 2003
Does anyone have advice as choosing between merge replication and updatable subscriptions? I've read the documentaion, they seem to offer the same functionality, and I don't see any clear guidelines as to why to pick one over the other...any suggestions would be appreciated!
Ed
View 1 Replies
View Related
Jul 11, 2006
Hello,
We have two updateable subscribers and a publisher using transactional replication. The distributor is located on publisher. Currenetly all 3 sites are SQL 2000 SP3. We plan to upgrade all sites to sp4 with minimum downtime (if possible zero). Is it possible to upgrade publisher/distributor first and subscribers after few days?
Thanks,
np70
View 1 Replies
View Related
Mar 8, 2007
We have a database set up for transactional replication with an updateable subscription. When we add log shipping to the publication database (sending the logs to a separate server) the publication and subscription entry show up in Management Studio's replication folder on the log ship target server (although the definitions are correct).
Is this configuration legitimate? Can I add log shipping to the subscription database as well?
Thanks... Liston
View 4 Replies
View Related
Mar 5, 2007
I am using RMO to synchronize an updateable transactional pull subscription from Sql Express SP2 to Sql Server 2005 64-bit Standard Edition. The data propogates from the server to the client, but changed data on the client does not replicate to the server. Any ideas?
Thanks!
View 3 Replies
View Related
Jun 13, 2006
When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record! If I remove the Allow Nulls field I can then edit it! What am I missing here?
View 1 Replies
View Related
Jul 29, 2004
Hi All,
How can I link a view into MS Access from MS Sql Server, that I can update as a tabble.
Thanks
Laszlo
View 3 Replies
View Related
Mar 12, 2008
I have used the Access 2003 Upsize Wizard to create SQLServer Tables, all worked fine except I cannot Update the tables from Access.
The idea being to use Access Forms as the front end to an SQLserver back end as the Access database is quite complex and filled with macros and queries, not one of mine.
I can update the table in SQLServer but the connected tables at the Access end are read only. Why?
Kind regards,
Johnv
View 1 Replies
View Related
Jul 5, 2007
Hi,
is there any update on the subject?
Jet has it for ages.
I want to use Scrollable/Updateable Cursors & OLEDB Rowsets opened via Select query.
Last time I have heard that MSFT is planning to add this in the future.
Is there a beta version that I can try?
Thanks,
Konstantin
View 8 Replies
View Related
Apr 21, 2015
We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.
We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.
Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.
We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.
It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.
I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)
So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?
View 9 Replies
View Related
Mar 28, 2007
What are conflicts from MERGE replication point of view?
------------------------
I think, therefore I am - Rene Descartes
View 1 Replies
View Related
Jul 10, 2006
I have two tables that are getting conflicts between the subscriber and the publisher, however I am pretty sure we only update these tables at the subscriber.
I have column level tracking turned on.
The tables both have Nonoverlapping, single subscription (3) set for partition options. I wonder does this do any maintenance to the tables?
I have put in place some triggers to audit what makes changes to the data, but won't know until tomorrow, so if there is something that anyone knows about that might help, please let me know.
Thanks
View 6 Replies
View Related
Oct 1, 2007
I have a database on SQL Server 2005 and am using Access 2003 for my front-end. Today, I started having problems with ONE of the tables. I can't make changes - I get the "another user has made changes" and do not have the Save Changes option - only the Copy to Clipboard or Drop Changes options. I can make changes directly to the table using the Management Studio.
I don't know if it's relative, but it happened as I was trying to add an "After Update" event to set a field equal to currentuser().
Do you have any idea what may be happening?
Thanks
View 7 Replies
View Related
Jun 14, 2006
Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs?
Thanks,
Derek
View 4 Replies
View Related
Feb 19, 2007
Can someone answer/point me to an article to handle update conflicts when replicating?
Say for example, you pull down data into your sql everywhere database and start making changes. Before you replicate, changes are made in the master database that conlift with changes you've made. For example, when you pull down your data there was a product with 4 units left. So maybe you place an order for that product for 4 units while offline. However, while you were offline someone else has taken all 4 of those units and when you replicate, you need to throw an error. How do you handle this situation?
View 1 Replies
View Related
Mar 6, 2004
Hi, I need to set an alert when a replication-conflict occurs rather that to check for conflicts manually. How can i accomplish this? I couldn't find the particular error-message to trap in an alert.
Regards!!
View 1 Replies
View Related
Mar 25, 2008
I have set up merge replication between 1 publisher and 9 subscribers (all push subscriptions). Distributor and publisher are located on the same machine.
Although everything seems to work fine from the outside (most of the time), there are a lot of conflicts in the conflict table for the replication. They appear all the time. There are a lot of "download insert failed" conflicts. They always look like in the following three screenshots:
http://www.tronk.be/conflicts/conflict1.JPG
http://www.tronk.be/conflicts/conflict2.JPG
http://www.tronk.be/conflicts/conflict3.JPG
In the same way, there are also many "upload insert failed" conflicts.
In addition to this, there are some "update conflict"s (but a lot less than the other conflicts). Some of them show the same row at both sides:
http://www.tronk.be/conflicts/conflict4.JPG
Others show a different row at the both sides:
http://www.tronk.be/conflicts/conflict5.JPG
The only thing that causes a real problem is the last screenshot, although I don't understand why the other conflicts are there (the inserst statements actually seem to happen anyway, even though there are conflicts). In case of the last screenshot, I can't find any place where an UPDATE actually happens at APP-STB, while I can clearly pinpoint the UPDATE at the other side (which is what actually comes from our program).
One more thing, the system is running on its limits, but all replication seems to be working fine.
I would appriacte any help or comments very much.
View 1 Replies
View Related
Apr 18, 2007
Hi,
I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. Is there a way to give an order to the way the tables are replicated. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.
View 6 Replies
View Related
Dec 3, 2006
Hi everyone, I'm creating a ASP.NET 2.0 web application utilizing sql server 2000 as a database. My problem revolves around multiuser acces with long running processes. Some of the pages in the application have long running processes against large tables in the database, lets say that take 2minutes to complete. My problem is how do I utilize ado properly in the rest of my application to display a message to users who may try to access data associated with a table while in the midst of one of its long running processes? For instance I would like to notify the user that "Table X is currently locked, please try again in a few minutes".Do I catch sqlException and examine the .Number property? Any insight is appreciated.
Thanks.
View 2 Replies
View Related
Dec 30, 1998
I have heard that there are potential problems if you put service pack for NT on your server, in that it conflicts with SQL 6.5. Does anyone know of
any such problems with either running 6.5 under these conditions or installing 6.5 if the service pack is already on the server.
Thanks
View 2 Replies
View Related
Apr 2, 2008
Hi:
I have maintenance plan on DBABC backup log to .trn job to run every 90 minutes (daily).
in order to keep the log file small, I also set up a job (T-SQL) to run at 4:15 am to backup log ABC with truncate_only, then run dbcc shrinkdatabase (DBABC, 10)
it looks "backup log ABC with truncate_only" has conflicts with the every90 minutes backup transaction log.
Question: could I keep the backup transaction log every90 minutes, but still could shrink the log file. The log file is growing very fast.
Or I have to use differential backup instead of backup tran log?
thanks
David
View 4 Replies
View Related
Aug 8, 2007
The server in question already has Ms-SQL installed and used. My web-app has been developed for MySQL.
What, if any, conflicts would I encounter by installing MySQL on the server as well?
Would I be better of changing the the web-app to work with MS-SQL?
View 1 Replies
View Related
