Incorrect Compatibility level Error 15414:

I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
JohnThis suggests you are using some capabilities available in SQL2000 that were
not previously available in down level versions. To check for indexed views
or indexes on computed columns run the following query in Query Analyzer.
These indexes will need to be dropped in order to downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John