locked
Is not a recognized cursor option RRS feed

  • Question

  • Error message :"...Is not a recognized cursor option"

    Declare strCon varchar(200),@name varchar(40) DECLARE tCursor CURSOR FOR Select name from sys.objects where type ='U' OPEN tCursor FETCH NEXT FROM tCursor into @name WHILE @@FETCH_STATUS = 0 BEGIN set strCon='Select count(*) from ' + @name EXECUTE sp_executesql strCon --Select @strCon FETCH NEXT FROM tCursor into @name END; CLOSE tCursor; DEALLOCATE tCursor;


    Tuesday, July 7, 2015 7:32 AM

Answers

  • Try 

    Declare @strCon nvarchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U' and name like 'General_%' 
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 Select @strCon
    	 EXECUTE  sp_executesql @strCon
    	 
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    GO


    Ahsan Kabir 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. http://www.aktechforum.blogspot.com/

    Tuesday, July 7, 2015 7:39 AM
  • Declare @strCon nvarchar(100),@name nvarchar(40)
    DECLARE tCursor CURSOR FOR Select name from sys.objects where type ='U'

    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    set @strCon = 'Select count(*) from ' + @name 
    EXECUTE  sp_executesql @strCon
    Select @strCon

          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;

    Vote As Helpful if it helps to solve your issue



    • Edited by AV111 Tuesday, July 7, 2015 7:44 AM
    • Proposed as answer by Satheesh Variath Tuesday, July 7, 2015 7:50 AM
    • Marked as answer by WASI UDDIN BAN Tuesday, July 7, 2015 8:56 AM
    Tuesday, July 7, 2015 7:39 AM
  • Hello - You have not defined the variable strCon correctly, resulting into failure. This should work for you


    -- strCon needs to replaced with @strCon where-ever it is referenced
    
    Declare @strCon varchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U'
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 EXECUTE  sp_executesql strCon
    	 --Select @strCon
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Eric__Zhang Wednesday, July 8, 2015 3:05 AM
    Tuesday, July 7, 2015 7:46 AM
  • You should prefix @(at) symbol while declaring a variable.
    Declare @strCon varchar(200),@name varchar(40)


    Satheesh
    My Blog | How to ask questions in technical forum

    • Marked as answer by Eric__Zhang Wednesday, July 8, 2015 3:05 AM
    Tuesday, July 7, 2015 7:50 AM
  • Make it as below :

    Declare @strCon nvarchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U' and name like 'General_%' 
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 Select @strCon
    	 EXECUTE  sp_executesql @strCon
    	 
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    GO

    Tuesday, July 7, 2015 8:50 AM
  • You have done a wrong on declaring variable need @
    Tuesday, July 7, 2015 8:52 AM

All replies

  • Try 

    Declare @strCon nvarchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U' and name like 'General_%' 
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 Select @strCon
    	 EXECUTE  sp_executesql @strCon
    	 
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    GO


    Ahsan Kabir 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. http://www.aktechforum.blogspot.com/

    Tuesday, July 7, 2015 7:39 AM
  • Declare @strCon nvarchar(100),@name nvarchar(40)
    DECLARE tCursor CURSOR FOR Select name from sys.objects where type ='U'

    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    set @strCon = 'Select count(*) from ' + @name 
    EXECUTE  sp_executesql @strCon
    Select @strCon

          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;

    Vote As Helpful if it helps to solve your issue



    • Edited by AV111 Tuesday, July 7, 2015 7:44 AM
    • Proposed as answer by Satheesh Variath Tuesday, July 7, 2015 7:50 AM
    • Marked as answer by WASI UDDIN BAN Tuesday, July 7, 2015 8:56 AM
    Tuesday, July 7, 2015 7:39 AM
  • Hello - You have not defined the variable strCon correctly, resulting into failure. This should work for you


    -- strCon needs to replaced with @strCon where-ever it is referenced
    
    Declare @strCon varchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U'
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 EXECUTE  sp_executesql strCon
    	 --Select @strCon
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Eric__Zhang Wednesday, July 8, 2015 3:05 AM
    Tuesday, July 7, 2015 7:46 AM
  • You should prefix @(at) symbol while declaring a variable.
    Declare @strCon varchar(200),@name varchar(40)


    Satheesh
    My Blog | How to ask questions in technical forum

    • Marked as answer by Eric__Zhang Wednesday, July 8, 2015 3:05 AM
    Tuesday, July 7, 2015 7:50 AM
  • Make sure that the variable is prefixed with @ and will be look like

    Declare @strCon varchar(200)

    Tuesday, July 7, 2015 7:51 AM
  • Make it as below :

    Declare @strCon nvarchar(200),@name varchar(40)
    DECLARE tCursor CURSOR FOR 	Select name from sys.objects where type ='U' and name like 'General_%' 
    
    OPEN tCursor
    FETCH NEXT FROM tCursor into @name
    WHILE @@FETCH_STATUS = 0
       BEGIN
    	 set @strCon='Select count(*) from ' + @name 
    	 Select @strCon
    	 EXECUTE  sp_executesql @strCon
    	 
    
          FETCH NEXT FROM tCursor into @name
       END;
    CLOSE tCursor;
    DEALLOCATE tCursor;
    GO

    Tuesday, July 7, 2015 8:50 AM
  • You have done a wrong on declaring variable need @
    Tuesday, July 7, 2015 8:52 AM