DROP INDEX SQL format error for SQL2000 but not SQL2005

Brendan

Has any else come across the problem/solution where the following scrip reports an error when executing against SQL Server 2000 but not SQL Server 2005?

DROP INDEX [EventIDIndex] ON [dbo].[CalendarEvent]

The error reported again SQL Server 2000 is:

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

What does with SQL Server 2000 and SQL Server 2005 is :

drop index [dbo].[CalendarEvent].[EventIDIndex]

I'm hoping there is a setting in SQL Compare 5.2 that can fix this.

Thanks in advance for any help

Brendan

Just some more info.

I found that the later DB had in it's Properties/Option a setting called 'Compatibility Level' which was set to 'SQL Server 2005' and the earlier one 'SQL Server 2000'. However, when I changed the later DB to the 2000 level SQL Compare still built the same output file.

The reason I checked this is that I found that depending on what DB I compared with SQL Compare, some output files scripted the drop index statements in both ways!!!

Brian Donahue

Hi Brendan,

I think this may be a known issue. Because SQL Compare can produce SQL 2000 and SQL 2005 compatible database scripts, and considers the compatibility level of the database as well, the order that you specify the databases becomes important. SQL Compare will always orient the compatibility of the scripts dependeing on whichever database is on the right-hand side of the comparison settings. If you put the 'compatibility level 80' database on the right, you will end up with a SQL Server 2000-compatible script. Otherwise it ends up as a 2005-compatible script.

Brendan

Thanks for the reply.

As changing the compatibility level doesn't solve this issue I will install the same product versions I was comparing on a SQL Server 2005 system, now on a SQL Server 2000 system and do the compare again.

I'll update this forum with the results late.

Cheers,
Brendan

oaklodge

I am having this problem too.

Left - SQL 2005 database
Right - SQL 7 or 2000 database

Right is the database that will be changed

The syntac for the DROP INDEX is the new syntac and does not work with SQL 2000 or SQL 7

Brian Donahue

Hi,

When you mention these versions, is it the compatibility level of the database or the SQL platform as well? In other words, are the SQL 7 and 2000 dbs running on a SQL Server 2005 in compatibility mode?

oaklodge

If I create the script running against an actual SQL 2000 Server (on the right side) then it works correctly.

So it seems it's ignoring the Database Compaitibility setting.

Brian Donahue

Hi,

Yes, it must actually be using the SQL Server platform version rather than the compatibility level of the database, as I suspected. Thanks for your input.

mdp2176

I'm not sure where this was left? We are using SQL 2005, but need scripts to be generated taht are supported on SQL 2000. Is our only option really to install a SQL 2000 instance somewhere to use for our synchronizations?

JonathanWatts

Hi there,

Sorry about the delay in getting a response to you. As Brian says SQL Compare does not use the database compatibility level for determining the syntax style. There are a number of reasons for this, the main one being that for reasons best know to Microsoft, SQL Server 2005 allows users to create some SQL 2005 objects within compatibility mode 80 databases.

We used to ignore these strange objects as they existed in the wrong compatibility database, however received complaints that as the objects could exist in the database then we should show them. Therefore, we started using the server version over the database compatibility level. Unfortunately, this setting is also the one which effects the style of syntax being used.

This means that as you say, if you want to use SQL 2000 syntax, the only work around at the moment is to use a SQL 2000 server as a target.

We will consider a force SQL 2000 or SQL 2005 syntax option for a future version of SQL Compare, but as SQL Compare 6 has almost reached code lock off, this feature will not make the initial release.

Regards,

Jonathan

virtking

Hello,

Two points.

1. I just checked v7 of the tool and I don't see any option to force the syntax. Am I just not looking at the right place?
2. I've just created a data compare script with "left" db being an SQL 2000 and "right" db being an SQL 2005. The DROP INDEX syntax was generated with 2005 syntax. I used v7 of the SQL Data Compare. So in case SQL Compare is "cured", data compare is not.

Thanks.

JohnnyT

Jonathan Watts wrote:

We will consider a force SQL 2000 or SQL 2005 syntax option for a future version of SQL Compare, but as SQL Compare 6 has almost reached code lock off, this feature will not make the initial release.

I also just got bit by this problem too. Our database is set to compatibility mode 8.0 (2000) because many of our clients are still running SQL 2000. I just upgraded my PC to Win7, so I could no longer run SQL 2000. I therefore moved my staging and live databases to SQL 2005, but kept the the compatibility mode of both databases set to 8.0. On the last version update script I made (with SQL Compare 8 ) and deployed to our clients, all of them still on SQL 2000 get this DROP INDEX syntax error.

Please don't say I have 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. You guys REALLY NEED to change SQL Compare to respect the compatibility mode settings of the databases, in spite of the minor concerns you mentioned.

In the meantime, is there anything I can do? Right now, I and my SQL 2000 clients are screwed. Thanks.

dekano

Hola, me llamo Victoria y he comprobado es que en mi opinión, en función de lo que en comparación con DB SQL Compare, algunos archivos de salida el guión de la caída de índice de declaraciones en ambos sentidos!
Besos