cursor with session

• thbaig

  SSCrazy

  Points: 2984

  More actions

  July 18, 2012 at 12:06 pm

  #259580

  Hi,

  I am using cursor in my application for data replication.

  Sometimes I receive error that cursor is already open.

  I am opening ,closing and deallocating cursor properly.

  My question is , is there any way that cursor name may bind with session Id etc. So that for every new connection in parallel, I may not receive cursor already open error?

  I am declaring cursor in the following way

  DECLARE Color CURSOR FOR

  SELECT cc.colorid

  FROM dbo.tblColor cc

  ......

• Lowell

  SSC Guru

  Points: 323482

  More actions

  July 18, 2012 at 12:11 pm

  #1514425

  from a SQL server perspective, cursor declarations are session specific(unless you are using the DECLARE cursor_name myCursor GLOBAL syntax);

  so it's very likely that an error is occuring sometimes and the code is bailing out without closing and deallocating your cursor.

  if you track down that error, that would fix your issue right away.

  can you post your cursor code for a little peer review? we can probably help eliminiate the cursor completely, instead of jsut fixing the cursor.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  July 18, 2012 at 1:50 pm

  #1514526

  quite possible an error in the processing section causing the script to exit your cursor However the cursor is not closed and not deallocated within the session therefore the next execution you get your error, i have experienced this my self when working with cursors.

  ***The first step is always the hardest *******

• thbaig

  SSCrazy

  Points: 2984

  More actions

  July 19, 2012 at 9:27 am

  #1514937

  thank you all.

  problem resolved by adding Local to cursor declaration. I assume that by default cursor declaration is locl.

  DECLARE Color CURSOR Local FOR

  SELECT cc.colorid

  FROM dbo.tblColor cc