Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-13 : 10:16:02
|
| Larry writes "I'm not a SQL expert by any means and I have run into a situation that has me stumped. It is probably a common problem, but I could not find it on my own, so I am asking for help.I backed up my database on machine #1 and restored it to machine #2. After restoring the database, I setup a subscription to the data base which is being published on machine #1 and I replicated the data successfully. Subsequently, when I try to insert a record on the 2nd machine in a table that has records that were modified since the original backup date, I get an error message stating that a duplicate key exists and the insert fails.Doing a DBCC checkident on the table produced "Checking identity information: current identity value '1671', current column value '1699'.", an obvious mismatch.The identity value in question here is a primary key setup as "not for replication" with a seed value of 1 and increment of 1 (the default values).This is SQL 2000 with Service Pack 3a installed.My question is, Is there some simple way to reset the identity after replication completes so that machine 2 matches machine #1 and why doesn't the replication procedure do this automatically?I have tried doing a DBCC DBREINDEX (mytablename, '', 70) but that does not do the job and the checkident still returns mismatching values.I have read a number of posts here regarding alternate methods of managing identities, but my database is already in production and now I just need to replicate its data in the event the primary database server should fail. I need a solution that does not require a restructuring of my scripts, fields, etc.Thanks in advance...." |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-10 : 01:39:18
|
| If you're using machine2 for failover, use logshipping instead. Also if you have machine1 as publisher and machine2 as source, editing the data on the tables on machine2 will result in a 'duplicate value' error. |
 |
|
|
|
|
|
|
|
Duplicate key after replicating