Turning on multiple result sets in an ODBC connection to SQL Server

Question

I have an application that originally needed to connect to Sybase (via ODBC), but I've needed to add the ability to connect to SQL Server as well. As ODBC should be able to handle both, I thought I was in a good position.

Unfort, SQL Server will not let me, by default, nest ODBC commands and ODBCDataReaders - it complains the connection is busy (Connection is busy with results for another command).

I know that I had to specify that multiple active result sets (MARS) were allowed in similar circumstances when connecting to SQL Server via a native driver, so I thought it wouldn't be an issue.

The DSN wizard has no entr y when creating a SystemDSN.

Some people have provided registry hacks to get around this, but this did not work (add a MARS_Connection with a value of Yes to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\system-dsn-name).

Another suggestion was to create a file-dsn, and add "MARS_Connection=YES" to that. Didn't work.

Finally, a DSN-less connection string. I've tried this one (using MultipleActiveResultSets - same variable as a Sql Server connection would use),

"Driver={SQL Native Client};Server=xxx.xxx.xxx.xxx;Database=someDB;Uid=u;Pwd=p;MultipleActiveResultSets=True;"

and this one:

"Driver={SQL Native Client};Server=192.168.75.33\\ARIA;Database=Aria;Uid=sa;Pwd=service;MARS_Connection=YES;"

I have checked the various connection-string sites - they all suggest what I've already tried.

I should state that I've tried both the SQL Server driver, and the SQL Server native driver...

Answer 1

According to the SNI documentation on Using Multiple Active Result Sets (MARS):

The SQL Server Native Client ODBC driver supports MARS through additions to the SQLSetConnectAttr and SQLGetConnectAttr functions. SQL_COPT_SS_MARS_ENABLED has been added to accept either SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO, with SQL_MARS_ENABLED_NO being the default. In addition, a new connection string keyword, Mars_Connection, as been added. It accepts "yes" or "no" values; "no" is the default.

Make sure your client loads the right drivers, use Mars_Connection=yes, and validate in the app by checking SQL_COPT_SS_MARS_ENABLED on SQLGetConnectAttr.