Turning on multiple result sets in an ODBC connection to SQL Server
- by David Griffiths
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.
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.
