Databases
StackOverflow2013
Postsdbo
POIncorrect syntax near '(' when checking index fragmentation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIncorrect syntax near '(' when checking index fragmentation
    primarykey
    data
    text
    <p>I'm trying to check index fragmentation in my database using SQL 2008 R2.</p> <p>I'm using the following code, taken from <a href="http://msdn.microsoft.com/en-gb/library/ms189858(v=sql.100).aspx" rel="nofollow">http://msdn.microsoft.com/en-gb/library/ms189858(v=sql.100).aspx</a> with a couple of name changes:</p> <pre><code>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; </code></pre> <p>GO</p> <p>When I run it I get the error:</p> <pre><code>Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '('. </code></pre> <p>Can anyone spot where I'm going wrong?</p> <h2>UPDATE</h2> <p>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?</p> <h2>UPDATE</h2> <p>In response to Akrem's sugegstion, I also tried this but get the same error.</p> <pre><code>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; </code></pre> <h2>UPDATE</h2> <p>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.</p> <p>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.</p> <p>Any ideas anyone?</p> <h2>UPDATE</h2> <p>OK, this is weird. If I take the DB_ID and OBJECT_ID out of the statement the batch runs perfectly.</p> <pre><code>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; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USR-C
    1. USR-C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIncorrect syntax near '(' when checking index fragmentation
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs this the exact query you have, seems correct.
      singulars
      1. POIncorrect syntax near '(' when checking index fragmentation
      1. USvon v.
    2. COExact query, copied in from SQL Server Management Studio.
      singulars
      1. POIncorrect syntax near '(' when checking index fragmentation
      1. USR-C
    3. CONope, if I copy and paste this code, it runs fine, are you sure there aren't any stray characters in your query window? If you double click the error message, it will highlight the line with the problem.
      singulars
      1. POIncorrect syntax near '(' when checking index fragmentation
      1. USsteoleary
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload