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

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

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.

MORE INFORMATION

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

Restore the database in SQL Server 2000:

RESTORE DATABASE myDb from disk='d:\myDb.bak' WITH REPLACE
go

Create an index on the computed column in SQL Server 2000:
```
USE myDb
go

CREATE INDEX myIndex ON myTable(col3)
go
```

Modification Type:	Major	Last Reviewed:	11/17/2004
Keywords:	kbBug kbpending KB281310