April 8, 2011 at 7:29 am
Here is my code which is returning the following error, and database in question is a version SQL 2008 db. What am i doing wrong?
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
DECLARE @dbName AS varchar(MAX)
DECLARE @use AS varchar(MAX)
SET @dbName = ' '
SELECT @dbName = (SELECT [Name] FROM sys.databases WHERE database_id = 11)
SET @use = 'USE '+ @dbName + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
SET @use = @use +
'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 sys.dm_db_index_physical_stats(db_id(db_name()),
NULL,NULL,NULL,''DETAILED'') ps -- Faster option: SAMPLED
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN sys.tables t
ON ps.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY [Schema], [Table], [Index]'
EXEC (@use)
April 8, 2011 at 7:34 am
April 8, 2011 at 4:03 pm
Justin-281851 (4/8/2011)
Here is my code which is returning the following error, and database in question is a version SQL 2008 db. What am i doing wrong?Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Like Calvo said, I think the problem is the GO itself; you aren't allowed to have multiple batches inside a dynamic batch.
So change:
SET @use = 'USE '+ @dbName + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
to
SET @use = 'USE '+ @dbName + ';' + CHAR(13) + CHAR(10)
And give it a try.
April 8, 2011 at 4:29 pm
Thanks guys. It works now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply