Is ODBC from MARS but ADO/OLEDB from VENUS?

Question

Short question: I am finding I need to use MARS over ODBC but not over ADO/OLEDB, is that correct?

Longer explanation:

I just discovered my ODBC code (using "Driver={SQL Native Client}", MFC CDatabase code) needs to have MARS ("MARS_Connection=yes;") because, although I do not issue multiple SELECTs when opening a RecordSet, I do fetch a batch of rows, then need to open another RecordSet to issue a new SELECT, and then return to the first RecordSet for the next batch. Without the MARS I get ODBC error "Connection is busy with results for another command". All fair enough.

However, my code works identically with ADO/OLEDB ("Provider=SQLNCLI", #import msado15.dll) instead of ODBC. In the same situation, I have not had to specify "MarsConn=yes".

I am confused/surprised. Is this correct/expected, or am I missing something?

Answer 1

If anyone is interested, I found out what the problem/difference is. I had to revisit code because of a subtle problem in the ADO case, which turned out to be related.

If you need multiple concurrent RecordSets, it's easy with ODBC, because if you don't use MARS it just errors, as above.

With ADO/OLEDB, however, it's more subtle. When I do not use MARS, multiple RecordSets seem to just work OK. But, under the covers, what happens is that ADO auto-opens another new session for each one, but you don't know about it and cannot tell. And that turns out to be real slow, because each session requires a complete audit logon & off, and I'm creating, closing, and re-creating all the time.

So I put in "MARS Connection=True" for ADO and, lo & behold, it now behaves just like ODBC, re-using the existing connection instead of creating new ones.

So the moral is: you have to have MARS for ODBC, while ADO/OLEDB will allow multiple concurrent RecordSets without MARS by doing its own thing, but it may (well) not be what you want/is best.