none
I have Infinite loop with my Stored Procedure

    Question

  • I am testing my very simple Stored Procedure where I loop through every single database.  For every single database returned, I simply do a SELECT 'Dummy Record'.  The SP has been running for over 20 minutes now when it should be running no more than a minute.

    Thank you in advance for the help!

    /****** Object:  StoredProcedure [dbo].[WP_SP_Test]    Script Date: 04/01/2013 08:47:29 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[WP_SP_Test] 
    	
    AS
    
    BEGIN
    
    SET NOCOUNT ON;
    
    DECLARE @name Varchar(80) -- database Name
    
    DECLARE DatabaseName_cursor CURSOR STATIC FOR
    
    SELECT name
     	FROM master..sysdatabases
    WHERE NAME NOT IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB')
    
    OPEN DatabaseName_cursor 
    FETCH NEXT FROM DatabaseName_cursor INTO @name 
    
    WHILE @@FETCH_STATUS = 0 
    
    DECLARE @SQL nvarchar(max) 
    SET @SQL = 
    
    '
    USE ['+@Name+'] 
    
     
      SELECT ''Dummy Record''
    
    '
    
    END
    
    
    CLOSE DatabaseName_cursor   
    DEALLOCATE DatabaseName_cursor 
    
    GO
    
    
    

    Monday, April 01, 2013 2:16 PM

Answers

  • WHILE @@FETCH_STATUS = 0 
    
    DECLARE @SQL nvarchar(max) 
    SET @SQL = 
    
    '
    USE ['+@Name+'] 
    
     
      SELECT ''Dummy Record''
    
    '
    FETCH NEXT FROM DatabaseName_cursor
    END

    You are missing a "FETCH NEXT FROM DatabaseName_cursor" in the WHILE loop so you are looping through the same record.  Instead of using a cursor, you could also look at using sp_foreachdb to accomplish this a bit easier.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Monday, April 01, 2013 2:20 PM
    Moderator

All replies

  • WHILE @@FETCH_STATUS = 0 
    
    DECLARE @SQL nvarchar(max) 
    SET @SQL = 
    
    '
    USE ['+@Name+'] 
    
     
      SELECT ''Dummy Record''
    
    '
    FETCH NEXT FROM DatabaseName_cursor
    END

    You are missing a "FETCH NEXT FROM DatabaseName_cursor" in the WHILE loop so you are looping through the same record.  Instead of using a cursor, you could also look at using sp_foreachdb to accomplish this a bit easier.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Monday, April 01, 2013 2:20 PM
    Moderator
  • Thank you Sam for your help. 

    It's working now.

    Monday, April 01, 2013 2:21 PM