CREATE and DROP INDEX during two SQL 2000 databases synchro

Stéphane LAURANS

There was an opened issue on older SQL Compare old versions that seems to be still opened.

First, I would apologize if the response was already given anywhere else but I didn't find one.

I have an SQL Server 2008 R2 instance with two databases that are SQL 2000 compatibles.

When SQL Compare generates the scripts, part of the script is an Index deletion.

The script is :

DROP INDEX [IndexName] ON dbo.[TableName]

If I run the script on my down SQL 2008 Server, the script is working find but when my customer is running the script on an SQL 2000 Instance, he has the following error:

For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname.

What I understand is that the script would be :

DROP INDEX [TableName].[IndexName]

So, is there a way to generate the right script for SQL 2000 ?


There is the same issue for the INDEX creation:
The INCLUDE predicats aren't valid on SQL 2000 but SQL Compare generates ones.

Regards,

peter.peart

Hi Stephane,

Thanks for your post. To the best of my knowledge, the issue is being caused by the fact that you are running in compatibility mode rather than against an actual SQL Server 2000 DB.

As far as I was aware, and according to Books Online, compatibility mode is only really used as a stop gap measure if you have applications connecting to a DB that are not in an upgraded state:

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2008, convert the application to work properly. Then use ALTER DATABASE to change the compatibility level to 100. The new compatibility setting for a database takes effect when the database is next made current (whether as the default database on login or on being specified in a USE statement).

With that in mind, you are still comparing to a 2008 DB so the syntax generated would be a 2008 based script. In order to get this working properly, I would recommend giving out the below free utility to your customers:

http://labs.red-gate.com/Tools/Details/RedGateSnapper

The concept of this is that it allows anyone to take a snapshot of their database schema (no data included). If they then sent you this snapshot you could then generate a syntactically correct sync script against the snapshot which could then be applied to the live DB.

HTH!

Pete