Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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

Comments

Posted by Anonymous on 30 November 2011

Pingback from  SQL Server ??? Error Msg 102, Level 15, State 1,... | SQL Server | Syngu

Posted by pkrudysz on 5 December 2011

I like the 2nd solution.  That could be an improvement to my current script.

Leave a Comment

Please register or log in to leave a comment.