April 12, 2011 at 6:56 am
Trying to put together a SSRS report for index frag levels. I have a dataset (Dataset2) that brings back a drop down list of all databases, I have a second dataset (Dataset1) that runs the following query depending on what was picked but I am getting an error that says:
Query execution failed for dataset 'Dataset1'. Incorrect syntax near ' '.
What am I doing wrong?
SELECT schema_name(t.schema_id) AS [Schema],
object_name(ps.object_id) AS [Table],
i.name AS [Index],
ps.Index_type_desc AS IndexType,
convert(TINYINT,ps.avg_fragmentation_in_percent) AS [AvgFrag%],
convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
ps.record_count AS RecordCnt,
ps.fragment_count AS FragmentCnt
FROM @dbName.sys.dm_db_index_physical_stats(db_id('@dbname'),NULL,NULL,NULL,'DETAILED') ps
INNER JOIN @dbName.sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN @dbname.sys.tables t
ON ps.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY [Schema], [Table], [Index]
April 12, 2011 at 7:00 am
You can't use the variable @DBname in your query like that. You'll need to use dynamic SQL. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 7:08 am
The error is on the From line. You can't use a variable to declare an object name like that. It will need to be done using dynamic SQL.
declare
@dbname varchar(25),
@sql varchar(2000)
select @sql =
'select * FROM ' + @dbname + '.sys.dm_db_index_physical_stats(db_id(''@dbname''),NULL,NULL,NULL,''DETAILED'') ps'
exec (@sql)
April 12, 2011 at 7:17 am
Thank you both, I got it working.
April 12, 2011 at 7:27 am
it wont work still sorry?
April 12, 2011 at 7:29 am
That code eon't work on compatibiliy level 80 you need 90+.
Also the dbname is concatenated only once, but used twice in the code.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply