Queries which include DMFs return a syntax error

When I execute
queries which include DMFs the queries return a syntax error.

For
example:

SELECT — TOP 20

qs.sql_handle,

qs.execution_count,

qs.total_worker_time AS
Total_CPU,

total_CPU_inSeconds
= –Converted from
microseconds

qs.total_worker_time/1000000,

average_CPU_inSeconds
= –Converted from
microseconds

(qs.total_worker_time/1000000) / qs.execution_count,

qs.total_elapsed_time,

total_elapsed_time_inSeconds
= –Converted from microseconds

qs.total_elapsed_time/1000000

–,
st.text

–,
qp.query_plan

from

sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

ORDER
BY qs.total_worker_time desc

The
above query is failing for sys.dm_exec_sql_text(qs.sql_handle) and 
sys.dm_exec_query_plan (qs.plan_handle) 
DMFs.

 The reason for
this error is that when the user is attempting to run query is not
compatible with previous version of the SQL Server. When SQL Server 2000 is
upgraded to SQL Server 2005 or SQL Server 2008, the database object
compatibility should be also upgraded to the next version. 

To check
the compatibility level of the master database: 

select
name,compatibility_level from sys.databases

where
name = ‘master’

If the above query returns values 80 or less that means your
database is not compatible with SQL Server 2005 or SQL Server 2008

You can change this   by navigating to the Database property
page of the master database and changing the compatibility level to 90 or 100.

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'master', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'master', 100




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |