How to resolve [Microsoft][ODBC Driver 17 for SQL Server]Invalid cursor state ?

Question

Hi,

I am experiencing issues with my database instance in your SQL service.

The program we are developing uses that database.

When I test it in a single thread, the ODBC driver works fine. However, when I use multiple threads, I occasionally encounter an 'Invalid cursor state' error message: '[Microsoft][ODBC Driver 17 for SQL Server] Invalid cursor state.'

I have thoroughly reviewed threads and C++ code and cannot identify any collisions or issues. The ODBC driver error occurs specifically when one thread attempts to execute a SQL procedure with a local cursor. To provide more context, I have included the structure of the procedure below, illustrating how the cursor is used.

Your assistance in resolving this issue would be greatly appreciated.

Maybe interesting facts:

• Each thread communicates with db almost constantly.
• The number of threads used ranges from 4 to 10;
• The program works fine with local Microsoft SQL Server 2019 and Amazon SQL service(which I have tested);
• I'm testing program on a virtual machine in Azure Cloud and I get the same error.
• I googled last 7 days and didn't find solution that helped.

Additional questions:

• Are there any limits regarding memory or execution time?
  • If yes, how can I change them and what will be optimal values?
  • Could this be the reason for the 'invalid cursor state'?
• On my local MSSQL server, I can find logs at: "\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log" - How can I access them in your service for my database instance?

SQL procedure structure:

create proc someProcedure @refNumber bigint
as

set nocount on

begin transaction

declare @value1 as int
declare @value2 as varchar(32)
declare @value3 as int

-- Lock, resource name is refNumber in string form (meaning, one lock per refNumber)
declare @result as int
declare @LockedResource as varchar(16)
set @LockedResource = str(@refNumber,16)

exec @result = sp_getapplock @Resource = @LockedResource, @LockMode = 'Exclusive'
if (@result < 0)
	set @value2 =  'ERROR: sp_getapplock failed with code:' + ltrim(str(@result,3))
else
begin
	declare uCursor cursor local for
	select value1, value2, value3 from Table where Id = @refNumber
	for update
	open uCursor

	fetch next from uCursor
	into @value1, @value2, @value3

	-- checking and changing values @value1, @value2, @value3
	-- uses too getdate(), datepart() functions
	
 	-- Update Table row
	if (substring(@value2, 1, 6) <> 'ERROR:')
	begin
		update Table set value1=@value1, value2=@value2, value3=@value3
		where current of uCursor
	end

	close uCursor
	deallocate uCursor
	
	exec sp_releaseapplock @Resource = @LockedResource
end

commit transaction

set nocount off

select @value2 as Result;

Thank you, Bojan

Answer 1

Check @@FETCH_STATUS after the FETCH statement in the proc to ensure that a row is available:

	if @@FETCH_STATTUS = 0 AND substring(@value2, 1, 6) <> 'ERROR:'

	begin

		update Table set value1=@value1, value2=@value2, value3=@value3

		where current of uCursor

	end

It seems the code in the question was obfuscated so it's not clear if a cursor is actually needed, especially for a single row. It's generally best to avoid cursors with SQL Server.

Answer 2

Note that the error message is from the ODBC driver, not from SQL Server, so it is a client-side cursor that is invalid state.

Then again, this may be due to some error in the procedure. I think it helps to implement proper exception handling in the procedure, possibly logging any errors to a table. And for that matter better error-handling in the ODBC code, so that SQL Servers are trapped accordingly. (You could investigate this in a test environment by injecting some error to the SQL processing.)

I wholeheartedly agree with Dan that there is all reason if there really is a need to use a cursor in the procedure at all.