After SQL Data Compare, SQL Compare shows WITH NOCHECK dif

• 
  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.

  Brian Donahue August 06, 2008 06:26

  - 0 +
• 
  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.

  Michelle T August 06, 2008 08:02

  - 0 +
• 
  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

  jdraggi August 06, 2008 09:11

  - 0 +
• 
  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
  

  tchaplin November 20, 2008 21:26

  - 0 +
• 
  Dude, that's awesome! Thank you!!!

  jdraggi November 21, 2008 12:28

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

  tchaplin November 21, 2008 20:19

  - 0 +
• 
  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

  opspark April 13, 2011 15:58

  - 0 +

Add comment

Please sign in to leave a comment.