Microsoft KB Archive/281310

= BUG: You cannot create an index on a computed column in a SQL Server 2000 database that was restored from SQL Server 7.0 =

Article ID: 281310

Article Last Modified on 11/17/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q281310



BUG #: 351344 (SHILOH)



SYMPTOMS
When a SQL Server 7.0 database that contains a table with a computed column is backed up and restored into SQL Server 2000, an attempt in SQL Server 2000 to create an index on that computed column immediately after the restore fails with the following error message:

Server: Msg 1959, Level 16, State 1, Line 2

Cannot create index on view or computed column because this database is not SQL Server compatible.



WORKAROUND
To work around this problem:  Issue a CHECKPOINT statement before you create the index.

-or-

  Run the following stored procedure: sp_dbcmptlevel dbname, 80 because sp_dbcmptlevel issues a checkpoint. 



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



Steps to Reproduce Behavior
  Create a table in SQL Server 7.0 that has a computed column in a user database: USE myDb go

CREATE TABLE myTable( col1 int, col2 int, col3 as col1*col2) go   Back up the database: BACKUP DATABASE myDb to disk='d:\myDb.bak' go </li>  Restore the database in SQL Server 2000: RESTORE DATABASE myDb from disk='d:\myDb.bak' WITH REPLACE go </li>  Create an index on the computed column in SQL Server 2000: USE myDb go

CREATE INDEX myIndex ON myTable(col3) go </li></ol>

Keywords: kbbug kbpending KB281310

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.