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