IDENTITY field corrupts after INSERT

laurieb

Probably a SQL Server 2005 bug rather than Redgate, but after running a simple insert query generated by Data Compare on a table with an identity field, no new records can be inserted because the IDENTITY value is not being created. Removing and re-applying the IDENTITY property on the primary key solves the problem.

Has anyone else come across this? I can forward the script if necessary

Chris Auckland

Thanks for your post.

What project options did you use for the sync? Are you using the 'Include Identity Columns' option?

Also, are the table structures identical? You may want to use SQL Compare to check this?'

laurieb

Amongst others I selected Include Identity Columns and Reseed Identity column. The script ran this as the last statement.
-- Reseed identity on [dbo].[tblEvent]
DBCC CHECKIDENT('[dbo].[tblEvent]', RESEED, 1014049)
GO
-- Reseed identity on [dbo].[tblContact]
DBCC CHECKIDENT('[dbo].[tblContact]', RESEED, 173013)
GO

I suspect the reseed values were wrong for some reason. I don't run this script very often. When I run it again I will check the identity reseed values before I run the script

Chris Auckland

Thanks for your reply.

The seed values are calculated when interrogating the schema during the comparison phase, so if the seed values change between running the compare and generating/running the script, then they could be incorrect.

The best way to get round this is to refresh the schema information in the Tables & Views tab, before you generate the script.

laurieb

Thanks Chris. Please close the call. There may have been a delay bewteen generating and executing the script. I will check details when I run this script next