SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A cursor with the name 'CursorName' already exists.


A cursor with the name 'CursorName' already exists.

Author
Message
B Raj Dhakal
B Raj Dhakal
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 112
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
derek.colley
derek.colley
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7150 Visits: 603
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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

vinu512
vinu512
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13833 Visits: 1626
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 ;-)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250327 Visits: 19268
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
B Raj Dhakal
B Raj Dhakal
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 112
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
Paul White
Paul White
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132629 Visits: 11445
Your 2000 instance probably had the CURSOR_DEFAULT database option set to LOCAL (the default is GLOBAL).

See http://msdn.microsoft.com/en-us/library/ms190249(v=sql.105).aspx

Example change script:


ALTER DATABASE <database_name>
SET CURSOR_DEFAULT LOCAL;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
B Raj Dhakal
B Raj Dhakal
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 112
Thanks Paul,

I will check it today.

B Raj Dhakal
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search