NOCHECK added

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

NOCHECK added

Postby gknierim » Tue Nov 15, 2011 5:28 pm

So, I've created create and upgrade scripts using the Packager. I got them to run successfully (with a few tweaks) but when I did a SQL Compare against the Master database of what it should look like and the upgraded database, the upgraded database had NOCHECK added for some of the constraints on the tables. Is this a bug with packager or SQL Compare?
gknierim
 
Posts: 24
Joined: Tue Nov 16, 2004 3:16 pm

Postby james.billings » Wed Nov 16, 2011 6:52 pm

Thanks for your post- it's hard to say for sure where the problem occurred. Both products have an option to ignore "WITH NOCHECK" which may be related though (Tools > Options in SQL Packager, and Edit Project > Options in SQL Compare)

In addition, SQL Data Compare has an option "Force constraints to be re-enabled with CHECK". It doesn't look like Packager exposes this though.

I do recall there being the odd bug reported in this area (although not directly in relation to SQL Packager) so if the above doesn't help and can supply some more detailed information to reproduce what you saw, I can see about logging a bug if it's indeed incorrect.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby gknierim » Thu Nov 17, 2011 6:14 pm

I've seen the option to ignore WITH NOCHECK but I want that to be enabled. I think that would be an important option not to ignore.

I've had the same problem WITH NOCHECK with SQL Compare. I will sync up the databases to make sure that it is not present, and then when I come back the next time (week later) and do the SQL Compare again, the NOCHECK somehow has been added back. So I'm not sure that there is a bug there but I've seen this issue lots of times. I manage alot of database so I keep thinking that it was another database that I did this on. I will need to investigate this further to make sure it is consistent.

After I run the scripts made by packager, I went into SSMS and scripted the table to a new query window and it scripts out the ALTER TABLE WITH NOCHECK but then right after it, it adds a WITH CHECK in a separate statement. So I'm not sure what that is all about either.
gknierim
 
Posts: 24
Joined: Tue Nov 16, 2004 3:16 pm

Postby james.billings » Thu Nov 17, 2011 6:37 pm

We do have a couple of bugs logged in this area, but I'm not 100% sure they relate to what you're seeing:

One (ref: SC-3419) was raised as a result of [url="http://www.red-gate.com/MessageBoard/viewtopic.php?p=20555"]this forum post[/url]

Another, SC-4077, relates to the "Ignore with nocheck" option in Compare also then ignoring if the constraint is enabled or not. This was by design though due to the way SQL handles these.

Not sure if that helps - if you want to mail in some step-by-step changes of what you had configured to start with, and how it changed, I can run through it and log any bugs that need to be logged.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

NOCHECK added for tables marked to package data with no data

Postby dustinlw » Wed Feb 22, 2012 6:58 pm

We have encountered this as well, in the latest SQL Packager 6.4.0.8.

We narrowed it down to the following steps:
1. Select a table for data to be packaged that has no data in it, that has a foreign key constraint
2. Run the package to create the script.

Results: The script contains:
1. a foreign key DROP CONSTRAINT for that table's constraint at the beginning of the data section,
2. then (rightly) no INSERTs for the table,
3. then a foreign key constraint ADDed WITH NOCHECK at the end of the data section

Work-arounds: Prior to running the packager to create the script:
1. Add at least 1 row of data to the table (WITH NOCHECK disappears from ADD CONTSTRAINT statement), or
2. Un-select the table for data to be packaged (no DROP or ADD CONSTRAINT)

The following does NOT work around the issue:
1. Tools, Options... Data Options, Synchronization Behavior:
2. Un-check: Disable foreign keys
Because INSERTs will fail due to order in which they are performed (alphabetical) and foreign key constraints.

I am curious whether the RedGate team considers this a bug and will address it. We would like to mark tables to synchronize data that may or may not have any data in them when the packager is run without messing up schema by re-creating constraints WITH NOCHECK.
dustinlw
 
Posts: 2
Joined: Wed Feb 22, 2012 6:14 pm
Location: Provo, Utah, USA

Postby ffukes » Mon Mar 19, 2012 4:57 am

I can run through it and log any bugs that need t
Imageo be logged.
ffukes
 
Posts: 2
Joined: Mon Mar 19, 2012 4:49 am

Postby james.billings » Tue Mar 20, 2012 5:06 pm

Thanks for the reproduction steps supplied - I've logged a bug for the development team to investigate (ref. SPA-588). I don't currently have an ETA for this to be looked at unfortunately as they are currently working on other areas, however I'll update this thread as and when I hear more.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests