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

JohnnyT

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 properly first unbound from it.

In our case, we are dropping the "curr_count" column from the "max4sale" table. This column has the "empty_number" object bound to it for default values. Here is the relevant SQL Compare generated code...

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'max4sale', 'COLUMN', N'curr_count'))
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'max4sale', 'COLUMN', N'curr_count'
GO

IF EXISTS (SELECT 1 FROM sys.columns WHERE name = N'curr_count' AND object_id = OBJECT_ID(N'[dbo].[max4sale]', 'U') AND default_object_id = OBJECT_ID(N'[dbo].[[dbo]].[empty_number]]]', 'D'))
EXEC sp_unbindefault N'[dbo].[max4sale].[curr_count]'
GO

IF COL_LENGTH(N'[dbo].[max4sale]', N'curr_count') IS NOT NULL
ALTER TABLE [dbo].[max4sale] DROP COLUMN [curr_count]
GO

The bug is in the IF EXISTS check for the "empty_number" binding. In this case:

AND default_object_id = OBJECT_ID(N'[dbo].[[dbo]].[empty_number]]]', 'D')

is incorrect and should be generated as:

AND default_object_id = OBJECT_ID(N'[dbo].[empty_number]', 'D')

This bug causes the IF EXISTS to fail and the default remains bound to the column. So when the script tries to drop the column, if fails with the following error:

The object 'empty_number' is dependent on column 'curr_count'. ALTER TABLE DROP COLUMN curr_count failed because one or more objects access this column.

Please submit this bug to the developers for a fix. I hope it can be corrected soon as I have to manually correct my generated scripts right now. Thanks very much.

Brian Donahue

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.

JohnnyT

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.

JohnnyT

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.

Brian Donahue

Yes, it looks like SQL Compare is putting extra parenthesis in the object existence check that is causing it to not find the object. I have logged a bug SC-6379. It's scripting this:

IF EXISTS (SELECT 1 FROM sys.columns WHERE name = N'column2' AND object_id = OBJECT_ID(N'[dbo].[Table1]', 'U') AND default_object_id = OBJECT_ID(N'[dbo].[[dbo]].[empty_number]]]', 'D'))
EXEC sp_unbindefault N'[dbo].[Table1].[column2]'

when it should be this...

IF EXISTS (SELECT 1 FROM sys.columns WHERE name = N'column2' AND object_id = OBJECT_ID(N'[dbo].[Table1]', 'U') AND default_object_id = OBJECT_ID(N'[dbo].[dbo].[empty_number]', 'D'))
EXEC sp_unbindefault N'[dbo].[Table1].[column2]'

JohnnyT

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.

JohnnyT

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

Thanks,
John