July 4, 2008 at 6:01 am
Hi,
I have been puzzling over this for a whil now and I'm unable to fathom this out.
I am trying to create a stored procedure:
CREATE PROCEDURE [dbo].[pr_rebuild_all_indexes]
AS
DECLARE @temp_name varchar (100)
DECLARE @cmd nvarchar (350)
SELECT [NAME]
INTO #temp_table_names
FROM sysobjects
WHERE [type] = 'U'
AND [NAME] <> 'dtproperties'
ORDER BY [name]
WHILE (select count(*) from #temp_table_names) > 0
BEGIN
SELECT TOP 1 @temp_name = [NAME] from #temp_table_names
BEGIN TRY
SET @CMD = 'ALTER INDEX ALL ON ' + @temp_name + ' REBUILD WITH (ONLINE=ON)'
exec sp_executesql @cmd
END TRY
BEGIN CATCH
SET @CMD = 'DBCC DBREINDEX (' + @temp_name + ')'
exec sp_executesql @cmd
END CATCH
DELETE FROM #temp_table_names where [NAME] = @temp_name
END
DROP TABLE #temp_table_names
However, when I parse the code, I getthe following message:
Msg 137, Level 15, State 1, Procedure pr_rebuild_all_indexes, Line 25
Must declare the scalar variable "@CMD".
Msg 137, Level 15, State 1, Procedure pr_rebuild_all_indexes, Line 31
Must declare the scalar variable "@CMD".
I can't understand why it's telling me to declare the variable @cmd, yet it is quite obviously declared at the top of the code.
I googled the above problem, and the only real bit of info I could find was that it *may* be something to do with the collation of the server/database.
The Collation of the server is Latin1_General_BIN
Any help would be greatly appreciated.
July 4, 2008 at 6:06 am
I'm pretty sure that binary collations are case sensitive so @cmd is not the same as @CMD.
Change one or the other and try again.
Kev
July 4, 2008 at 6:10 am
Thanks kevriley.
That's worked perfectly.
I can't believe it was something so simple like that.
Thanks again
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply