Problems opening standard reports

Question

I am using MS Sql Server Mgt Studio.

1. I select a database.
2. I right click and select Report / Standard Reports / Index Usage Statistics
3. I get Error: Incorrect syntax near '('

I check some of the other reports display ok. Others, for example: Top transactions, All Block Transactions, I get Error: Index(zero baseD) must be greater than or equal to zero and less than the size of the argument list.

Any ideas what could be wrong?

Answer 1

My suspicion here is that your database is still running in SQL Server 2000 compatibility mode. Right click on the database, go to properties and then options and see. If that is the case you'll have to look at the index usage statistics manually with queries, certain constructs used in these reports and using operators like the APPLY operator won't work against DBs in 2000 compatibility mode. You should consider bringing up to 2005/2008 mode but this needs to be a dev project and properly tested/vetted in a pre-production environment.

If it isn't the compatibility mode, I'd also look at what version of SSMS you are running and what version/SP the database engine is. If they aren't the same this could also be a part of the issue.. Help about on SSMS will show it's version and may show the instance version but you can also run Select @@version to get the build number of your instance for sure.