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

JohnnyTJohnnyT Posts: 20
edited August 26, 2013 11:20AM in SQL Compare Previous Versions
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.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • JohnnyTJohnnyT Posts: 20
    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.
    · Share on Twitter
  • JohnnyTJohnnyT Posts: 20
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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]'
    · Share on Twitter
  • JohnnyTJohnnyT Posts: 20
    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.
    · Share on Twitter
  • JohnnyTJohnnyT Posts: 20
    Hi Brian,
    Any update on when the fix for this bug will be released?

    Thanks,
    John
    · Share on Twitter
Sign In or Register to comment.