none
A cursor with the name ' ' already exists.

    Question

  • Hello,

    I am receiving the following error because I am declaring a cursor more than once within the loop.

    A cursor with the name 'Metric_Cursor' already exists.

    Am I able to declare the cursor outside of the loop?

    I also tried closing and deallocating the cursor just before the loop ends, but it adds repeating rows to my tables, even though I received no errors.

    Any ideas? Thanks in advance!

    ALTER PROCEDURE [dbo].[sp_InsertLMHeaderFromLabourHist]
    	
    AS
    BEGIN
    	DECLARE @HeaderID int
    	DECLARE @StartDate datetime
    	DECLARE @DateSubmit datetime
    	DECLARE @Shift varchar(10)
    	DECLARE @StartTime varchar(10)
    	DECLARE @EmpID int
    	DECLARE @EmpNo varchar(50)
    	DECLARE @CrewID int
    	DECLARE @Zone varchar(50)
    	DECLARE @EquipID int
    	DECLARE @ActID int
    	DECLARE @Reg float
    	DECLARE @T15 float 
    	DECLARE @T20 float
    	DECLARE @Comment varchar(255)
    	DECLARE @DetailID int
    	DECLARE @MetricID int
    	DECLARE @MetricValue float
    	DECLARE @RowCount int
    	DECLARE @ItHeader int 
    	DECLARE @ItDetail int 
    	DECLARE @ItMetric int
    	SET @RowCount = (SELECT COUNT(LabourHistTEMP.ID) FROM LabourHistTEMP) 
    	SET @ItHeader = 1
    	SET @ItDetail = 1
    	SET @ItMetric = 1
    	
    	DECLARE Header_Cursor CURSOR for 
    	select LabourHistTEMP.EmpNo, LabourHistTEMP.ShiftStartDate, LabourHistTEMP.Date_Submitted, LabourHistTEMP.ShiftDayNight, LabourHistTEMP.ShiftStartTime, Employee.EmpID, Crew.CrewID, LabourHistTEMP.Zone 
    	from LabourHistTEMP left outer join Employee on LabourHistTEMP.EmpNo = Employee.EmpNo left outer join Crew on LabourHistTEMP.Crew = Crew.Description 
    	open Header_Cursor
    	fetch next from Header_Cursor into @EmpNo, @StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone
    	WHILE @@FETCH_STATUS = 0
    	begin
    		print CAST(@StartDate as varchar) + ' ' + cast(@DateSubmit as varchar) + ' ' + @Shift + ' ' + @StartTime + ' ' + cast(@EmpID as varchar) + ' ' + cast(@CrewID as varchar) + ' ' + @Zone
    		INSERT INTO LMActivityHeader(Date, SysDate, Shift, StartTime, EmpID, CrewID, Zone)
    		values(@StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone)
    		--SET @HeaderID = CAST(SCOPE_IDENTITY() AS INT)		--assigns the last primary key stored in this scope to @HeaderID
    		SET @HeaderID = (SELECT IDENT_CURRENT('LMActivityHeader'))	--assign the last primary key in table, LMActivityHeader to the variable
    		declare Detail_Cursor Cursor for 
    		select Equipment.EquipID, LMActivityTypes.ActID, LabourHistTEMP.Time_Reg, LabourHistTEMP.Time_T15,LabourHistTEMP.Time_T20, LabourHistTEMP.Comment
    		from LabourHistTEMP left outer join LMActivityTypes on LabourHistTEMP.Dept = LMActivityTypes.Dept AND LabourHistTEMP.SubActivity = LMActivityTypes.SubActivity and LabourHistTEMP.Activity = LMActivityTypes.Activity
    		left outer join Equipment on LabourHistTEMP.MachineNo = Equipment.EquipNo
    		where LabourHistTEMP.EmpNo = @EmpNo And LabourHistTEMP.ShiftStartDate = @StartDate and LabourHistTEMP.ShiftDayNight = @Shift
    		open Detail_Cursor
    		fetch next from Detail_Cursor into @EquipID, @ActID, @Reg, @T15, @T20, @Comment
    		WHILE @@FETCH_STATUS = 0
    		begin
    			print cast(@HeaderID as varchar) + ' ' + cast(@EquipID as varchar) + ' ' + cast(@ActID as varchar) + ' ' + cast(@reg as varchar) + ' ' + cast(@T15 as varchar) + ' ' + cast(@T20 as varchar)
    			INSERT INTO LMActivityDetail(HeaderID, EquipID, ActID, Reg, T15, T20, Comment)
    			values(@HeaderID, @EquipID, @ActID, @Reg, @T15, @T20, @Comment)
    			SET @DetailID = (SELECT IDENT_CURRENT('LMActivityDetail'))
    				
    			DECLARE Metric_Cursor CURSOR FOR 
    			SELECT MetricID, MetricValue
    			FROM Metric1
    			WHERE Metric1.EmpNo = @EmpNo and Metric1.ActID = @ActID and Metric1.ShiftStartDate = @StartDate
    			OPEN Metric_Cursor
    			FETCH NEXT FROM Metric_Cursor INTO @MetricID, @MetricValue
    			--INSERT INTO LMActivityMetric(MetricID, Value, DetailID)
    			--VALUES(@MetricID, @MetricValue, @DetailID) 
    			WHILE @@FETCH_STATUS = 0
    			BEGIN 
    				PRINT CAST(@DetailID AS VARCHAR) + ' ' + CAST(@MetricID AS VARCHAR) + ' ' + CAST(@MetricValue AS VARCHAR)
    				INSERT INTO LMActivityMetric(MetricID, Value, DetailID)
    				VALUES(@MetricID, @MetricValue, @DetailID) 
    				FETCH NEXT FROM Metric_Cursor
    				INTO @MetricID, @MetricValue
    			END
    			fetch next from Detail_Cursor into @EquipID, @ActID, @Reg, @T15, @T20, @Comment	
    			--CLOSE Metric_Cursor
    			--deallocate Metric_Cursor
    		end
    		fetch next from Header_Cursor into @EmpNo, @StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone
    		--close Detail_Cursor
    		--deallocate Detail_Cursor
    	end
    	close Header_Cursor
    	close Detail_Cursor
    	CLOSE Metric_Cursor
    	deallocate Header_Cursor
    	deallocate Detail_Cursor
    	DEALLOCATE Metric_Cursor
    END
    


    Monday, August 08, 2011 8:53 PM

Answers

  • I'm not sure that I get this with the cursor starts at the first row. If it should start at some other row, why don't add that to the WHERE clause?

    Generally, always declare your cursors as STATIC LOCAL. The default is a dynamic cursor, and they can cause some confusing results.

    I did not look in extreme detail at your code, but my gut feeling is that you probably don't need any cursors at all, but can do all in a set-based operation. Shorter code, and a lot more performant.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 08, 2011 10:11 PM

All replies

  • Close each Cursor after their loop ends. UnComment the commented lines for them and at the end you need to close only Header_Cursor.
    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Monday, August 08, 2011 8:56 PM
  • Either you have to move the declaration part of your detail_cursor outside the first while loop or put the close detail_curosr and deallocate detail_cursor within the loop...

    actually you alrey have them at thier places and commented them... do uncomment them...

    and remove the ones at the last...

    I think this is how your code should be 

     

    ALTER PROCEDURE [dbo].[sp_InsertLMHeaderFromLabourHist]
    	
    AS
    BEGIN
    	DECLARE @HeaderID int
    	DECLARE @StartDate datetime
    	DECLARE @DateSubmit datetime
    	DECLARE @Shift varchar(10)
    	DECLARE @StartTime varchar(10)
    	DECLARE @EmpID int
    	DECLARE @EmpNo varchar(50)
    	DECLARE @CrewID int
    	DECLARE @Zone varchar(50)
    	DECLARE @EquipID int
    	DECLARE @ActID int
    	DECLARE @Reg float
    	DECLARE @T15 float 
    	DECLARE @T20 float
    	DECLARE @Comment varchar(255)
    	DECLARE @DetailID int
    	DECLARE @MetricID int
    	DECLARE @MetricValue float
    	DECLARE @RowCount int
    	DECLARE @ItHeader int 
    	DECLARE @ItDetail int 
    	DECLARE @ItMetric int
    	SET @RowCount = (SELECT COUNT(LabourHistTEMP.ID) FROM LabourHistTEMP) 
    	SET @ItHeader = 1
    	SET @ItDetail = 1
    	SET @ItMetric = 1
    	
    	DECLARE Header_Cursor CURSOR for 
    	select LabourHistTEMP.EmpNo, LabourHistTEMP.ShiftStartDate, LabourHistTEMP.Date_Submitted, LabourHistTEMP.ShiftDayNight, LabourHistTEMP.ShiftStartTime, Employee.EmpID, Crew.CrewID, LabourHistTEMP.Zone 
    	from LabourHistTEMP left outer join Employee on LabourHistTEMP.EmpNo = Employee.EmpNo left outer join Crew on LabourHistTEMP.Crew = Crew.Description 
    	open Header_Cursor
    	fetch next from Header_Cursor into @EmpNo, @StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone
    	WHILE @@FETCH_STATUS = 0
    	begin
    		print CAST(@StartDate as varchar) + ' ' + cast(@DateSubmit as varchar) + ' ' + @Shift + ' ' + @StartTime + ' ' + cast(@EmpID as varchar) + ' ' + cast(@CrewID as varchar) + ' ' + @Zone
    		INSERT INTO LMActivityHeader(Date, SysDate, Shift, StartTime, EmpID, CrewID, Zone)
    		values(@StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone)
    		--SET @HeaderID = CAST(SCOPE_IDENTITY() AS INT)		--assigns the last primary key stored in this scope to @HeaderID
    		SET @HeaderID = (SELECT IDENT_CURRENT('LMActivityHeader'))	--assign the last primary key in table, LMActivityHeader to the variable
    		declare Detail_Cursor Cursor for 
    		select Equipment.EquipID, LMActivityTypes.ActID, LabourHistTEMP.Time_Reg, LabourHistTEMP.Time_T15,LabourHistTEMP.Time_T20, LabourHistTEMP.Comment
    		from LabourHistTEMP left outer join LMActivityTypes on LabourHistTEMP.Dept = LMActivityTypes.Dept AND LabourHistTEMP.SubActivity = LMActivityTypes.SubActivity and LabourHistTEMP.Activity = LMActivityTypes.Activity
    		left outer join Equipment on LabourHistTEMP.MachineNo = Equipment.EquipNo
    		where LabourHistTEMP.EmpNo = @EmpNo And LabourHistTEMP.ShiftStartDate = @StartDate and LabourHistTEMP.ShiftDayNight = @Shift
    		open Detail_Cursor
    		fetch next from Detail_Cursor into @EquipID, @ActID, @Reg, @T15, @T20, @Comment
    		WHILE @@FETCH_STATUS = 0
    		begin
    			print cast(@HeaderID as varchar) + ' ' + cast(@EquipID as varchar) + ' ' + cast(@ActID as varchar) + ' ' + cast(@reg as varchar) + ' ' + cast(@T15 as varchar) + ' ' + cast(@T20 as varchar)
    			INSERT INTO LMActivityDetail(HeaderID, EquipID, ActID, Reg, T15, T20, Comment)
    			values(@HeaderID, @EquipID, @ActID, @Reg, @T15, @T20, @Comment)
    			SET @DetailID = (SELECT IDENT_CURRENT('LMActivityDetail'))
    				
    			DECLARE Metric_Cursor CURSOR FOR 
    			SELECT MetricID, MetricValue
    			FROM Metric1
    			WHERE Metric1.EmpNo = @EmpNo and Metric1.ActID = @ActID and Metric1.ShiftStartDate = @StartDate
    			OPEN Metric_Cursor
    			FETCH NEXT FROM Metric_Cursor INTO @MetricID, @MetricValue
    			--INSERT INTO LMActivityMetric(MetricID, Value, DetailID)
    			--VALUES(@MetricID, @MetricValue, @DetailID) 
    			WHILE @@FETCH_STATUS = 0
    			BEGIN 
    				PRINT CAST(@DetailID AS VARCHAR) + ' ' + CAST(@MetricID AS VARCHAR) + ' ' + CAST(@MetricValue AS VARCHAR)
    				INSERT INTO LMActivityMetric(MetricID, Value, DetailID)
    				VALUES(@MetricID, @MetricValue, @DetailID) 
    				FETCH NEXT FROM Metric_Cursor
    				INTO @MetricID, @MetricValue
    			END
    			fetch next from Detail_Cursor into @EquipID, @ActID, @Reg, @T15, @T20, @Comment	
    			CLOSE Metric_Cursor
    			deallocate Metric_Cursor
    		end
    		fetch next from Header_Cursor into @EmpNo, @StartDate, @DateSubmit, @Shift, @StartTime, @EmpID, @CrewID, @Zone
    		close Detail_Cursor
    		deallocate Detail_Cursor
    	end
    	close Header_Cursor
    	deallocate Header_Cursor
    END
    
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Monday, August 08, 2011 9:01 PM
  • Hi Surendra,

    I need the detail_cursor and metric_cursor to be within the loops because I am performing a select statement on values from the previous cursor. When I uncomment the close and deallocate within the loops, I get duplicate rows because the cursor will always start from the beginning of the loop.  Is there another method to do this? Temporary tables, maybe?

    Monday, August 08, 2011 9:11 PM
  • Hi Arbi,

    When I only close the cursor after the loop ends, the error remains. If I close and deallocate it, there is no error, but I will get duplicate rows in my tables because the cursor will always start at the first row. Any other ideas or methods?  

    Monday, August 08, 2011 9:15 PM
  • From the information that you have shared with us it is difficult to suggest any alternate methods or why you are getting duplicates and how to  get rid of them... Even if I suggest something we might be going in circles...

     

    For understading your situation we need the below information.

    1) table definitions of all the tables involved in the above script 

    2) sample data for those tables

    3) expected output...

    Now coming to the suggestions:

    1) I see that you are using the IDENT_CURRENT() function that means, you should have a identity column for all your tables ..... which will act as a primary key in normal scenarios (although it may not I many other scenarios), so instead you trying to fetching all the columns / selecting all your columns in your cursors, try to use the identity column only, and then write a select statement in your while loop on the identity column to fetch the other columns...

    2) if you are getting duplicate records means, your joins are improper... might be you are missing some key fields to eliminate the duplicates... so check the select statements that you are using to declare the cursor and try to establish a relationship which will not give you a duplicates.....

     

    3) finally cursors are not the best approach in many of the cases as the peformance might be low with the cursors when considered with the other set based approaches...

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Monday, August 08, 2011 9:27 PM
  • I'm not sure that I get this with the cursor starts at the first row. If it should start at some other row, why don't add that to the WHERE clause?

    Generally, always declare your cursors as STATIC LOCAL. The default is a dynamic cursor, and they can cause some confusing results.

    I did not look in extreme detail at your code, but my gut feeling is that you probably don't need any cursors at all, but can do all in a set-based operation. Shorter code, and a lot more performant.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 08, 2011 10:11 PM