ODBC Execute/Fetch of SQL 2005 stored procedure result set cannot use a table @variable

Question

I'm using ODBC and C++ against SQL Server 2005 (native client).
I have the following simple test stored procedure that returns two rows of constant values:

CREATE PROCEDURE usp_testme AS BEGIN  
   declare @details table( one int, two int, three int, four int )  
   insert @details SELECT 1 one, 2 two, 3 three, 4 four UNION SELECT 5, 6, 7, 8  
   select one, two, three, four from @details  
END  

ODBC Code looks like this (simplified):

SQLExecDirect( m_hstmt, (SQLCHAR *)"{CALL usp_testme}", SQL_NTS );
SQLFetch( m_hstmt );   

The SQLExecDirect() call returns the following information:

INFO: 16954 : 01000 : [Microsoft etc]Executing SQL directly; no cursor  

and the SQLFetch() call then gives:

INFO: 0 : 24000 : [Microsoft etc]Invalid cursor state  

What the ODBC reference documentation tells me is that I dont have a result set.
So I change then stored procedure to the following instead (e.g. not using a table variable):

CREATE PROCEDURE usp_testme AS BEGIN  
 SELECT 1 one, 2 two, 3 three, 4 four UNION SELECT 5, 6, 7, 8  
END  

and voila, it works. Of course this is a simple test procedure, the real SQL I need executed is more complex and requires the table variable, no need to go into that. But why cannot an (ODBC) cursor be created against the result set from the first SP?

Does anyone know how to get this to work? Btw, i'm asserting ODBC v3 on the environment handle. I've tried different cursor attributes on the connection handle but to no avail.

Help is greatly appreciated!

Answer 1

The problem was the scrollable cursor setting on the statement, changing to a nonscrollable cursor solved the problem.