[DOC] Installing ODBC Driver 17 for SQL Server

[Eric-Liu-SANDAG]

Describe the documentation fix or enhancement

In Python code, in order to connect to the SQL Servers, we use the following style of connection string:

Estimates-Program/python/utils.py

Lines 60 to 67 in d85f276

	ESTIMATES_ENGINE = sql.create_engine(
	"mssql+pyodbc://@"
	+ _secrets["sql"]["estimates"]["server"]
	+ "/"
	+ _secrets["sql"]["estimates"]["database"]
	+ "?trusted_connection=yes&driver="
	+ "ODBC Driver 17 for SQL Server",
	fast_executemany=True,

Note, the connection string requires that ODBC Driver 17 for SQL Server is installed on the computer, which is not necessarily the case. This results in the error message:

{InterfaceError}InterfaceError("(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')")

The particular driver is also not installed by uv, despite pyodbc being specified in pyproject.toml. In order to solve this issue, I undertook the following steps which should be properly documented in the Setup section of README.md

First, check if ODBC Driver 17 for SQL Server is installed by running the program odbcad32.exe. It can be found using the Windows search bar. In the new screen, navigate to the Drivers page to see installed drivers

As you can see, ODBC Driver 17 for SQL Server is not installed. Note, ODBC Driver 18 for SQL Server does not work in the connection string as a different error will be raised. Follow the instructions from pyodbc to install the proper driver (Note, link is a permalink and may not be the most recent wiki revision)

Afterwards, you can see ODBC Driver 17 for SQL Server appears in the list of installed drivers (you may have to close and reopen the odbcad32.exe screen)

And code executes without the error message

[GregorSchroeder]

@Eric-Liu-SANDAG should we transition to using 18 instead of 17?

[Eric-Liu-SANDAG]

18 gave a different error message, something about the SQL Server version being incompatible IIRC. You can try it out pretty easily yourself by just changing the connection string