After SQL Data Compare, SQL Compare shows WITH NOCHECK dif

jdraggijdraggi Posts: 8
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 TMichelle T Posts: 566 Gold 1
    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.
    Software Developer
    Redgate Software
  • 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
  • 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&#40;MAX&#41;
    	DECLARE @TABLE_NAME AS NVARCHAR&#40;MAX&#41;
    	DECLARE @SQL AS NVARCHAR&#40;MAX&#41;
    
    	DECLARE CFks CURSOR FOR
    		SELECT 
    			&#91;name&#93; fk_name,
    			'mmrs.' + OBJECT_NAME&#40;&#91;parent_object_id&#93;&#41; 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
    
  • Dude, that's awesome! Thank you!!!
  • Sorry I left a hard coded schema name 'mmrs.' in there, you will need to remove it.
  • 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
Sign In or Register to comment.