Ignore identity seed doesn't work with other changes

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Ignore identity seed doesn't work with other changes

Postby dsp » Thu Mar 14, 2013 10:40 pm

I found out that renaming a column with an identity seed caused the identity to change.

db1: orgName int identity(500,1)
db2: orgName int identity(3000,1)

rename at db1: newName int identity(500,1)
After sql compare the column at db2 should be newName int Identity(3000,1), but it turned 500,1.

Danny Springer
dsp
 
Posts: 17
Joined: Sat Jan 30, 2010 7:46 pm

Postby Anu Deshpande » Mon Mar 18, 2013 11:28 am

Many thanks for your post and apologies for inconvenience caused.

We have logged a support call and would like to investigate this issue further.

I will email you with few more questions.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby Anu Deshpande » Tue Mar 19, 2013 11:06 am

As per our live chat it is SQL Compare's feature that allows to ignore the identity seed and increment value during comparison but SQL Compare will not ignore the settings during deployment.

Hope this answers your question.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby thejamie » Tue Jan 28, 2014 4:52 pm

It looks like the seed value depends on the presence of a primary key or not. If for some reason, the Identity key is not a primary key, the process that sets the DBCC CheckIdent (tablename,RESEED,0) does not get processed properly when you are not running the wizard in the table by table section but attempt to run it from the first tab ("Data Sources"). Try running from the tab that says "Tables and Views" and mark the column as an identity column. Otherwise, when not a primary key, the program does not seem to recognize that there is an identity column in the field. Or perhaps the Ignore Identity property is the default but it is over-ridden in the Tables and Views tab section. I can't quite figure out which but I do know that if I run from the Tables and Views, it seems to reset the identity if I check the box on the table that has an identity set. Seems to me to be counter-intuitive. I would think that if the table has an identity set, it should automatically reset that value.
thejamie
 
Posts: 3
Joined: Fri Oct 28, 2011 7:59 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests