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




Related Articles :

  • No Related Articles Found

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 |