Error while executing DMV - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.
DMV - has got very special place for DBAs since the inception of SQL Server 2005.
In this case one of the thme is very important to find out the index statistics within your database, such as sys.dm_db_index_physical_stats statement. Recently one of the user complained about running DMVs on a specific SQL Server instance where the error was generated as follows:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
So the first one to check is to ensure they are using correct syntax during the execution of any DMV, taking that into my own testing environment worked like a charm such as the script mentioned in this BlockingListDMV post. So back to the user asking for more details and eventually found a 'usual mistake' that every user commits, running the DMV against a SQL Server 2000 version database. Eventually the user confirmed that the specific database was recently upgraded from SQL Server 2000 and had some problems so kept the compatibility mode back to 80 instead of 90.
So its better to ensure the database(s) you are using are in 90 compatibility mode before running the DMVs within your SQL Server 2005 environment.