Home > Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > SQL Server – Error Msg 102, Level 15, State 1, Line x when using sys.dm_db_index_physical_stats

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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. Jana
    December 23, 2011 at 3:02 pm

    Hi Vishal…need some assistance regarding moving file groups…the requirement is as follows
    ————————————-
    – Move all non-clustered indexes to INDEX01 file group
    – Move all user (U) type tables and clustered indexes to DATA01 file group
    ————————-
    I need to perform teh above in 3 databases,and in each database we have 4 schemas,and in each database we have more than 100+ tables,can you please provied me some scripts if you have

    Thanks in advance

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: