Home > Alter, Error Messages, Functions and DMVs, SQL Queries, SQL Server, Stored Procedures, System Stored Procs, T-SQL > Database Compatibility Errors : ” If it is intended as a parameter to a table-valued function,ensure that your database compatibility mode is set to 90.”

Database Compatibility Errors : ” If it is intended as a parameter to a table-valued function,ensure that your database compatibility mode is set to 90.”

You might have encounterd the errors related to compatibility while running DMVs and functions on sql server databases. The error in the header is a typical compatibility error.  you might also get some errors such as

If it is intended as a parameter to a table-valued function,
ensure that your database compatibility mode is set to 90.
Version90 database compatibility level is not supported

and some others stating that the number of parameters passed is wrong.

Most of the DMVs are not available in versions of sql before 9.0 (2005).  To resolve this you need to make the database compatible with 2005 or above, the DMVs/ missing system objects gets created in the database automatically.

You can use the ALTER DATABASE to change the compatibility as

General syntax :

ALTER DATABASE db_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

example:


ALTER DATABASE MyDataBase

SET COMPATIBILITY_LEVEL = 90;

here

80 = sql server 2000/8.0

90= sql server 2005/9.0

100 = sql server 2008/10.0

you can alternatively use the system stored proc “SP_DBCMPTLEVEL“.

syntax is : SP_DBCMPTLEVEL 'DBname','Compatibility_value'

example

EXEC SP_DBCMPTLEVEL 'MyDataBase',90;

it will be better if you change the compatibility to 9.0 or greater

Advertisements
  1. Buy
    September 21, 2009 at 10:01 pm

    yeh right.. great post, Thank You

    • Tijo
      September 22, 2009 at 1:47 am

      nice to hear it .. :)

  2. Nilima
    March 23, 2010 at 11:38 am

    why this error comes any other solution for that

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: