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

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

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

Postby JohnnyT » Sun May 12, 2013 3:15 am

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...

Code: Select all
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:

Code: Select all
AND default_object_id = OBJECT_ID(N'[dbo].[[dbo]].[empty_number]]]', 'D')


is incorrect and should be generated as:

Code: Select all
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.
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby Brian Donahue » Tue May 14, 2013 2:37 pm

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby JohnnyT » Mon May 20, 2013 9:33 pm

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
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby JohnnyT » Tue May 21, 2013 9:48 pm

Here is a simple scenario for you to reproduce this error. Hope this helps.

Code: Select all
-- 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.
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby Brian Donahue » Fri May 24, 2013 10:48 am

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:
Code: Select all
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...
Code: Select all
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]'
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby JohnnyT » Mon May 27, 2013 10:46 pm

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
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm

Postby JohnnyT » Mon Aug 26, 2013 4:20 pm

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

Thanks,
John
JohnnyT
 
Posts: 17
Joined: Wed Nov 11, 2009 7:11 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests