client side cursors with ODBC

Question

Hello!

I just switched from Oracle to SQL-Server (2008) and now got some knew experiences. One Problem now is, that I got the message (I try to translate from German)

ODBC Error S1000:

[ODBC SQL Server Driver]The Connection is occupied with results from another hstmt

This occurs, when I try to use two cursors opened simultaneously - now I've read, that this could be caused in server-side cursors, which is the default.

My question: How (Where) can I specify, that I want to use client-side cursors? (I use the SQL-ODBC-API directly in C, like SQLConnect() and SQLExecute())

Thank you for any ideas and hints!

Kind regards,
RR

Answer 1

Two things here.

First, to use client side cursors, set the connection attribute prior to connecting thusly:

SQLSetConnectAttr( ..., SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, ...)

Second,

You may prefer to use the MARS option in the SQL Server ODBC Driver, which can be activated as described in this link:

http://technet.microsoft.com/en-us/library/ms345109(SQL.90).aspx

This will allow you to perform the same queries you describe above without getting the 'connection busy' error.

Please try the second option first, since I believe that's truly what you're looking for.

~Warren

P.S. - I noticed this article is pretty long, so in brief all you need to do to get the behavior you're seeking is to set this connection attribute prior to connecting :

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_INTEGER)

Answer 2

Two things here.

First, to use client side cursors, set the connection attribute prior to connecting thusly:

SQLSetConnectAttr( ..., SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, ...)

Second,

You may prefer to use the MARS option in the SQL Server ODBC Driver, which can be activated as described in this link:

http://technet.microsoft.com/en-us/library/ms345109(SQL.90).aspx

This will allow you to perform the same queries you describe above without getting the 'connection busy' error.

Please try the second option first, since I believe that's truly what you're looking for.

~Warren

P.S. - I noticed this article is pretty long, so in brief all you need to do to get the behavior you're seeking is to set this connection attribute prior to connecting :

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_INTEGER)

Thank you for your help - it lead me to the solution:

First of all: I included the ...SQL_COPT_SS_MARS_ENABLED... statement in my software - but it did not work.

Then I read in the article, that MARS should be enabled in the new drivers by default - so I recognized, that my driver was too old: I switched to "SQL Server Native Client 10.0" and removed the statement from above (as the article says - I thought it should be enabled by default) - but it did not work again.

So I included the ENABLE-statment again and used the new driver - heureca!

Now everything is working - thank you again,

RR

Answer 3

Glad I could help, but sorry you had such difficulty.

~Warren