Incorrect Compatibility level Error 15414:

John,
I ran the indexed views query and indexed computed columns
query on the database that has the compatability level set
to 70. Only one table returned from the indexed computed
columns query.
Output from indexed computed columns query
Object_Name Column_Name
AccountDetail AcctDtlSrceTble
__________________________________________________________
DROP INDEX AccountDetail.AcctDtlSrceTble
How do I obtain a sql script to regenerate the
AccountDetail.AcctDtlSrceTble indexed computed column?
What are the steps that I need to take to change the
compatiblity level 80 to 70?
Thank You,
John
This 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
name
---
---
-- ---
---
--
AccountDetail
AcctDtlSrceTble
(1 row(s) affected)The easiest way is to use the Query Analyzer Object Browser and expand the
node for the table and then the underlying one for Indexes. You can then
select the relavent index, right click on it and choose Script Object to New
Window As > Create.You can do the same to generate the script to drop it.
You can use the system stored procedure dbcmptlevel to change the
compatability mode e.g.
EXEC sp_dbcmptlevel 'pubs', 70
However, I'm not clear on why you need to do this ?
--
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:257801c427d3$568d9ac0$a101280a@.phx.gbl...
> John,
> I ran the indexed views query and indexed computed columns
> query on the database that has the compatability level set
> to 70. Only one table returned from the indexed computed
> columns query.
> Output from indexed computed columns query
> Object_Name Column_Name
> AccountDetail AcctDtlSrceTble
> __________________________________________________________
> DROP INDEX AccountDetail.AcctDtlSrceTble
> How do I obtain a sql script to regenerate the
> AccountDetail.AcctDtlSrceTble indexed computed column?
> What are the steps that I need to take to change the
> compatiblity level 80 to 70?
> Thank You,
> John
>
> This 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
>
>
> name
>
> ---
> ---
> -- ---
> ---
> --
> AccountDetail
> AcctDtlSrceTble
> (1 row(s) affected)