A cursor with the name … already exists - SQL Serv

问题:

I have a trigger that is used to perform some auditing tasks when changes are made to a table. Within this trigger there is a WHILE loop, and in the loop there is a cursor that is declared, used, closed and then deallocated before the next iteration of the loop.

When I call a stored procedure that changes the table and in turn causes the trigger to run, and I do this from within the Management Studio, all works as expected.

However, when this stored procedure is called from within my Java web application, I get an error: "A cursor with the name ... already exists".

Is there a reason why this stored procedure would work when I execute it manually, and not work when run from the web application?

回答1:

It sounds like you might be using GLOBAL cursors, which could cause problems like this.

If you must use cursors:

If you can, use LOCAL cursors in all your code. e.g. declare cursor using the "LOCAL" keyword, like this:

DECLARE yourcursor CURSOR LOCAL ...

回答2:

You can also try this

IF CURSOR_STATUS('global', 'Cursorname') >= -1
BEGIN
    CLOSE Cursorname
    DEALLOCATE Cursorname
END

回答3:

Sounds a little like it is being called by multiple threads and therefore already exists when the second thread tries to use the name. You might try naming your cursor dynamically with a GUID in the name etc. I would not recommend this approach.

I suggest you remove the cursor from your trigger code if at all possible in favour of a set based approach. Overhead of using a cursor within the trigger must be pretty high.