HELP! SC8 not respecting the database compatibility mode
JohnnyT
Posts: 20
This problem was discussed in the "DROP INDEX SQL format error for SQL2000 but not SQL2005" thread in the "SQL Compare Previous Versions" section ( http://www.red-gate.com/MessageBoard/vi ... php?t=4221 ), but it wasn't resolved there and I don't know if that section is still active. Here's my problem...
Our database is set to compatibility mode 8.0 (2000) because many of our clients are still running SQL 2000. I used to have my live and staging databases running in SQL 2000. SQL Compare 7 (and then 8 ) always worked great for creating version update scripts for all clients, regardless if they were running SQL2000, 2005 or 2008.
Last month, I just upgraded my PC to Win7 (x64), so I could no longer run SQL 2000. I therefore moved my staging and live databases to SQL 2005, but kept the compatibility mode of both databases set to 8.0. On the last version update script I made with SC8 and deployed to our clients, all of them still on SQL 2000 get "For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname" and other syntax errors.
The only workaround I can see is to install and run SQL 2000 inside Windows Virtual PC just to create these SQL Compare scripts that will work for our SQL 2000 clients. Please say this isn't so. You guys REALLY NEED to change SQL Compare to respect the compatibility mode setting of at least the target database.
In the meantime, is there anything I can do? Right now, I and my SQL 2000 clients are screwed. Thanks.
Our database is set to compatibility mode 8.0 (2000) because many of our clients are still running SQL 2000. I used to have my live and staging databases running in SQL 2000. SQL Compare 7 (and then 8 ) always worked great for creating version update scripts for all clients, regardless if they were running SQL2000, 2005 or 2008.
Last month, I just upgraded my PC to Win7 (x64), so I could no longer run SQL 2000. I therefore moved my staging and live databases to SQL 2005, but kept the compatibility mode of both databases set to 8.0. On the last version update script I made with SC8 and deployed to our clients, all of them still on SQL 2000 get "For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname" and other syntax errors.
The only workaround I can see is to install and run SQL 2000 inside Windows Virtual PC just to create these SQL Compare scripts that will work for our SQL 2000 clients. Please say this isn't so. You guys REALLY NEED to change SQL Compare to respect the compatibility mode setting of at least the target database.
In the meantime, is there anything I can do? Right now, I and my SQL 2000 clients are screwed. Thanks.
Comments
Unfortunatly this still isn't possible. SQL Comapre will create the scripts based on the target database version, rather than the compatiability mode, as this is a much safer way of doing things.
SQL Comapre will always create the scripts in the format of the target database, so if the databse is a SQL 2005 in comp mode 80, it will create scripts that run on SQL 2005 in comp mode 80, not scripts for a SQL 2000 database.
However, we have an open feature request to allow the user to force SQL compare to create the scripts targeting a specific version, which I have asked the development team to review again. For your reference the feature tracking code for this is SC-3517.
As a workaround, you could sync the SQL 2005 comp mode 80 database to a SQL 2000 server, and then use the SQL 2000 database as the target for the scripts.
1) Create a script folder of your target database
2) In the edit project dialogue, set the SQL Server version of the script folder to SQL Server 2000
2) Compare to the script folder
3) Choose to generate a synchronization script in the sync wizard
This should fool SQL Compare into believing it's synching to a SQL 2000 server and hence make it generate the right syntax.
Redgate Software
Hopefully the developers will approve the feature request to force scripts to a specific SQL Server version. I understand the compatibility problems involved, but those of us who needs this feature wouldn't attempt to sync an older database to a newer database which contains object types that are not supported in the target database. We understand these limitations and would live with the warning and error messages produced in the script generation. Please do consider adding this feature. Thanks again for your help.