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

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