Options

wrong RESEED

cavalecavale Posts: 6
edited June 20, 2008 6:54AM in SQL Data Compare Previous Versions
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
· Share on Twitter

Comments

  • Options
    cavalecavale Posts: 6
    double post..
    · Share on Twitter
  • Options
    richardjmrichardjm Posts: 598
    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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
    · Share on Twitter
  • Options
    cavalecavale Posts: 6
    tnx!

    updating the schema seems to resolve the issue

    still wondering why it didn't happen with the previous version of SQL Data Compare..
    · Share on Twitter
  • Options
    richardjmrichardjm Posts: 598
    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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
    · Share on Twitter
  • Options
    seanfredrickseanfredrick Posts: 1
    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
    · Share on Twitter
  • Options
    Andras BAndras B Posts: 249
    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
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
    · Share on Twitter
Sign In or Register to comment.