Microsoft released a patch which we applied to our servers last night that seems to have broken ODBC connections to our SQL Server (2014). It appears that “ODBC Driver 17 for SQL Server” works still by testing through the ODBC Data Source Administrator. In my application, the message returned is “Client unable to establish connection”
The connections from my Windows 10 workstation continue to work but I’m running SQL Server driver version 10.00.19041.01 from 2019 but the server is running SQL Server driver 6.03.9600.17415. I don’t believer SQL Server driver is available for download anymore.
I’d like to force the connection to the ODBC Driver 17 but the MSSQLDatabase doesn’t have a Datasource property. Evidently the ODBCDatabase object has been deprecated? It is still in the help file and the docs, but I’m getting a “Can’t find a type with this name” when compiling in Xojo 2021r3.1 and Xojo 2020r2.
Has anyone else come up with a work around?
When I was talking about the ODBCDatabase not compiling, evidently that is affected because I copied out the ODBCDatabase.xojo_plugin from the Plugins folder. Interesting that it affects the code auto-complete. I’m going to try going the ODBCDatabase route.
Here are the steps I followed to get my apps up and working in connection to Microsoft SQL Server again:
- Install ODBC driver for SQL Server version 17
- Convert classes from MSSQLServer to ODBCDatabase
- Change all of the NEW commands from NEW MSSQLServer to NEW ODBCDatabase* In my connection methods, set the DriverName to use ODBC 17. ( mDB.DataSource = “Driver={ODBC Driver 17 for SQL Server};Server=” + App.cServer + “;Database=” + App.cDatabase + “;UID=” + App.cUser + “;PWD=MySecretDBPassword;”
)
- Change all prepare objects from MSSQLServerPreparedStatement to be ODBCPreparedStatement
- Change all bindings from MSSQL bindings to ODBC bindings (example: ODBCPreparedStatement.ODBC_TYPE_STRING)
That should take care of 99% of the issues.
3 Likes