April 7, 2011 at 9:21 pm
i am using the MCTS exam 70-432 MSSQL server 2008 training kit..
on page 237-238, i have retyped this code into a query on my computer...
i have rechecked it twice to see if i made a type-o,
and i dont beleive i did.. so I was wondering what else could these errors be reffering to??
and i am getting these errors...
Msg 137, Level 15, State 2, Procedure asp_reindex, Line 18
Must declare the scalar variable "@schema".
Msg 137, Level 15, State 2, Procedure asp_reindex, Line 33
Must declare the scalar variable "@schema".
Msg 137, Level 15, State 2, Procedure asp_reindex, Line 42
Must declare the scalar variable "@schema".
CREATE PROCEDURE dbo.asp_reindex @database SYSNAME, @fragpercent INT
AS
DECLARE @cmdNVARCHAR(max),
@tableSYSNAME,
@shcemaSYSNAME
--Using a a cursor for demonstration purposes.
--Could also do this with a table variable and a WHILE loop
DECLARE curtable CURSOR FOR
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, database_id) SchemaName,
OBJECT_NAME(object_id,database_id) TableName
FROM sys.dm_db_index_physical_stats (DB_ID(@database),NULL,NULL,NULL,'SAMPLED')
WHERE avg_fragmentation_in_percent >= @fragpercent
FOR READ ONLY
OPEN curtable
FETCH curtable INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @shcema + '.' + @table
+ 'REBUILD WITH (ONLINE = ON)'
--Try ONLINE build first, if failure, change to OFFLINE build.
BEGIN TRY
EXEC sp_executesql @cmd
END TRY
BEGIN CATCH
BEGIN
SET @cmd = 'ALTER INDEX ALL ON' + @database + '.' + @schema + '.'
+ @table + ' REBUILD WITH (ONLINE = OFF)'
EXEC sp_executesql @cmd
END
END CATCH
FETCH curtable INTO @schema, @table
END
CLOSE curtable
DEALLOCATE curtable
GO
April 7, 2011 at 9:25 pm
Your DECLARE statement for @schema is mis-spelled...so it does not recognize the other lines of code that reference @schema.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 7, 2011 at 9:33 pm
..
man my eyes are not working right..
reading for the last 3 hours probably hasnt helped..
thanks!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply