Incorrect syntax near '(' when checking index fragmentation

Question

I'm trying to check index fragmentation in my database using SQL 2008 R2.

I'm using the following code, taken from http://msdn.microsoft.com/en-gb/library/ms189858(v=sql.100).aspx with a couple of name changes:

USE StockSystem;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.StockItems'),NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

When I run it I get the error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Can anyone spot where I'm going wrong?

UPDATE

Weird thing is, if I call the two functions (DB_ID and OBJECT_ID) and get the values, then substitute the values in the main select statement, it all works fine. Why can't I seemingly use the two functions within the SELECT, as per MSDN?

UPDATE

In response to Akrem's sugegstion, I also tried this but get the same error.

USE StockSystem;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('StockSystem'), OBJECT_ID(N'dbo.StockItems'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

UPDATE

Sorry for all the updates. I've tried the same query on a SQL2012 system with a copy of the database. This works without a problem. I've also tried the same statement but with different names on a different database but on the same SQL instance. This works too.

I guess it's a problem with the database then so I've restored a copy into a test SQL instance. Running the index stats for this restored copy has the same problem. As this is a production database that the company can't be without I'm limited in what else I can try.

Any ideas anyone?

UPDATE

OK, this is weird. If I take the DB_ID and OBJECT_ID out of the statement the batch runs perfectly.

use StockSystem

declare @dbid SMALLINT
declare @objectid INT

select @dbid = DB_ID('StockSystem'), @objectid = OBJECT_ID(N'dbo.StockItems')

SELECT a.index_id,name,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbid,@objectid,NULL,NULL,NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Answer 1

• Unexpected errors from table-valued functions

Your database is probably set with compatibility level 80 (SQL Server 2000) and DB_ID and OBJECT_ID functions can not be used as a parameter for dynamic management function.

You should either change compatibility level to something newer or use variables before query:

USE StockSystem;
GO
DECLARE
    @database_id INT = DB_ID(),
    @object_id   INT = OBJECT_ID(N'dbo.StockItems');

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@database_id ,@object_id , NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;