Problems compairing SQL 2000 and SQL 2005 databases

thies

I am having the issues with SQL Compare saying there are differences in two tables I have, one in SQL 2000 and the other in SQL 2005, when there are no semantic differences. There are syntax difference:

On SQL 2000, my table generation script contains;

[Deleted] [bit] NOT NULL CONSTRAINT [DF_AclUser_Deleted] DEFAULT (0)

However the line fed into SQL 2005 becomes (note double '()' around the DEFAULT value);

[Deleted] [bit] NOT NULL CONSTRAINT [DF_AclUser_Deleted] DEFAULT ((0))

My problem, is that I cannot change the syntax of these defaults on the 2005 server, and I cannot make SQL Compare ignore this type of difference in the SQL table generation script.

I cannot removed these double '()'s either by syncing the databases, editing using Enterprise Manager or any other way I can think of (removing them, just makes the come back in 2005)

Is there an option for SQL Compare to not treat DEFAULT ((0)) and DEFAULT (0) as differences? (But still keeps DEFAULT (1) and DEFAULT(0) as differences?). Can such an option be added to SQL Compare?

Thanks.
Thies.

Brian Donahue

Hello Thies,

You may want to have a look at this blog:

http://blogs.red-gate.com/blogs/andras/

This contains some information about how we have adjusted SQL Compare to cope with SQL Server 2005's requirement for doubling-up the partentesis.

thies

Brian Donahue wrote:

You may want to have a look at this blog:

http://blogs.red-gate.com/blogs/andras/

This contains some information about how we have adjusted SQL Compare to cope with SQL Server 2005's requirement for doubling-up the partentesis.

I read through this, but am not quite sure if it makes things clearer to me. I need a way to compare an SQL 2000 database with an SQL 2005 database, and after syncronize them the two, having SQL Compare tell me there are no differences between the two.

With the way things are now between 2000 and 2005, it compares SQL 2000s: DEFAULT (0) with SQL 2005s: DEFAULT ((0)) and says they are different. Fine, lets sync these differences and recompare. SQL Compare suggests chaning the SQL 2005s DEFAULT ((0)) with DEFAULT (0) - but when this is executed on the SQL 2005 side, it changes again to DEFAULT ((0)), as expected. If I could get the DEFAULTs used in 2000 and 2005 to be viewed as the same, I would be happy, as that would be a once off - I am happy doing changes either in the SQL 2000 database or the SQL 2005 database.

Problem: In SQL 2005 I cannot make DEFAULT (0) work. In SQL 2000 I cannot make DEFAULT ((0)) work.

My main issue is SQL Compare saying these are different, is it makes me have to add a manual (read: error prone) process of checking the defaults in a seperate task (disabling DEFAULT checking in SQL Compare).

I understand that this is not SQL Compare that has changed, but some other product. I would however love if SQL Compare where to handle this case.

I love SQL Compare/SQL Data Compare - my job would not be as fun without such tools.

Neil Davidson

Can you go to the help->about box and let us know what version of SQL Compare you're using please.

thies

Neil Davidson wrote:

Can you go to the help->about box and let us know what version of SQL Compare you're using please.

I was running 3.x of SQL Compare. I am now running 5.0.0.1622.

I have still not found a workaround comparing; SQL 2000::DEFAULT (0) with SQL2005::DEFUALT((0))

BTW; I like the little game you guys added in the About box - do I get a prize of I put all the pieces in the right place, and what is the Lama doing in the picture?

- Thies.

Dominick Reed

The llama is our newest recruit - he looks after beverage procurement and lawn maintenance. Everyone should have a company llama, if only to pose for pictures to put in the about box of your software applications.

Andras B

thies wrote:

Brian Donahue wrote:

You may want to have a look at this blog:

http://blogs.red-gate.com/blogs/andras/

This contains some information about how we have adjusted SQL Compare to cope with SQL Server 2005's requirement for doubling-up the partentesis.

I read through this, but am not quite sure if it makes things clearer to me. I need a way to compare an SQL 2000 database with an SQL 2005 database, and after syncronize them the two, having SQL Compare tell me there are no differences between the two.

With the way things are now between 2000 and 2005, it compares SQL 2000s: DEFAULT (0) with SQL 2005s: DEFAULT ((0)) and says they are different. Fine, lets sync these differences and recompare. SQL Compare suggests chaning the SQL 2005s DEFAULT ((0)) with DEFAULT (0) - but when this is executed on the SQL 2005 side, it changes again to DEFAULT ((0)), as expected. If I could get the DEFAULTs used in 2000 and 2005 to be viewed as the same, I would be happy, as that would be a once off - I am happy doing changes either in the SQL 2000 database or the SQL 2005 database.

Problem: In SQL 2005 I cannot make DEFAULT (0) work. In SQL 2000 I cannot make DEFAULT ((0)) work.

My main issue is SQL Compare saying these are different, is it makes me have to add a manual (read: error prone) process of checking the defaults in a seperate task (disabling DEFAULT checking in SQL Compare).

I understand that this is not SQL Compare that has changed, but some other product. I would however love if SQL Compare where to handle this case.

I love SQL Compare/SQL Data Compare - my job would not be as fun without such tools.

Hi,
Are you using a user defined type? The problem with UDTs is that they have changed a bit between 2000 and 2005. Under 2005 the UDTs live in a schema, under 2000 they have an owner that does not form part of the fully qualified name of the UDT. I have improved the support for such case in our internal build. The next dot release should have this fixed.
So, just to summarise, the textual difference in SQL compare shows the default text to be different, internally we consider the semantics, so we consider them the same, but the base type of the column we mark in the current version as different because of the 2000 - 2005 changes. Note that if you migrate a 2000 db to 2005 by restoring it. changing the compatibility level, the 2000 behaviour will exist in 2005

Regards,

Andras[/code]

thies

Andras wrote:

Are you using a user defined type?

No, in my database we do not use UDT. However we use bit and int a lot, and I am getting this problem on those fields.

Do I understand correct, that a dot-release should improve the problem, and how do I go about getting a notification when it is made available.

Thanks,
Thies.