Problems with schema changes through schema comparison sdk
bkerr
Posts: 2
When I run SQL compare manually, it moves the database tables over correctly to the destination database. However when I use the SQL comparison SDK, I have a number of database tables that still have differences. All the differences are on the alter table statements and still have the "no check" setting on.
Source:
ALTER TABLE [dbo].[ALM_OBJ58] ADD CONSTRAINT [FKALM_OBJ58_ALM_OBJ58_Idn] FOREIGN KEY ([ALM_OBJ58_Idn]) REFERENCES [dbo].[ALM_RESOURCE_BASE] ([ALM_RESOURCE_BASE_Idn])
Destination:
ALTER TABLE [dbo].[ALM_OBJ58] WITH NOCHECK ADD CONSTRAINT [FKALM_OBJ58_ALM_OBJ58_Idn] FOREIGN KEY ([ALM_OBJ58_Idn]) REFERENCES [dbo].[ALM_RESOURCE_BASE] ([ALM_RESOURCE_BASE_Idn])
I am using Options.Default when calling CompareWith and BuildFromDifferences. I can ignore it by setting IgnoreWithNoCheck, but I would rather have the tables match exactly. How do I fix this?
Source:
ALTER TABLE [dbo].[ALM_OBJ58] ADD CONSTRAINT [FKALM_OBJ58_ALM_OBJ58_Idn] FOREIGN KEY ([ALM_OBJ58_Idn]) REFERENCES [dbo].[ALM_RESOURCE_BASE] ([ALM_RESOURCE_BASE_Idn])
Destination:
ALTER TABLE [dbo].[ALM_OBJ58] WITH NOCHECK ADD CONSTRAINT [FKALM_OBJ58_ALM_OBJ58_Idn] FOREIGN KEY ([ALM_OBJ58_Idn]) REFERENCES [dbo].[ALM_RESOURCE_BASE] ([ALM_RESOURCE_BASE_Idn])
I am using Options.Default when calling CompareWith and BuildFromDifferences. I can ignore it by setting IgnoreWithNoCheck, but I would rather have the tables match exactly. How do I fix this?
Comments
I think this issue might be being caused by using SQL Data Compare after you have used SQL Compare.
When SQL Data Compare generates the script, by default it will add the constraint with WITH NOCHECK, to basically speed up the sync' as we can assume that the data is valid. This results in a schema difference that SQL Compare picks up after the SQL Data Compare sync'.
There are two ways round this. The first is to use the SQL Compare option 'ignore WITH NOCHECK' which you know about. The second is to use the SQL Data Compare option to 'Force constraints to be re-enabled with CHECK'.
I hope this is helpful.