Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a procedure to rebuild indexes with a cursor. Instead of executing properly, I get an error message:

Msg 2812, Level 16, State 62
Could not find stored procedure

What to do to get rid of this situation?

USE [ACT]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
ALTER PROCEDURE [dbo].[myProcedure]
AS
BEGIN

DECLARE @idx_name VARCHAR(200) -- Index name  
DECLARE @tableName VARCHAR(256) -- Table Name
DECLARE @sql varchar(500) 

DECLARE Index_Rebuild_Cursor CURSOR FOR  
SELECT OBJECT_NAME(ps.OBJECT_ID)tbl_name,b.name idx_name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id 
WHERE ps.avg_fragmentation_in_percent>80

OPEN Index_Rebuild_Cursor   
FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name

WHILE @@FETCH_STATUS = 0   
BEGIN   

 SET @sql='ALTER INDEX '+@idx_name+' ON '+@tableName+' REBUILD'
 EXEC @sql

 FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name
END   

CLOSE Index_Rebuild_Cursor   
DEALLOCATE Index_Rebuild_Cursor

END
share|improve this question
    
[ACT] is the correct database? –  bummi Jan 28 '13 at 6:33
    
yes ACT is the correct one. –  JP Chauhan Jan 28 '13 at 6:34
    
sqlserverlearner.com/2011/… –  bummi Jan 28 '13 at 6:58
    
yes bummi. EXEC (@sql) works. –  JP Chauhan Jan 28 '13 at 7:17
    
Does your database have multiple schemas? Is the default schema to your login set to something else, other than "dbo"? –  Shawn Melton Jan 28 '13 at 8:17

2 Answers 2

search for the sp with

SELECT  *
FROM    sys.sql_modules
WHERE   definition LIKE '%SET @sql=''ALTER INDEX ''+@idx_name+'' ON ''+@tableName+'' REBUILD''%'

if it exists under a different name you might find it with this query.

share|improve this answer
    
There is no stored procedure to find - did you look at the OP's code? The error happens inside the procedure and is a result of the EXEC call - which does not call a stored procedure. –  Aaron Bertrand Jan 28 '13 at 14:46

EXEC @sql is invalid syntax (you need EXEC(@sql); - those parentheses are important). Nonetheless, I highly recommend:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'ALTER ...';
-----------^ this N is important too
...

EXEC sp_executesql @sql;

Doing this as a habit makes sure that you will be able to use strongly typed parameters when you can.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.