sp_cursoropen returns empty result set when the query works fine

Question

While migrating from Advantage database to SQL Server using OLEDB providers, a part of my application stopped working. After investigation I have found that the application freezes when I try to iterate using a cursor. Using SQL profiler I found the exact moment when that happens. I think that the problem is that the cursor returns an empty result set, even though the query that is inside the cursor works fine on its own and returns the needed data. Here is what I got from profiler (with a slightly simplified query):

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT DT,MONTHINDEX,CUSIP_ID from src_trading_price_t',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

And here is the output:

I don't really understand what's going on.

Thank you in advance!

Answer 1

After spending a lot of time on investigating the issue, I have discovered a new tool - the Activity Monitor tool inside the SQL Server Management Studio.

It allowed me to see that my cursor query was blocked by a different, in fact the main global connection of my application. More specific, the query was waiting on a resource (I guess one of the tables) to become free.

I changed the connection of the cursor when it opens to the global one instead of creating its own (inspiration), and everything started working:

// specify the connection to use instead of creating a dedicated one
m_pSelect->PutRefActiveConnection(g_databaseConnection.m_database);

// use vtMissing since we specify the connection separately
m_pSelect->Open((LPCSTR)csSql, vtMissing, adOpenForwardOnly, adLockOptimistic, adCmdText);