CODEWITHSUNDEEP
javasqlsql-servert-sqldatabase-cursor
t0mm0d
t0mm0d

Reputation: 192

A cursor with the name ... already exists - SQL Server 2008

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?

Upvotes: 12

Views: 27548

Answers (3)

Manjunath Patelappa
Manjunath Patelappa

Reputation: 77

You can also try this

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

Upvotes: 7

Moe Sisko
Moe Sisko

Reputation: 12005

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 ...

Upvotes: 31

catteralld
catteralld

Reputation: 101

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.

Upvotes: 4

Related Questions