WITH NOCHECK problems using command line

Ben Mills

I'm using the command line version of SQL Compare 5 to sync a remote database with a local database. The command line version keeps finding differences in foreign keys where the local database has the WITH NOCHECK option specified, but the remote database doesn't. It finds these differences every night and doesn't seem to sync the databases. What's even more confusing is that when I run the SQL Compare 5 UI, no differences are found (and I do not have the ignore WITH NOCHECK option selected). Also, when I look at the foreign keys in Enterprise Manager, they seem to be identical.

In summary, the command line version of SQL Compare seems to be finding foreign key differences that don't exist.

I have fixed this for now by specifying the IgnoreWithNocheck in the command line options.

Thanks,
Ben

Brian Donahue

Hello Ben,

It sounds like you've done exactly the right thing by ignoring WITH NOCHECK on the command line options. There are a few ways to use WITH NOCHECK in SQL. One usage does not check existing data on creation and one disables the foreign key. WITH CHECK forces the constraint to check existing data on creation.

So what may be happening is that SQL Compare is creating the foreign keys as part of the synchronization, but it's possible that they're not working because they're disabled. Is this the problem?

Ben Mills

Hi Brian,

I definitely don't have any disabled foreign keys. As I said, the weirdest thing about this is that the UI and command lines tools are giving me different results. The other issue is that the command line sync job is running every night and is complaining about differences, but not actually syncing the databases (just to be clear, there haven't been any actual schema changes for a few weeks now, but I get the sync report every night).

This worked flawlessly with the version 3 command line tool. I'm only having the problem since switching to version 5. This is not a big issue for me as I can use the IgnoreWithNocheck option, but it does seem like a bug that should be fixed at some point.

Thanks,
Ben

Tilman

Hi Ben,

could you send us schema snapshots of your 2 databases? That would help us enormously to fix your problem.

You can create schema snapshots with the SQL Compare UI by going to Tools > Create Snapshot..., or with the command line by using the /makesnapshot switch. Having said that, maybe you shouldn't use the command line, given that it produces the errors you have reported.

If you could send these snapshots to support@red-gate.com, that would be much appreciated.

Tilman

Ben Mills

Thanks for offering to take a look at this, but I'm able to send our database snapshot to you (company policy). As I said, I'm dealing with this issue by using the IgnoreWithNocheck option. I just wanted to make you aware of it.

Thanks,
Ben

Tilman

Hi Ben,

I tried to reproduce your problem but failed. I created 2 dbs with an identical table which has an identical foreign key on it. I then compared the dbs with the SQL Compare UI and command line and both times the tables came up as identical (using default options). Could you maybe tell me what version of SQL Compare 5 you are using and what SQL Server version your dbs are on?

Thanks,

Tilman

Ben Mills

I'm using SQL Compare 5.1.0.8. The databases are SQL Server 2000.

Here is a snippet of the HTML report generated by the command line. The left side (database 1) is the remote SQL Server at my hosts. The right hand side (database 2) is the local SQL Server on our network.

-- Foreign Keys <> -- Foreign Keys

ALTER TABLE [dbo].[AdditionalHost] ADD CONSTRAINT [FK_AdditionalHost_Site] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Site] ([SiteID]) <> ALTER TABLE [dbo].[AdditionalHost] WITH NOCHECK ADD CONSTRAINT [FK_AdditionalHost_Site] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Site] ([SiteID])

When I look at the foreign keys in Enterprise Manager, the "Check existing data on creation" is NOT checked for either database. I'm not sure if this is the checkbox that corresponds to the WITH NOCHECK option.

Brian Donahue

Hi Ben,

I had a look at your snapshots and didn't see a problem. I synchronized the snapshot to a blank database (not selecting the ignore with nocheck), then compared the snapshot to the database that I'd created. I found no differences.

The only difference is that I had used the project file (/pr:) that I'd created with the UI version of SQL Compare, to make sure I had all of the correct settings. (I think it's easier)

Ben Mills

Hi Brian,

I realize this is a strange one and seems to be hard to reproduce, but this has only started since I switched to SQL Compare 5. Here's what I'm going to do now:

1. I ran a compare using the UI and saw the differences.
2. I ran the sync.
3. I closed the UI and reopened.
4. I ran another compare and everything matches.
5. I will not touch the schema of either database.
6. Let's see what happens tonight when I run the compare and sync script.

Thanks for taking the time to look into this.

As I said, I realize this is a hrad one for you to reproduce.

Ben

Ben Mills

I think I may have figured out what the problem is. Here's my sequence of events:

1. Compare and sync A with B using SQL Compare.
2. Compare schemas again and everything matches.
3. Compare and sync DATA in A with B using SQL Data Compare.
4. Compare schemas again and there are WITH NOCHECK discrepancies.

It seems that SQL Data Compare is causing schema changes. I seem to remember reading that SQL Data Compare has a new algorithm that disables the foreign keys before copying data. It seems that it is not reenabling them to their original state.

Ben

Brian Donahue

Hello Ben,

Thanks for the extra information. SQL Data Compare does always re-create foreign keys as WITH NOCHECK. This is by design.

The only exception is if the key is disabled to begin with, in which it is created as NOCHECK CONSTRAINT and left the way it was originally.

If you have any more questions about this, please let me know.

Ben Mills

Hi Brian,

I don't see how this is by design. Surely a tool to sync data shouldn't be changing the schema in any way.

The problem for me is that I use a modelling tool (Dezign for Databases) to generate new versions of the schema into an empty database. I then sync that database with my development copy to get the schema changes (whilst leaving the data in place in the development copy). The tool creates the foreign keys without the WITH NOCHECK option and then SQL Data Compare is trying to set the WITH NOCHECK option. The result is a battle between one tool trying to remove the option and the other trying to add it.

I can ignore these differences, but I think that this is a bug that should be fixed in the next version.

Thanks for the help,
Ben

richardjm

OK it has taken me a little bit of time to work out why I have done this in this manner but I think I've finally got to the bottom of it. The with nocheck should only happen for foreign keys of tables which aren't being migrated. So take the scenario...

Table1 data(1,2,3)
Table2 data(1,2,3) foreign key to Table1
Table3 data(1,2,3) foreign key to Table2

We have this data in two databases. In one database we delete all the 3s. We then choose to synchronise the databases but we choose not to sychronise Table2. If I was to drop and re-create the foreign keys all without WITH NOCHECK then it would fail as the data looks like...

Table1 data(1,2)
Table2 data(1,2,3) foreign key to Table1
Table3 data(1,2) foreign key to Table2

So I must drop the foreign key for table2 and recreate it with nocheck or the migration would fail.

Hope this makes sense.



PS I have the comment in my code... // Currently this will break the sql compare NOCHECK behaviour

Ben Mills

Thanks for the update. The explanation makes sense, though I wonder why people would want to put their data in an inconsistent state. I'm synchronizing all the tables with this batch file:

SQLDataCompare.exe /server1:Production /database1:mydatabase1 /username1:myusername1 /password1:mypassword1 /server2:localhost /database2:mydatabase2 /out:DataCompareResults.txt /scriptfile:DataCompareSynchronize.sql /force /options:Default /synchronize > DataCompareOutput.txt

Some of the tables are lookup tables, where none of the data is changing day to day. I wonder if these qualify as "not being migrated".

Anyway, thanks for all the help. Despite my moaning about this issue, SQL Compare and SQL Data Compare are great products that have become an indispensible part of my development process.

Ben Mills