wrong RESEED
cavale
Posts: 6
Generated SQL script:
as you can see the script deletes the rows where the id=1043, 1044 and 1045
so it should reseed on 1042 which it does not..
instead it reseeds on 1035 and resulting in a primary key error in my app.
why does it reseed wrong?
this is very annoying..
edit:
im working with SQL Data Compare 6 version 6.1.1.308
and i never encounterd this bug in version 6.0
SET NUMERIC_ROUNDABORT OFF GO SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON GO -- Pointer used for text / image updates. This might not be needed, but is declared here just in case DECLARE @pv binary(16) BEGIN TRANSACTION -- Delete 3 rows from [dbo].[tblManagerAssemblies] DELETE FROM [dbo].[tblManagerAssemblies] WHERE [manasm_id]=1043 DELETE FROM [dbo].[tblManagerAssemblies] WHERE [manasm_id]=1044 DELETE FROM [dbo].[tblManagerAssemblies] WHERE [manasm_id]=1045 -- Update 7 rows in [dbo].[tblManagerAssemblies] UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6751, [manasm_project]=154, [manasm_block]=11098, [manasm_assembly]=1, [manasm_b]=790, [manasm_lm_ruw]=119.346, [manasm_lm_af]=119.346, [manasm_m2_ruw]=21.586548, [manasm_m2_af]=21.586548, [manasm_m3_ruw]=0.012352305, [manasm_m3_af]=0.012352305, [manasm_kg_ruw]=96.96559425, [manasm_kg_af]=96.96559425, [manasm_material]=17, [manasm_slot]=32, [manasm_aantal6]=6 WHERE [manasm_id]=1036 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6752, [manasm_project]=154, [manasm_block]=11099, [manasm_assembly]=1, [manasm_lm_ruw]=872.931, [manasm_lm_af]=872.931, [manasm_m2_ruw]=173.797278, [manasm_m2_af]=173.797278, [manasm_m3_ruw]=0.0967197175, [manasm_m3_af]=0.0967197175, [manasm_kg_ruw]=759.249782375, [manasm_kg_af]=759.249782375, [manasm_material]=17, [manasm_slot]=32, [manasm_aantal4]=41, [manasm_aantal6]=41 WHERE [manasm_id]=1037 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6753, [manasm_project]=154, [manasm_block]=11100, [manasm_assembly]=2, [manasm_b]=790, [manasm_lm_ruw]=517.166, [manasm_lm_af]=517.166, [manasm_m2_ruw]=93.541708, [manasm_m2_af]=93.541708, [manasm_m3_ruw]=0.053526655, [manasm_m3_af]=0.053526655, [manasm_kg_ruw]=420.18424175, [manasm_kg_af]=420.18424175, [manasm_material]=17, [manasm_slot]=32, [manasm_aantal4]=26, [manasm_aantal6]=26 WHERE [manasm_id]=1038 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6754, [manasm_project]=154, [manasm_block]=11101, [manasm_assembly]=2, [manasm_lm_ruw]=766.476, [manasm_lm_af]=766.476, [manasm_m2_ruw]=152.602488, [manasm_m2_af]=152.602488, [manasm_m3_ruw]=0.08492463, [manasm_m3_af]=0.08492463, [manasm_kg_ruw]=666.6583455, [manasm_kg_af]=666.6583455, [manasm_material]=17, [manasm_slot]=32, [manasm_aantal4]=36, [manasm_aantal6]=36 WHERE [manasm_id]=1039 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6755, [manasm_project]=154, [manasm_block]=11097, [manasm_b]=2570, [manasm_lm_ruw]=67.26, [manasm_lm_af]=67.26, [manasm_m2_ruw]=30.48732, [manasm_m2_af]=29.85576, [manasm_m3_ruw]=0.030716334, [manasm_m3_af]=0.024211374, [manasm_kg_ruw]=166.9632219, [manasm_kg_af]=149.3998299, [manasm_material]=17, [manasm_slot]=32, [manasm_aantal4]=3, [manasm_aantal6]=3 WHERE [manasm_id]=1040 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6756, [manasm_project]=154, [manasm_block]=11097, [manasm_assembly]=10, [manasm_b]=134, [manasm_h]=290, [manasm_lm_ruw]=1.398, [manasm_lm_af]=1.398, [manasm_m2_ruw]=0.201732, [manasm_m2_af]=0.201732, [manasm_m3_ruw]=0.00050433, [manasm_m3_af]=0.00050433, [manasm_kg_ruw]=3.9589905, [manasm_kg_af]=3.9589905, [manasm_material]=NULL, [manasm_slot]=NULL, [manasm_aantal4]=3, [manasm_aantal6]=3 WHERE [manasm_id]=1041 UPDATE [dbo].[tblManagerAssemblies] SET [manasm_parasm_id]=6757, [manasm_project]=154, [manasm_block]=11097, [manasm_assembly]=11, [manasm_lm_ruw]=1.608, [manasm_lm_af]=1.608, [manasm_m2_ruw]=0.2081796, [manasm_m2_af]=0.208032, [manasm_m3_ruw]=0.0005817012, [manasm_m3_af]=0.00057993, [manasm_kg_ruw]=4.16789274, [manasm_kg_af]=4.1631105, [manasm_aantal4]=3, [manasm_aantal6]=3 WHERE [manasm_id]=1042 COMMIT TRANSACTION GO -- Reseed identity on [dbo].[tblManagerAssemblies] DBCC CHECKIDENT('[dbo].[tblManagerAssemblies]', RESEED, 1035) GO
as you can see the script deletes the rows where the id=1043, 1044 and 1045
so it should reseed on 1042 which it does not..
instead it reseeds on 1035 and resulting in a primary key error in my app.
why does it reseed wrong?
this is very annoying..
edit:
im working with SQL Data Compare 6 version 6.1.1.308
and i never encounterd this bug in version 6.0
Comments
The SEED is simply copied from one database to another so if there have been a large quantity of inserts on the target as opposed to the source and not all of the data is removed in the target when attempting to move the SEED across it puts it at a value where there is still data.
If you are simply doing a compare and synch straight off I'm not sure why this would be causing an issue. Try refreshing the schema and the the data comparison straight before the synch.
There may also be an issue if you're changing direction of the synchronization as there seems to be a bug in our system about that although I'm not sure if it's still valid.
Hope this gives you some ideas.
Project Manager
Red Gate Software Ltd
updating the schema seems to resolve the issue
still wondering why it didn't happen with the previous version of SQL Data Compare..
This is a slight annoyance in SQL Data Compare so we've got an issue in our bug tracking system to address this at some point.
Glad it's working for you now.
Project Manager
Red Gate Software Ltd
If I understand the problem correctly it gets the values for the reseed when it evaluates the schema. The problem we are hitting on many of our compare runs is the schema is checked at the beginning of the process, then it takes 5-10 min for the tables to compare before the synchronization script is ready. But during that 5-10 min a new row is added to our tables. The inserts are added to the script, but the reseed value is now incorrect. So the destination database now gets an error if a row is inserted into that table.
We are just finishing our evaluation of your software and this seems like it could become a bit of an issue for us if a fix is not going to be available in the near future.
Thanks!
- Sean
Use Edit project-> Options -> Reseed identity columns
If your databases seem to follow the above usage pattern, then you can even save this option as "My Defaults" so that new projects will not have the "Reseed identity columns" set.
Kind regards,
Andras
Red Gate Software Ltd.