BUG - 'STATISTICS_NORECOMPUTE' is not a recognized option

Joakim Mårlöv

SQL Compare happily generates the following SQL (SQL server 2000) with wrong syntax:

ALTER TABLE [dbo].[TaskHeuristicRun] ADD CONSTRAINT [PK_TaskHeuristicRun] PRIMARY KEY CLUSTERED ([taskHeuristicRunID]) WITH STATISTICS_NORECOMPUTE=ON

Error message from SQL server:
'STATISTICS_NORECOMPUTE' is not a recognized ALTER TABLE option.

As a side note, the comparison in the GUI shows a slightly different syntax with parentheses added around the STATISTICS_NORECOMPUTE=ON - and that is also wrong syntax :-).

Regards,

Joakim

Brian Donahue

Hi Joakim,

I'm aware that this happens on SQL Server 2000 migration scripts when you compare against a SQL Server 2005 database, because the program assumes that you want to run the migration script against whatever database platform is specified on the right-hand side of the compare databases dialogue.

If you switch the two databases around in the project settings, specifying the SQL Server 2000 on the right and the SQL 2005 server on the left, it should get SQL Compare to omit the SQL 2005-specific STATISTICS_NORECOMPUTE clause from the migration script.

Joakim Mårlöv

Thanks Brian,

but it actually is a comparison between two SQL Server 2000 servers.

Joakim

Brian Donahue

Hi,

This is a bit of a mystery. As far as I know, STATISTICS_NORECOMPUTE didn't appear in T-SQL until SQL Server 2005.

Are you positive that the server isn't really a SQL 2005 running a database in SQL Server 2000 (80) compatibility mode?

Joakim Mårlöv

>Are you positive that the server isn't really a SQL 2005 running a database in SQL Server 2000 (80) compatibility mode?

Yes sir 8) .

Unless one of my colleagues have done rogue installations secretely at night... performing dark rites... laying out pentagrams of TP cable, sacrificing Microsoft Mice and old Pentiums while invoking the archangel Linux etc...

No, I am positive, no SQL 2005 on any of our production servers yet, and they all show this symptom.

Joakim