Red Gate forums :: View topic - Found new bug when using "Add object existence check" option
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Sun May 12, 2013 3:15 am    Post subject: Found new bug when using "Add object existence check" option Reply with quote

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:

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:

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


is incorrect and should be generated as:

Code:

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:

Quote:

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



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Tue May 14, 2013 2:37 pm    Post subject: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon May 20, 2013 9:33 pm    Post subject: Re: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Tue May 21, 2013 9:48 pm    Post subject: Reply with quote

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

Code:

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



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Fri May 24, 2013 10:48 am    Post subject: Reply with quote

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

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon May 27, 2013 10:46 pm    Post subject: Re: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Mon Aug 26, 2013 4:20 pm    Post subject: Reply with quote

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

Thanks,
John
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