Search code examples
sql-serverodbcpyodbcsql-server-mars

Pyodbc can't enable MARS even when SQL native client shows 'MARS: yes'

I am trying to connect to a SQL Server 2019 with MARS. First I have configured a system DSN in ODBC Data Sources, which shows MARS is enabled (ODBC driver 17). I have also tried SQL Server native client 11.0, which gives the same result.

enter image description here

Then I used this to create a connection in pyodbc.

cnxn = pyodbc.connect(DSN="83something",
                      UID="something",PWD="something")
how_many = cnxn.getinfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES)
print('how many ',how_many)

However I am still get 1, which indicates MARS is not enabled for Pyodbc. I have been struggling for a long time and any help is appreciated.

I have also tried MARS_Connection = yes in the connection string but don't think this will work on Windows.

Solution

  • I finally find setting a DSN with MARS on helps. Even though MAX concurrent shows 1, it actually can avoid 'cursor busy' or something similar.

    Please refer to the following steps:

    Fire up an Administrator command prompt (e.g. press the Start button, start typing “Command Prompt”, and when it appears, right-click it and choose “Run as Administrator”).

    Run the command: odbcconf /a {CONFIGSYSDSN "SQL Server Native Client 11.0" "DSN=MY_DSN|MARS_Connection=Yes"} (replacing the driver and DSN names with your own).

    You can re-run the ODBC configuration wizard, and it should now say Multiple Active Result Sets(MARS): YES where it said … NO before.

    Use CONFIGDSN instead of CONFIGSYSDSN if you are using a user DSN. Your changes should be visible if you reopen the configuration wizard again; the MARS option (which you can’t edit) should have changed from “No” to “Yes”.

    https://crateanon.readthedocs.io/en/latest/faq_troubleshooting.html#configure-odbc-mars