After SQL Data Compare, SQL Compare shows WITH NOCHECK dif

jdraggi

Should I be concerned that after running a SQL Data Compare version 6 or 7 that SQL Compare 6 shows that the database that was data compare synchronized now has a "WITH NOCHECK" difference on all of those tables?

I would like to maintain the accuracy of the database structure. Is there a check-box that I need to 'check' or a script that I should run afterwards?

Thanks,
John

Brian Donahue

Hi John,

If you synchronize data using SQL Data Compare with the disable foreign keys option selected, the keys may be dropped during synchronization and then put back in place without checking the data, the assumption being that the data that you had migrated is already consistent.

This does have the effect of causing a difference when you check the table's schema afterwards using SQL Compare. The workaround is to use the ignore WITH NOCHECK in the comparison project options of SQL Compare, since there is nothing you can do in Data Compare except to leave your FKs intact during data synchronization, which may cause the synchronization to fail.

Michelle T

You can revalidate your constraints (foreign keys, check constraints etc) by running the following statement for each constraint:

ALTER TABLE <table name> WITH CHECK CHECK CONSTRAINT <constraint name>
GO

This will check the integrity of your table with regards to that constraint and either give you an error if there is a problem or remove the 'WITH NOCHECK' if there is no problem.

If you have a lot of data this may take a very long time, which is why we don't automatically do it after Data Compare is finished.

jdraggi

Thank you both for your input.

Brian Donahue, I have located the "disable foreign keys" option and unchecked it. Thank you. Will this cause any foreseeable problems besides the sync failure? Does this mean that I will have to manually select the tables to compare? IE select the dependent tables first, then go back and select the rest of the tables that depended on the first set of tables... or will Data Compare be smart enough to handle that part?

Michelle Taylor, Thank you for this manual fix. There is a really large number of tables and it would be very time consuming to setup. I'm assuming that this is what SQL Compare 6 is doing. Until now, what I've done is created an up to date backup of the existing database that I'm going to data compare into, then perform the data compare against the remote database, then perform a SQL compare between the backup and the original -- fixing the changes made to original.

--John

tchaplin@miningis.com.au

I use this script to fix all the NO CHECKS in the database.

I'm about to post a related question called "Disabling foreign keys" for those interested.

BEGIN TRY
	BEGIN TRANSACTION
	DECLARE @FK_NAME AS NVARCHAR(MAX)
	DECLARE @TABLE_NAME AS NVARCHAR(MAX)
	DECLARE @SQL AS NVARCHAR(MAX)

	DECLARE CFks CURSOR FOR
		SELECT 
			[name] fk_name,
			'mmrs.' + OBJECT_NAME([parent_object_id]) table_name
		FROM  
			sys.foreign_keys fk
		WHERE 
			is_not_trusted=1 
			
	OPEN CFks

	FETCH NEXT FROM CFks
	INTO @FK_NAME, @TABLE_NAME
	
	WHILE @@FETCH_STATUS = 0
	BEGIN

		PRINT 'FK: ' + @FK_NAME + ' TABLE: ' + @TABLE_NAME
		
		SET @SQL = '	
			ALTER TABLE ' + @TABLE_NAME + '
				  WITH CHECK
				  CHECK CONSTRAINT ' + @FK_NAME + '
		'
		PRINT @SQL
		EXEC sp_executesql @SQL
		FETCH NEXT FROM CFks
		INTO @FK_NAME, @TABLE_NAME
		
	END
	      
	CLOSE CFks
	DEALLOCATE CFks
	PRINT 'Done.'
	COMMIT TRANSACTION
	PRINT 'Committed.'
END TRY
BEGIN CATCH
	PRINT 'Error.'
	PRINT 'FK: ' + @FK_NAME + ' TABLE: ' + @TABLE_NAME
	ROLLBACK TRANSACTION
	PRINT 'Rolled back.'
END CATCH

jdraggi

Dude, that's awesome! Thank you!!!

tchaplin@miningis.com.au

Sorry I left a hard coded schema name 'mmrs.' in there, you will need to remove it.

opspark

Great script, thanks.
Re the hard-coded piece, this works:

DECLARE CFks CURSOR FOR
SELECT
[name] fk_name,
OBJECT_SCHEMA_NAME([parent_object_id]) + '.' + OBJECT_NAME([parent_object_id]) table_name
FROM
sys.foreign_keys fk
WHERE
is_not_trusted=1