User profile for JohnnyT

Hi Brian, Any update on when the fix for this bug will be released? Thanks, John / comments

Hi Brian, Any update on when the fix for this bug will be released? Thanks, John

0 votes

Brian Donahue wrote: I have logged a bug SC-6379. Thanks Brian. Just to clarify, it's also adding an extra "dbo" as well the extra brackets. / comments

Brian Donahue wrote: I have logged a bug SC-6379. Thanks Brian. Just to clarify, it's also adding an extra "dbo" as well the extra brackets.

0 votes

Here is a simple scenario for you to reproduce this error. Hope this helps. -- Create identical TestA and TestB databases create database TestA ; go use TestA ; go create default [dbo].[empty_number] AS 0 ; go create table dbo.Table1 ( column1 char(10) not null, column2 int not null ) ; execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ; go create database TestB ; go use TestB ; go create default [dbo].[empty_number] AS 0 ; go create table dbo.Table1 ( column1 char(10) not null, column2 int not null ) ; execute sp_bindefault N'dbo.empty_number', N'dbo.Table1.column2' ; go -- Now drop the "column2" in Table1 in the TestA database. use TestA ; go execute sp_unbindefault N'dbo.Table1.column2' ; go alter table dbo.Table1 drop column column2 ; go -- Now use SQL Compare 10 to generate a script to make TestB match TestA. Be sure to -- check the "Add object existence checks" option before generating the script. -- Run the generated script on the TestB database and see the error. / comments

Here is a simple scenario for you to reproduce this error. Hope this helps.-- Create identical TestA and TestB databases create database TestA ; go use TestA ; go create default [dbo].&#91...

0 votes

Brian Donahue wrote: Hello, Thanks for supplying all of the output from SQL Compare... would it be possible to script a scenario that caused this problem to happen? I can see where the problem occurs in your deployment but I can't figure out how to reproduce it so I can submit a proper bug report. I'll see what I can do. It may take some time for me to set this up. / comments

Brian Donahue wrote: Hello, Thanks for supplying all of the output from SQL Compare... would it be possible to script a scenario that caused this problem to happen? I can see where the problem o...

0 votes

Found new bug when using "Add object existence check" option

We very rarely drop columns from our database, but we just had a case where we received an error in a generated SQL Compare script when dropping a column that did not have the default object proper...

2 followers 6 comments 0 votes

Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch. / comments

Thank you Chris and Brian. I ran a couple tests and it works perfectly. I appreciate your quick turnaround on providing a patch.

0 votes

Thanks Brian. The thing is, the relation does exist because it was re-created further up the script. The NOCHECK option on the ADD CONSTRAINT applies only to checking for conforming data. The constraint still remains enforced. It needs to be marked as unenfoced, but the IF NOT EXISTS is preventing it. So the "ALTER TABLE [dbo].[cartrule] NOCHECK CONSTRAINT [FK_cartrule_items] " line to disable enforcement does not get processed. When I manually remove the IF NOT EXISTS lines and let the ALTER TABLE NOCHECK CONSTRAINT lines get processed, they databases now match. When I generate a deployment script without these two new options enabled, there are no IF NOT EXISTS lines at all and the ALTER TABLE NOCHECK CONTSTRAINT lines are always processed. / comments

Thanks Brian. The thing is, the relation does exist because it was re-created further up the script. The NOCHECK option on the ADD CONSTRAINT applies only to checking for conforming data. The co...

0 votes

Thanks for the response, Brian. Regardless of the reason to drop and re-add a foreign key, it is a reproducible bug that occurs when "Add object existence checks" and "Use DROP and CREATE instead of ALTER" options are both enabled. Non-enforced foreign key relations are erroneously made enforced after running the generated deployment script. As I pointed out above, the cause is the incorrect IF NOT EXISTS wrapper that is added to script, preventing it from specifying the NOCHECK option on the relations. When these two options are not enabled, the NOCHECK is properly specified, so the problem is definitely in these new options. Hopefully this can be fixed with a patch release soon, as using these two new options can really screw up deployments. I'm just glad I caught this before I deployed it to my 200+ clients. Thanks, John / comments

Thanks for the response, Brian. Regardless of the reason to drop and re-add a foreign key, it is a reproducible bug that occurs when "Add object existence checks" and "Use DROP and CREATE instead ...

0 votes

After a little more research, I found the bug. The deployment script does try to disable enforcement of the FK constraint, but the logic added by the "Add object existence checks" is incorrect. Here's an example. I have two tables named "items" and "cartrule" which have an unenforced relation defined in it. With this option checked, the deployment script drops the constraint... PRINT N'Dropping foreign keys from [dbo].[cartrule]' GO IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U')) ALTER TABLE [dbo].[cartrule] DROP CONSTRAINT[FK_cartrule_items] GO Then it rebuilds it... PRINT N'Adding foreign keys to [dbo].[cartrule]' GO IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U')) ALTER TABLE [dbo].[cartrule] WITH NOCHECK ADD CONSTRAINT [FK_cartrule_items] FOREIGN KEY ([department], [category], [item]) REFERENCES [dbo].[items] ([department], [category], [item]) NOT FOR REPLICATION GO Then it attempts to mark it as unenforced... PRINT N'Disabling constraints on [dbo].[cartrule]' GO IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cartrule_items]', 'F') AND parent_object_id = OBJECT_ID(N'[dbo].[cartrule]', 'U')) ALTER TABLE [dbo].[cartrule] NOCHECK CONSTRAINT [FK_cartrule_items] GO However, the logic added by the "Add object existence checks" option is not correct. It should be "IF EXISTS", not "IF NOT EXISTS". The way it is now, it remains enforced. Thanks. / comments

After a little more research, I found the bug. The deployment script does try to disable enforcement of the FK constraint, but the logic added by the "Add object existence checks" is incorrect. H...

0 votes

New bug in comparing Foreign Key Relationships

Thank you for adding the "Use DROP and CREATE instead of ALTER" option that we requested. This will be a big help when deploying update scripts to clients who may already have pre-release versions...

3 followers 8 comments 0 votes

How can we help you today?

JohnnyT

Activity overview