cursor with session

Question

cursor with session

thbaig

Hall of Fame

Points: 3003
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
......

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

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 Hall of Fame Points: 3003 More actions · Answer 3

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