odbc sqlserver notes

ODBC SQL Server notes

Setup

• install SQL Server Express 2017
  • picked Basic, no customization
    • Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;
• install SSMS 17.5
• turn on protocols

ODBC configuration

• Driver = ODBC Driver 13 for SQL Server or Driver = SQL Server Native Client 11.0
• Server = localhost\SQLEXPRESS

Issue: Several concurrent tests fail with Connection is busy with results for another command (HY000)

• need to set MARS_Connection = Yes option (can't use gui, must edit registry entry!)
• refs: https://stackoverflow.com/questions/9017264/why-only-some-users-get-the-error-connection-is-busy-with-results-for-another, https://github.com/catherinedevlin/ipython-sql/issues/54, https://serverfault.com/questions/302169/odbc-sql-server-how-do-i-turn-on-multiple-active-result-setsmars-for-a-syst

Issues

Remaining issues (test failures):

• Multi-statement string doesn't raise error.
• tinyint roundtrip -1 causes overflow error (SQLSTATE 22003)
• time roundtrip causes error: "unsupported type (typeid: -154)"
  • TODO: allow connection to specify blacklisted types; or fetch as string instead?
• timestamp test causes error: "Explicit conversion from data type datatime2 to timestamp is not allowed. (SQLSTATE: 22018)"

Notes

No parameter types (unknown)

Fetching NUMERIC/DECIMAL fields works using SQL_ARD_TYPE