http://www.sqlservercentral.com/blogs/sqlandme/2011/11/14/sql-server-_1320_-error-msg-102_2C00_-level-15_2C00_-state-1_2C00_-line-x-when-using-sys.dm_5F00_db_5F00_index_5F00_physical_5F00_stats/

Printed 2014/11/26 08:46AM

SQL Server – Error Msg 102, Level 15, State 1, Line x when using sys.dm_db_index_physical_stats

By Vishal.Gajjar, 2011/11/14

You run into this error when using sys.dm_db_index_physical_stats DMV on a database which is running under SQL Server 2000 (80) compatibility mode.

USE [SqlAndMe]

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Result Set:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ‘SqlAndMe’.

There is nothing wrong with the syntax at all, yet you might spend time scratching your head!!! DMVs do not support a function as a parameter when compatibility mode is set to SQL Server 2000 (80). You can check the current compatibility mode using sys.databases:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'SqlAndMe'

GO

Result Set:

name          compatibility_level

SqlAndMe      80

To use sys.dm_db_index_physical_stats without changing the database compatibility mode you can try one of the below solutions:

Solution 1: Run the statement in the context of a different database which has compatibility mode of SQL Server 2005 (90) or higher:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'master'

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('SqlAndMe.dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Since the compatibility mode of [master] is set to SQL Server 2008 (100) this will work fine. We need to use three-part name for object here as we are running the statement in the context of different database.

Result Set:

name          compatibility_level

master        100

 

database_id object_id   index_id    partition_number

6           1906105831  1           1               

Solution 2: Remove functions calls from arguments of sys.dm_db_index_physical_stats. Pass constants or variables instead. This solution does not require context switching and can be done from the context of the database itself:

USE [SqlAndMe]

GO

 

DECLARE @dbid VARCHAR(20)

DECLARE @objid VARCHAR(20)

 

SET @dbid   = DB_ID('SqlAndMe')

SET @objid  = OBJECT_ID('dbo.ProductList')

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (@dbid, @objid, 1, NULL, 'SAMPLED')

GO

Result Set:

database_id object_id   index_id    partition_number

6           1906105831  1           1               

 

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe


Filed under: Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.