SQL 2005 and SQL2000
Rippo
Posts: 10
I have a upgraded my development machine to SQL 2005 and am running a DB in compatibility level 2000 (80). The live server is still running SQL 2000. All is OK on SQL compare apart from one minor glitch.
The dev machine (2005) creates double brackets around any new created default values and the live server (2000) only has single brackets around the defaults. This means that the tables can never be sync'd up properly
See http://www.rippo.co.uk/sql.png
TO REPRODUCE THIS BEHAVIOUR
On the SQL 2005 machine create a new column (int) and assign a default (0). Now compare with a SQL 2000 machine. The table syncs up ok but when you refresh you see that the tables are not the same because of the double brackets.
Can anyone help?
The dev machine (2005) creates double brackets around any new created default values and the live server (2000) only has single brackets around the defaults. This means that the tables can never be sync'd up properly
See http://www.rippo.co.uk/sql.png
TO REPRODUCE THIS BEHAVIOUR
On the SQL 2005 machine create a new column (int) and assign a default (0). Now compare with a SQL 2000 machine. The table syncs up ok but when you refresh you see that the tables are not the same because of the double brackets.
Can anyone help?
This discussion has been closed.
Comments
It looks like SQL Compare doesn't consider this extra pair of parenthesis and there doesn't appear to be an option that will make the program ignore them.
There is going to need to be a fix for this in the software. Thanks for pointing this out.
Is this likely to happen soon?
This is drving me nuts at the moment!
I've found out that we're already aware of this and have fixed it. The update will appear in the 4.0 SQL Bundle release of SQL Compare that we're releasing right around the end of the year. This version introduces the fix as part of the SQL 2005 compliance.
I'm afraid there is no workaround for the current 3.x version of SQL Compare.
I've had a look at this again with Beta 3 of SQL Compare 4.0 and it's still a live issue. The problem is that when we do synchronize the default, we use single parenthesis, but SQL Server 2005 automatically adds the second set of them.
I don't think we can actually fix this without actually telling a small lie about the SQL Server 2005 object's data definition by trying to strip the second set of parenthesis.