A cursor with the name 'CursorName' already exists.

Question

A cursor with the name 'CursorName' already exists.

B Raj Dhakal

SSChasing Mays

Points: 602
More actions
May 25, 2012 at 12:55 am

#258633

Hi there,
I am working to upgrade SQL Server 2000 to SQL Server 2008R2. We have application developed on VB6. While running the application I encountered an error where it is trying to update a table. The table has update trigger on which there is a Cussor called UpdateLoop.
DECLARE UpdateLoop CURSOR FOR
The error returned was
Msg 16915, Level 16, State 1, Procedure JobUpdate, Line 16
A cursor with the name 'UpdateLoop' already exists.
The statement has been terminated.
It was working perfectly fine on SQL Server 2000, there are several triggers having Cussor in the database tables.
From my idea what I know is, having cursor in a trigger is not a good idea as there is no guarantee that the previous cursor has been de-allocated before the next one is declared. I don’t mind to change it to set-based T-SQL statements, but it might be a time consuming.
Just want to share the solution with those who are having the same trouble. After a long struggle, I finally found the solution. Change the scope of the cursor to local.
DECLARE UpdateLoop CURSOR LOCAL FOR
B Raj Dhakal

Viewing 7 posts - 1 through 6 (of 6 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Thanks for this, I wasn't aware that CURSORs could be specified in context.

If you had posed this as a problem I would have said to deallocate and close the cursor in the code immediately preceding your cursor definition.

I'll read up on the syntax today if I get time. Cheers.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

vinu512 SSCoach Points: 15729 More actions · Answer 2

Is the Cursor being Deallocated after it has been used in the Trigger??

EDIT: Sorry didn't read the original post.

But, on second thought I would still ask the above question.

You just changed the scope to Local.

It might not affect your application in the Global scope.

But, if the Cursor is not Deallocated and is Declared again in the Local scope then it might give you the same error again.

So, I would suggest that you check the code of the Trigger and check if the Cursor is being Deallocated or not.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Your assessment of cursors in a trigger being a bad idea is correct. Cursors can be really bad for performance and inside a trigger can be one of the worst places to use a trigger. I would suggest that any time you need to spend to make your trigger set based will reap rewards to your users.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

B Raj Dhakal SSChasing Mays Points: 602 More actions · Answer 4

Thank you all for your great comments.

Yes the CUSSOR was deallocated and closed. As I mentioned in my original post it is working fine with 2000. I had read somewhere that the structure of CURSOR has been modified in the later version. I haven't read the syntax very well. When I was playing with the code and thought to specify the contest as local and it worked.

Please post me back if you find it is not actually working because of the local context.

B Raj Dhakal