Error 16955 – Could not create an acceptable cursor . Dynamic cursors and DISTINCT KEYWORD

02 June,2015 by Jack Vamvas

Question: I’m getting an error when issuing some t-sql statements . The error details are :

 

Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.
Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1
The cursor was not declared.

 

The code causing the error code

 

declare @p1 int
set @p1=NULL
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=NULL
exec sp_cursoropen @p1 output,N'SELECT DISTINCT  itemid         FROM ShopItems          Inner Join Categories  on itemid =categoryid’ ,@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

 

What is causing this error and how can I fix this error?

 

Answer:  You are looking at an example of a dynamic cursor.

The @p3 parameter represents the Scroll option and @p4 parameter represents the Concurrency control option

 

 select * from sys.dm_exec_cursors(0)  will return information about the cursor

 Read more on how to Troubleshoot open cursors with sys.dm_exec_cursors

An initial  observation is if you take DISTINCT keyword  out of the sql statement and run the original code , the error disappears. Why does taking the DISTINCT keyword out of the statement fix the issue?

As a cursor type is not specified you will receive a dynamic cursor. This means that every  FETCH evaluates which row to get.  Adding the DISTINCT keyword  causes an error.  SQL Server will use a static cursor, which copies the result set to a table. The cursor is served from this TEMPDB table.

Adding the DISTINCT keyword means there isn’t direct contact with the temp table and the base table. The DISTINCT keyword means the values are fetched and placed in the TEMPDB table .Any operation on the CURSOR means the data is fetched ONLY from the TEMPDB.

Changing  the @p3 parameter value to STATIC  (0x0008)   and the @p4 parameter value to READ_ONLY (0x0001) will fix the issue.

The same error appears if you use GROUP BY

Read More on  TempDB and Cursors

List current SQL Server tempdb statements - SQL Server DBA

Performance Myth : Replacing a CURSOR with a WHILE LOOP and ...

SQL Server – Current queries executing on SQL Server

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Error 16955 – Could not create an acceptable cursor . Dynamic cursors and DISTINCT KEYWORD


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer