• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2587
  • Last Modified:

Cursor method: Error Msg

Whats wrong with this code? Could you pls help

----------------------------------------------------------------
go
declare @INTERNID varchar(500)
declare @STAMP2 binary(500)
declare @STAMP3_B binary(500)
declare @STAMP3_I binary(500)
declare @STAMP3_C binary(500)
declare @STAMP3_M binary(500)
declare @STAMP3_N binary(500)
declare @ID varchar(500)
declare @RMP_ID varchar(500)
declare @resultRMP varchar(100)
declare @result varchar(100)
declare @hsecs numeric
declare @date datetime
set @hsecs = 1
set @date = getdate()
exec shortstamp @date, @hsecs, @result OUTPUT

DECLARE queryRMP CURSOR FOR
SELECT _STAMP2, _ID from RMP where _Status1 = 'Generated'

DECLARE queryBUSSTRA CURSOR FOR
SELECT _STAMP3 from BUS_STRA
join RMP on RMP._STAMP2 = BUS_STRA._STAMP2 where RMP._Status1 = 'Generated'

DECLARE queryINVSTRA CURSOR FOR
SELECT _STAMP3 from INV_STRA
join RMP on RMP._STAMP2 = INV_STRA._STAMP2 where RMP._Status1 = 'Generated'

DECLARE queryCOMCLIE CURSOR FOR
SELECT _STAMP3 from COM_CLIE
join RMP on RMP._STAMP2 = COM_CLIE._STAMP2 where RMP._Status1 = 'Generated'

DECLARE queryMAJCHAL CURSOR FOR
SELECT _STAMP3 from MAJ_CHAL
join RMP on RMP._STAMP2 = MAJ_CHAL._STAMP2 where RMP._Status1 = 'Generated'

DECLARE queryNEXREM CURSOR FOR
SELECT _STAMP3 from NEX_REM
join RMP on RMP._STAMP2 = NEX_REM._STAMP2 where RMP._Status1 = 'Generated'

OPEN queryRMP
FETCH NEXT FROM queryRMP INTO @STAMP2, @RMP_ID

while @@FETCH_STATUS = 0
  BEGIN
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE RMP SET _ID = @result where @STAMP2 = RMP._STAMP2
  set @resultRMP = @result

  OPEN queryBUSSTRA
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE BUS_STRA
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = BUS_STRA._STAMP2
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  CLOSE queryBUSSTRA
  DEALLOCATE queryBUSSTRA

  OPEN queryINVSTRA
  FETCH NEXT FROM queryINVSTRA INTO @STAMP3_I
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE INV_STRA
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = INV_STRA._STAMP2
  FETCH NEXT FROM queryINVSTRA INTO @STAMP3_I
  CLOSE queryINVSTRA
  DEALLOCATE queryINVSTRA

  OPEN queryCOMCLIE
  FETCH NEXT FROM queryCOMCLIE INTO @STAMP3_C
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE COM_CLIE
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = COM_CLIE._STAMP2
  FETCH NEXT FROM queryCOMCLIE INTO @STAMP3_C
  CLOSE queryCOMCLIE
  DEALLOCATE queryCOMCLIE
 
  OPEN queryMAJCHAL
  FETCH NEXT FROM queryMAJCHAL INTO @STAMP3_M
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE MAJ_CHAL
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = MAJ_CHAL._STAMP2
  FETCH NEXT FROM queryMAJCHAL INTO @STAMP3_M
  CLOSE queryMAJCHAL
  DEALLOCATE queryMAJCHAL  

  OPEN queryNEXREM
  FETCH NEXT FROM queryNEXREM INTO @STAMP3_N
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE NEX_REM
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = NEX_REM._STAMP2
  FETCH NEXT FROM queryNEXREM INTO @STAMP3_N
  CLOSE queryNEXREM
  DEALLOCATE queryNEXREM
 
FETCH NEXT FROM queryRMP INTO @STAMP2
END
CLOSE queryRMP
DEALLOCATE queryRMP

----------------------------------------------------------------------------
I get these error msgs:
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'queryBUSSTRA' already exists.
Server: Msg 16915, Level 16, State 1, Line 26
A cursor with the name 'queryINVSTRA' already exists.
Server: Msg 16915, Level 16, State 1, Line 30
A cursor with the name 'queryCOMCLIE' already exists.
Server: Msg 16915, Level 16, State 1, Line 34
A cursor with the name 'queryMAJCHAL' already exists.
Server: Msg 16915, Level 16, State 1, Line 38
A cursor with the name 'queryNEXREM' already exists.

0
Vasi04
Asked:
Vasi04
2 Solutions
 
Brendt HessSenior DBACommented:
First, you need some BEGIN and END statements here.

OPEN queryRMP
FETCH NEXT FROM queryRMP INTO @STAMP2, @RMP_ID

while @@FETCH_STATUS = 0
  BEGIN
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE RMP SET _ID = @result where @STAMP2 = RMP._STAMP2
  set @resultRMP = @result

  DECLARE queryBUSSTRA CURSOR FOR
  SELECT _STAMP3 from BUS_STRA
  join RMP on RMP._STAMP2 = BUS_STRA._STAMP2 where RMP._Status1 = 'Generated'

  OPEN queryBUSSTRA
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  while @@FETCH_STATUS = 0
   BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE BUS_STRA
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where @STAMP2 = BUS_STRA._STAMP2
      FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
   END
  CLOSE queryBUSSTRA
  DEALLOCATE queryBUSSTRA

  DECLARE queryINVSTRA CURSOR FOR
  SELECT _STAMP3 from INV_STRA
  join RMP on RMP._STAMP2 = INV_STRA._STAMP2 where RMP._Status1 = 'Generated'

  OPEN queryINVSTRA
  FETCH NEXT FROM queryINVSTRA INTO @STAMP3_I
  while @@FETCH_STATUS = 0
  BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE INV_STRA
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where @STAMP2 = INV_STRA._STAMP2
      FETCH NEXT FROM queryINVSTRA INTO @STAMP3_I
  END
  CLOSE queryINVSTRA
  DEALLOCATE queryINVSTRA

  DECLARE queryCOMCLIE CURSOR FOR
  SELECT _STAMP3 from COM_CLIE
  join RMP on RMP._STAMP2 = COM_CLIE._STAMP2 where RMP._Status1 = 'Generated'

  OPEN queryCOMCLIE
  FETCH NEXT FROM queryCOMCLIE INTO @STAMP3_C
  while @@FETCH_STATUS = 0
  BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE COM_CLIE
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where @STAMP2 = COM_CLIE._STAMP2
      FETCH NEXT FROM queryCOMCLIE INTO @STAMP3_C
  END
  CLOSE queryCOMCLIE
  DEALLOCATE queryCOMCLIE

  DECLARE queryMAJCHAL CURSOR FOR
  SELECT _STAMP3 from MAJ_CHAL
  join RMP on RMP._STAMP2 = MAJ_CHAL._STAMP2 where RMP._Status1 = 'Generated'

  OPEN queryMAJCHAL
  FETCH NEXT FROM queryMAJCHAL INTO @STAMP3_M
  while @@FETCH_STATUS = 0
  BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE MAJ_CHAL
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where @STAMP2 = MAJ_CHAL._STAMP2
      FETCH NEXT FROM queryMAJCHAL INTO @STAMP3_M
  END
  CLOSE queryMAJCHAL
  DEALLOCATE queryMAJCHAL  

  DECLARE queryNEXREM CURSOR FOR
  SELECT _STAMP3 from NEX_REM
  join RMP on RMP._STAMP2 = NEX_REM._STAMP2 where RMP._Status1 = 'Generated'

  OPEN queryNEXREM
  FETCH NEXT FROM queryNEXREM INTO @STAMP3_N
  while @@FETCH_STATUS = 0
  BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE NEX_REM
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where @STAMP2 = NEX_REM._STAMP2
      FETCH NEXT FROM queryNEXREM INTO @STAMP3_N
  END
  CLOSE queryNEXREM
  DEALLOCATE queryNEXREM
 
  FETCH NEXT FROM queryRMP INTO @STAMP2
END
CLOSE queryRMP
DEALLOCATE queryRMP

------------------------------------

Also, before running this again, save the code, and close all of your connections to the server.  If you were running this in small sections in Query Analyzer, you may have opened the cursors without closing them

I am also pretty sure that you need to re-define the cursor after each deallocation.  So, move your DECLAREs into the loop, as above.

Finally, I really think that you need to re-think your methodology here.  This looks like something that should be doable in queries, or at least user-defined functions, rather than having to use cursors.
0
 
SashPCommented:
Hi Vasi04

You are missing BEGIN and END statements for you "while @@FETCH_STATUS = 0" loops

  OPEN queryBUSSTRA
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  while @@FETCH_STATUS = 0
  exec shortstamp @date, @hsecs, @result OUTPUT
  UPDATE BUS_STRA
  SET _RMP_ID = @RMP_ID
  ,_ID = @result
  where @STAMP2 = BUS_STRA._STAMP2
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  CLOSE queryBUSSTRA
  DEALLOCATE queryBUSSTRA


should perhaps be

  OPEN queryBUSSTRA
  FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  while @@FETCH_STATUS = 0
  BEGIN
    exec shortstamp @date, @hsecs, @result OUTPUT
    UPDATE BUS_STRA
    SET _RMP_ID = @RMP_ID
    ,_ID = @result
    where @STAMP2 = BUS_STRA._STAMP2
    FETCH NEXT FROM queryBUSSTRA INTO @STAMP3_B
  END
  CLOSE queryBUSSTRA
  DEALLOCATE queryBUSSTRA

Cheers Sash
0
 
LowfatspreadCommented:
as above
and put IN SOME ERROR CHECKING !!!!!!!!!

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Vasi04Author Commented:
Yeah, I have this problem with @result,
Actually I wanted to use insert into.... select....
but i wanted every newly added row to get unique @result. This is only possible with cursor method.  I cannot use user defined funtion as I have SQL 7.0
Any idea?
0
 
Vasi04Author Commented:
0
 
Brendt HessSenior DBACommented:
I notice that you don't actually use the information returned by the FETCH statement.  Are you just trying to update each record in the individual cursor with a distinct value returned by the shortstamp stored proc?  Assuming that each return is, in some way, unique, then you can rewrite your loops like this:

while @@FETCH_STATUS = 0
  BEGIN
  UPDATE RMP SET _ID = @result where @STAMP2 = RMP._STAMP2   -- May want to use WHERE CURRENT OF here
  set @resultRMP = @result

  DECLARE queryBUSSTRA CURSOR FOR
  SELECT _STAMP3 from BUS_STRA
  join RMP on RMP._STAMP2 = BUS_STRA._STAMP2 where RMP._Status1 = 'Generated' AND rmp._Stamp2 = @Stamp2

  OPEN queryBUSSTRA
  FETCH NEXT FROM queryBUSSTRA -- INTO @STAMP3_B -- since not used, do not need to retrieve data value
  while @@FETCH_STATUS = 0
   BEGIN
      exec shortstamp @date, @hsecs, @result OUTPUT
      UPDATE BUS_STRA
      SET _RMP_ID = @RMP_ID
      ,_ID = @result
      where CURRENT OF queryBUSSTRA   -- Updates only the record currently pointed to by the cursor
                                                                     ---@STAMP2 = BUS_STRA._STAMP2
      FETCH NEXT FROM queryBUSSTRA --INTO @STAMP3_B
   END
  CLOSE queryBUSSTRA
  DEALLOCATE queryBUSSTRA

...

etcetera.

Note the differences.  The Cursor definition query restricts based on the @Stamp2 value, limiting the number of entries examined.  The WHERE CURRENT OF option means that you will update *only* the record currently pointed to by your cursor.  I believe that this is what you are working towards.
0
 
Vasi04Author Commented:
Thank you hess, you have understood my problem.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now