Red Gate forums :: View topic - NOCHECK added
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

NOCHECK added

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gknierim



Joined: 16 Nov 2004
Posts: 24

PostPosted: Tue Nov 15, 2011 5:28 pm    Post subject: NOCHECK added Reply with quote

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?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Wed Nov 16, 2011 6:52 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
gknierim



Joined: 16 Nov 2004
Posts: 24

PostPosted: Thu Nov 17, 2011 6:14 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Thu Nov 17, 2011 6:37 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dustinlw



Joined: 22 Feb 2012
Posts: 2
Location: Provo, Utah, USA

PostPosted: Wed Feb 22, 2012 6:58 pm    Post subject: NOCHECK added for tables marked to package data with no data Reply with quote

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.
Back to top
View user's profile Send private message
ffukes



Joined: 19 Mar 2012
Posts: 2

PostPosted: Mon Mar 19, 2012 4:57 am    Post subject: Reply with quote

I can run through it and log any bugs that need t
o be logged.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1120
Location: My desk.

PostPosted: Tue Mar 20, 2012 5:06 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group