ODBC Connection to MS SQL server that permits multiple open recordsets ?

I’ve had a fun week fighting with various odbc drivers

Anyone know the right ODBC connection string to use on Windows 10 that will make MARS (multiple active record sets) active when connecting to MS SQL Server 13

Connection string

SQL Server Strings

Never used that string myself

Guess I should have said I’ve already tried most of the ones posted there and so far a pile do not connect at all
Those that do connection do not enable MARS

// connects but does not enable MARS - local_db.DataSource = "Driver={SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword + ";MARS_Connection=yes;"

// connects but does not enable MARS - local_db.DataSource = "Driver={ODBC Driver 17 for SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword + ";Trusted_Connection=yes;MARS_Connection=yes;"

// connects but does not enable MARS - local_db.DataSource = "Driver={SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword
// connects but does not enable MARS - local_db.DataSource = "Driver={SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword + ";MARS_Connection=yes;"

// wont connect - local_db.DataSource = "Driver={SQL Server Native Client 11.0};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword
// wont connect - local_db.DataSource = "Driver={SQL Server Native Client 11.0};Server=" + m_Server + ";Database="+ m_dbName + ";"

Ok…forget I said anything :wink:

I never got it working either with odbc or mbs. I think the sql returns the data it’s just that there is no way to iterate the record sets.

Oops sorry. It’s Mars you want not returning multiple record sets from one query. I don’t recall that working either, though to be honest it’s years since I tried. I just use separate connection objects.

I think I’ve now tried 7 or 8 different drivers with different connection strings and 3 different plugins (although I’ve only used 1 API from the 3 possible in MBS so far)

The MSSQLDatabase plugin requires the sql native client which has been deprecated for years and MS does not recommend

MSOLEDBSQL is the one MS recommends but so far I have not found a means to connect using that one - yet
The strings on the site brian pointed out dont work with the ODBC plugin

MBS I had other issues which Christian is aware of - but remain unresolved so far

I would describe the experience so far as horrible

Funny thing is that using MBS on my mac once I got the connection string right EVERYTHING just worked pretty much first time (once I fixed syntax errors in my sql and my code)

yep, horrible is how i’d describe it too.

v13 is 2016 I think. I seem to recall that we discussed some way of setting sqlattributes prior to the connect but it never happened.
do you really need multiple recordsets on the same connection though? I found that just creating a new connection for every query worked ok. not the fastest, but it was reliable

will have to review the code and see if i can avoid multiple open recordsets etc

This works fine here connecting to an SQL server via ODBCDatabase with multiple open RowSets, removing the MARS_Connection=Yes causes a busy with another command error:

db.DataSource = "Driver={SQL Server Native Client 11.0};Server=192.168.0.1;Database=xyz;Uid=xyz;Pwd=xyz;MARS_Connection=Yes;"

I’ll have a shot with that
Native client isnt on MS recommends any more which is why really wanted MSOLEDBSQL to work - that one IS recommended

We had this with a client and they use OLEDB with multi connection:

db.Option("UseAPI") = "OLEDB" db.Option("SSPROP_INIT_MARSCONNECTION") = "VARIANT_TRUE"

This works here using an ODBCDatabase for the Microsoft ODBC Driver for SQL Server which is not deprecated (for anyone reading this later, you grab the architecture edition related to the OS, not the app):

db.DataSource = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.1;Database=" + d + ";Uid=" + un + ";Pwd=" + pw + ";MARS_Connection=Yes;"

[quote=479158:@JulianS]This works here using an ODBCDatabase for the Microsoft ODBC Driver for SQL Server which is not deprecated (for anyone reading this later, you grab the architecture edition related to the OS, not the app):

db.DataSource = "Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.1;Database=" + d + ";Uid=" + un + ";Pwd=" + pw + ";MARS_Connection=Yes;"

I’d swear I tried something like this on Windows already but can certainly retry

[quote=479155:@Christian Schmitz]We had this with a client and they use OLEDB with multi connection:

db.Option("UseAPI") = "OLEDB" db.Option("SSPROP_INIT_MARSCONNECTION") = "VARIANT_TRUE"[/quote]

have you solved the issue I reported with MBS SQLDatabaseMBS ?

Ah indeed I had tried this and I’m not certain why it doesnt connect
with

local_db.DataSource = "Driver={ODBC Driver 17 for SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";Uid=" + m_UserName + ";Pwd=" + localPassword + ";MARS_Connection=Yes;"[/code]
The error I get is 
[quote][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified[/quote]
yet if I change this to
[code]local_db.DataSource = "Driver={SQL Server};Server=" + m_Server + ";Database="+ m_dbName + ";UID=" + m_UserName + ";Pwd=" + localPassword + ";MARS_Connection=yes;"

it connect but does not support MARS …

the search continues

well now I know why the driver name difference
if I open the ODBC admin panel it lists the driver name as “SQL Server” not any of the others
its version 10.00.18362.01 which i’m not certain is “the very latest”
checking that option as well

well … one step forward two steps sideways … arg !

Not yet. Not a big priority right now.

Then please stop suggesting I use it when it doesnt work
When that issue is fixed I’ll be happy to try again