wrong RESEED

cavale

Generated SQL script:

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

cavale

double post..

richardjm

If you refresh the comparison at any point the SEED information isn't included in the data as it's actually part of the SCHEMA of the table. You can force this to happen by refreshing the schema on the tables & views tab of the project configuration.

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.

cavale

tnx!

updating the schema seems to resolve the issue

still wondering why it didn't happen with the previous version of SQL Data Compare..

richardjm

It would have done - although you may have been lucky enough that the reseed value was still valid.

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.

seanfredrick

Is there any time frame for when this issue will be fixed?

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

Andras B

If your data is being changed between the start of the comparison and the synchronization, then reseeding the identity value does not make much sense, exactly because of the conflicts you are experiencing. You can control whether identity columns should be reseeded via project options.

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