Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query SQL in compatibiliy level 80 error

Posted on 2016-09-02
3
Medium Priority
?
58 Views
Last Modified: 2016-09-02
Good afternoon,

I need to execute the query below to find out the use of a table, but the base is on this basis compatibility level is 80, the error.

Does anyone know how I can adapt this script to not introduce error?

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

Queries that I need to run are attached

Thank you
query1.txt
query2.txt
0
Comment
Question by:Support_38
3 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41782257
The solution is, you can use DB_ID(), but not directly inside a call as parameter. See for example here: http://www.sqlservercentral.com/Forums/Topic992205-391-1.aspx

If you depend on compatibility levels, because the database moved recently and you don't want to risc a compatibility issue, fix this as soon as possible, testing all your sql code to see whether the database really needs this limitation. You never do yourself a favor in keeping anything in some compatibility level, as it hinders you using new things, you don't improve.

Bye, Olaf.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 41782318
Query 1, as below.  Do similar thing for query 2.

DECLARE @db_id smallint
SET @db_id = DB_ID()

SELECT o.name AS [Table_Name], x.name AS [Index_Name],
       i.partition_number AS [Partition],
       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
       i.leaf_update_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
            + i.leaf_delete_count + i.leaf_update_count
            + i.leaf_page_merge_count + i.singleton_lookup_count
           ) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (@db_id, NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC
0
 

Author Closing Comment

by:Support_38
ID: 41782401
thank you
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question