Replic From Multiple Clients: Identifier Conflicts
Mar 29, 2007
I think I will have a problem with Id’s when I do merge replication. The problem is, I have several clients (local shops) who can do additions to a (Customer) table. Other tables will link to that table (things that a customer buys) with their foreign key, so the id of this table is very important. Now, twice a day that table with customers is merged from all clients to the central server. (the table with things that the customer buys, does not needs to be merged as it only remains local).
Possible problem:
if we assign ranges to identifiers for each client (thus local shop), which will be the best option we found until so far, how can we then detect the doubles? (with doubles, we mean that when the same customer is added in two local shops for a first time, then we will have the same customer twice in the central database)(afterwards, when we delete one customer row in the central database, we might have a id conflict when we merge back to the clients -as a customer might have already bought some goods at the two different shops meanwhile-)
What’s the common practice to resolve this?
(we need to do Merge Replication, because during the day, our local shops are disconnected from the central server)
I have a SQL Server 2005 Express database that was designed to be used by one client. What is the best way to change the design so it can contain multiple clients that can only see data entered by users of each client organization? Also I'm using the asp.net membership database to handle login and profiles. Can this be used with my multi client database?
Hi all,I've got a question concerning synchronization of multiple database clients.Consider a database, accessed by two clients. Is it guarantted, that ifclient 1 succefully commits a transaction, client 2 _instantly_ sees thechanges made by client 1?thx and regards,StephanP.S..: For MS Jet databases I know there _is_ a delay between writing datato the database and being able to read this updated value from anotherconnection,so the question refers to "real" DBMs (esp. SQL Server) only ;)
We have a situation where multiple-random client connections to SQL server get disconnected.
The workstations are not consistent, the time is not consistent, and the functions being run are not consistent. One thing that we can reproduce is that sometime, but not always, if a workstation runs a function that calls a specific stored procedure (SQL native client, ADO, SQL Server 2005 SP2), during the course of the parameter validation (after the .execute) SQL sends an ACK, RST followed by two RST TCP packet back to the client and disconnects the connection.
At the same time, several other connections across multiple computers are also dropped by SQL. However, some computers may have multiple connections to SQL and some, but not all of these will be dropped. Other computers will not have their connections dropped at all.
No SQL server errors are logged. Trace flags 4029 and 3689 have been set.
We have the network packet traces and the SQL profiler output to show this.
This is not reproducable at any other site.
Any ideas on what this could be would be greatly appreciated. -- Dana Comolli MS ISV Partner
I've been running SQL Server 2005 for over three months, and have got the database up to speed. Using SSMSE for an interface.
I've have been trying to get the Intranet to link to the database (ASP), but after a little research i found we needed to change the Authentication from 'Windows Authentication' to a mix of windows auth and sql login. Needing to restart the server for the changes to take effect.
After the restart things started to go pear shaped.
Before the restart we were able to run queries from different clients at the same time (4 workstations). Now we are unable to access anything on the database if a query is running on a seperate client. As in table properties, running a query. As i've said, we were able to do this perfectly fine before the restart.
We also use other programs to generate records for the database. These programs now have difficulty connecting to the database. We've gone through the wizards and as far as we are aware things should be working. We have created extra username/logins for these programs so not one computer/client/program uses the same connection login. However, they are unable to connect even after the wizard says there is no problem connecting to the server.
Is there any *restore to factory/default settings* button? We've even tried reverting to Windows Authentication to try and solve the problem, but it didn't work, we're pretty dependant on the database for day to day operations.
Hi Everyone,I have one database file which needs to be accesable to both a .net website and a vb.net application.The application collects data from the 'real world', processes it and adds the data to the database. The Website reads data from the database and displays the data in a graphical format.Now for the problem; While the Application is connected to the database (adding data), the Website is denied acces to the database. All connections to the DB are disposed when the Application is not uploading data, but the Website continues to be locked out (making it pretty useless) Closing the Application makes the Website work fine, but of course this means no 'real world' data can be updated.Can anyone suggest what is happening here, and more importantly how to fix it? A walkthrough must be available for this problem somewhere,... I just can't find it :(Thanks all,*The Stressed One*
I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row. Thanks.
I have a script that needs to be run for 50 different @ClientID. I dont want to run this script individually for each clientid. Would 'SET @clientID in (111, 222, 333) work? I've been told that it wouldn't. Short version of the script is.....
We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom.
What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance.
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
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.
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
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?
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?
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.
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'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.
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.
I'm trying to get the last ten unique clients viewed by each user. I have tried using the TOP (N) but it does not show the last ten just any ten. When a user views a client record, a record is saved to the tblUserRecentViewedClients table and includes logID (key), client_id (int), username (nvarchar), lastviewed (datetime). Here is what I have so far. Can anyone offer any suggestions?SELECT TOP (10) tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName, tblClient.CompanyName FROM tblUserRecentViewedClients INNER JOIN tblClient ON tblUserRecentViewedClients.client_ID = tblClient.client_ID WHERE tblUserRecentViewedClients.UserName=@UserName GROUP BY tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName, tblClient.CompanyName
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.
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
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?
I have an Access XP ADE application connected to a SQL Server 7.0 SP4database. I have created a timestamp column in the main table.Unfortunately, I am now getting persistent write conflict errors.The order of operations are:1. The application starts and loads the recordset into the form using astored procedure.2. I modify a field and press a save button which uses me.dirty=false toforce a save.3. The field is saved to the database. Using profiler I can observe themodified field being saved. As I would expect, the update statement isusing the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.4. Without navigating off the record, I alter the same field (or adifferent field) and press save again and a write conflict will appear.Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.I thought that the timestamp column value is incremented each time therecord is updated. The ADE application does not appear to be recognizingthe new timestamp value.Any help or advice you could give would be appreciated.ThanksGeorge
Hello all... I'm stuck, I cannot figure out how I should go about flaggingconflicts on a sheduling app. I currently have 8 columns (school grades)that have class over the course of 9 periods. I am populating the asp pagefine, and making changes to the database with forms lists. I need to compareall the results of one period (thats 8 results) so that i may find aclassroom conflict. Is there any solution in SQL?This is my query:sql = "SELECT * FROM schedule WHERE period ='"&num&"'"I step through this 9 times in a for/next loopThanks in advance!Alpay Eno
I am busy extending a VB6 app to talk to SQL Server Express 2005 and have come across a naming conflict. Some of the columns in the application's Access 97 tables is "index" which obviously exists as a T-SQL keyword and therefore any queries I perform including this column throws a syntax error. I tried prefixing the columns with their table names as in TableName.Index, but this still throws up the syntax error which I thought was a bit odd. I preferably want a fix that will be Access as well as T-SQL compliant but if that's not possible I will just write a string converter that does the job based on anyone's suggestions. TIA
i have both server instances installed on same machine, 1st is sql 2k enterprise and 2nd is sqlExpress 2005.
all was working fine till today, i renamed a catalog in the express2005 instance. the reason for this renaming was a duplicate catalog name in the 2k and in the 2005 so when i backup my dbs one is overwriting the other because of the same file name.
after this rename i started to get some errors in the application so i decided to rename it back to its original name and relocate the backup destination to other folder.
now the situation in that i cant login any more to the 2005 server, when i login to servsqlexpress2005 (via manager or via odbc) i see always the 2k server. i tried to login to any server name like .sqlexpress2005 or whatever i always get to see the 2k catalogs only.
in proccess list i see both sqlserv.exe proccesses, also in services all is working fine, i only cant access to this server in any way.
PLEASE HELP!!! should i reinstall 2005 instance? i dont wanna do that i have so much configs on it include very massive reporting services
***the more that i try to connect my 2k5 server the problem looks wierd more and more. when i connected as localhostsqlexpress2005 i succeeded... but in 2-3 minutes connection didnt turn off, but it switched to the 2k server... it looks like a major bug!!! how can connection switch himself between 2 server without even notice??
exec ('CREATE SYNONYM tblsynonym FOR ' + @cx + '..TableName')
---Procedure
drop synonym tblsynonym
The application i'm working on uses stored procedures that will at some point be called by more than one user at a time. At the start of the stored procedure the synonym is created and then it is dropped when the procedure completes, the issue is this: if two users access the same stored procedure at the same time then the first procedure will create the synonym and the second will fail because the syonym already exists.
There's really strange issue. I have the table 'Resellers' with 'ResellerID' as identity column. As a rule, the records are inserted into this table at publisher. Usually, the subscribers just update this table.
After I added several new columns to the table, the strange conflicts had appeared at the subscriber side:
The merge process is retrying a failed operation made to article 'Resellers' - Reason: 'Violation of PRIMARY KEY constraint 'PK_Resellers'. Cannot insert duplicate key in object 'dbo.Resellers'.'.
If I look into MSmerge_conflict_Core_Resellers conflicts table at the subscriber, I see that 'ResellerID' column contains value '1'. Of course, the record with such 'ResellerID' already exists. But at the publisher the record with the given rowguid has quite other value!
Why?! What is the reason of such behaviour?
I did re-init of the subscriber - no result, rather, all records which had conflicts appeared in the table, but the recent created records has conflicts again.
- Transactional (5 articles - tables only) Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums. As an example: - Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated. Thanks.
