SQL 2005 Cursor / update issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 Cursor / update issues

Author

Topic

shilpi4
Starting Member

3 Posts

Posted - 2011-02-01 : 11:38:49

Hello,

I'm having issues with a TSQL code where I'm using cursors. The error is "Msg 16931, ..., There are no rows in the current fetch buffer". I've done a good amount of research and tried all that other coders had to offer.

The code basically has to add an extra column to an existing table (which has been extracted from elsewhere) and perform certain calculations to show some numbers in the last column.

I'm running a query and saving it in a new table and using this table to run my code over. If however, I create this table or simulate it manually, my code works!! Not sure what the problem is... could be a memory problem, but the table is only 672 rows.

Here's the code:

use LCGProj_OPA_Eastern_2010_2030_8501
SELECT A.YEAR,
A.MONTH,
A.DAY,
A.HOUR,
SUM(A.GENERATION) AS GENERATION,
SUM(A.CAPACITY_FOR_SPIN) AS CAPACITY_FOR_SPIN,
SUM(A.[CAPACITY_FOR_NON-SPIN]) AS [CAPACITY_FOR_NON-SPIN],
SUM(A.CAPACITY_ON_MAINTENANCE) AS CAPACITY_ON_MAINTENANCE,
SUM(A.CAPACITY_ON_OUTAGE) AS CAPACITY_ON_OUTAGE,
SUM(A.CAPACITY_NOT_COMMITTED) AS CAPACITY_NOT_COMMITTED
INTO #TEMP_PSP_EM
FROM OUTGENERATORHOUR_19 AS A INNER JOIN
GENERATOR AS B ON A.UPLANGENERATORID = B.UPLANGENERATORID INNER JOIN
BUS AS C ON B.UPLANBUSID = C.UPLANBUSID INNER JOIN
ZONE AS D ON C.ZONEID = D.ZONEID
WHERE (A.YEAR = 2015) AND (A.MONTH = 2) AND (B.GENERATORSCENARIO = 9) AND (C.BUSSCENARIO = 1) AND (D.ZONESCENARIO = 5)
AND (D.ZONE_NAME IN ('IESO_NIAGARA', 'IESO_NORTHWST', 'IESO_NORTHEST', 'IESO_ESSA', 'IESO_OTTAWA', 'IESO_EAST', 'IESO_TORONTO',
'IESO_SOUTHWST', 'IESO_BRUCE', 'IESO_WEST')) AND (B.UNIT_NAME = 'E_Greenfield G1')
GROUP BY A.YEAR, A.MONTH, A.DAY, A.HOUR
ORDER BY A.YEAR, A.MONTH, A.DAY, A.HOUR;
GO

ALTER TABLE #TEMP_PSP_EM ADD EM INT;
GO

/*declaring variables and cursors for each of the relevant columns*/
declare @generation int
declare generation_cursor CURSOR FOR
select GENERATION FROM #TEMP_PSP_EM
open generation_cursor
FETCH NEXT from generation_cursor INTO @generation

declare @capacity_for_spin int
declare cap_for_spin_cursor CURSOR FOR
select CAPACITY_FOR_SPIN FROM #TEMP_PSP_EM
open cap_for_spin_cursor
FETCH NEXT from cap_for_spin_cursor INTO @capacity_for_spin

declare @capacity_not_committed int
declare cap_not_committed_cursor CURSOR FOR
select CAPACITY_NOT_COMMITTED FROM #TEMP_PSP_EM
open cap_not_committed_cursor
FETCH NEXT from cap_not_committed_cursor INTO @capacity_not_committed

declare @EM int
declare em_cursor CURSOR FOR
select EM FROM #TEMP_PSP_EM
open em_cursor
FETCH NEXT from em_cursor INTO @EM

declare @count int

/*If the fetch is succesful, the value of 'generation' is checked to determine the appropropriate value of 'EM' (Energy Margin).*/

while (@@FETCH_STATUS = 0)
begin
if (@generation = 0)
begin
set @count = 0
while(@generation = 0)
begin
if(@count<4)
begin
while (@count<4)
begin
set @EM = SUM(@generation+@capacity_for_spin)
update #TEMP_PSP_EM
set EM = @EM where current of em_cursor
set @count = @count +1

FETCH NEXT from generation_cursor INTO @generation
FETCH NEXT from cap_for_spin_cursor INTO @capacity_for_spin
FETCH NEXT from cap_not_committed_cursor INTO @capacity_not_committed
FETCH NEXT from em_cursor INTO @EM
end
end
else
begin
set @EM = SUM(@generation+@capacity_for_spin+@capacity_not_committed)
update #TEMP_PSP_EM
set EM = @EM where current of em_cursor
set @count = @count +1

FETCH NEXT from generation_cursor INTO @generation
FETCH NEXT from cap_for_spin_cursor INTO @capacity_for_spin
FETCH NEXT from cap_not_committed_cursor INTO @capacity_not_committed
FETCH NEXT from em_cursor INTO @EM
end

end
end
else
begin
set @EM = SUM(@generation+@capacity_for_spin+@capacity_not_committed)
update #TEMP_PSP_EM
set EM = @EM where current of em_cursor

FETCH NEXT from generation_cursor INTO @generation
FETCH NEXT from cap_for_spin_cursor INTO @capacity_for_spin
FETCH NEXT from cap_not_committed_cursor INTO @capacity_not_committed
FETCH NEXT from em_cursor INTO @EM
end
end

/*temporary cursors are decommissioned*/
select * from #TEMP_PSP_EM;
GO

close generation_cursor
deallocate generation_cursor
close cap_for_spin_cursor
deallocate cap_for_spin_cursor
close cap_not_committed_cursor
deallocate cap_not_committed_cursor
close em_cursor
deallocate em_cursor

drop table #TEMP_PSP_EM;
GO

shilpi4
Starting Member

3 Posts

Posted - 2011-02-01 : 17:54:58

I suppose a lot of people aren't responding because the code might look a little intimidating.

Well, Here's the problem (One more time with feeling):

I put together a table by extracting a bunch of things from different databases (The first big SELECT statement).

Then I add a column to this table (EM) where I'll be updating values.

I declare all the necessary cursors and variables which go through each row of the table; the while loops and if statements check for a bunch of conditions and update the value of EM.

Problem:
When I run this code, I get a "Msg 16931, ..., There are no rows in the current fetch buffer" error.
However, despite this error, if I execute a "Select * from table" query, I get the correct answer.
Also, instead of doing the extraction from different databases and storing it in the new table, if I just create a new table and manually input the values, it works.

Did that make it easier? Any help would be much appreciated.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-02 : 05:37:28

I do not see any condition for the loop ... e.g. While @@fetch_Status=0

Also you are declaring all cursors at a time and deallocating them together ... which is a problem. Go through the below sample

declare @par1 int
declare abc cursor for
select col1 from tab1
Open abc
Fetch Next From abc into @par1
While @@FETCH_STATUS=0
Begin

--#### The Logic should start from here
Select '1'
--#### The Logic should end by here
IF there's need of another cursor then it should be declared and deallocated with in this loop. This is how you can use multiple cursors

Fetch Next From abc into @par1
End
close abc
Deallocate abc

hope this clarified the above two identified issues of your script :D

Cheers!
MIK

shilpi4
Starting Member

3 Posts

Posted - 2011-02-02 : 13:22:30

Umm... there is a fetch condition for the while loop... The other loops are all nested within it.
And I'm deallocating the cursors as soon as I'm done using them. If I allocate and deallocate them within the loop it will be more expensive. Anyway, those are not the issues... the code works perfectly when I run it on a manually created table (which is a replica of the extracted table). I've done the breakpoints and variable watch. I get the correct result using the correct code too... only problem is I get the error. Then I have to stop the code and execute only a "select * from table" to output the result.
If anyone could help with that issue it would be great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources