(pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')

Question

I am using python script to connect to the DB.

from sqlalchemy import text , create_engine
Server = ''
Database = ''
Driver = 'SQL Server'
Database_Con = f'mssql://@{Server}/{Database}?driver={Driver}'
#Create the engine to connect to the database
engine=create_engine(Database_Con)
con = engine.connect()

When inserting to a flat table it throws below error:

(pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')
[SQL: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE ([INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) OR [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max))) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))]
[parameters: ('BASE TABLE', 'VIEW', '', 'dbo')]
(Background on this error at: https://sqlalche.me/e/20/dbapi)

If I modify the driver, it works perfectly. I don't have the option of upgrading or installing the driver on the server. I tried modifying the datatype to varchar(4000) from varchar(max). also, the flat table has datetime columns.

Any insights on the error please?

Answer 1

The query cited in the question is generated by SQLAlchemy. It checks [INFORMATION_SCHEMA].[TABLES] to see if a particular table exists.

That query fails when using the ancient "SQL Server" ODBC driver (SQLSRV32.DLL) that ships with Windows. That driver dates back to the days of SQL Server 2000 and has been deprecated for many, many years. The query works for more recent versions of the ODBC driver like "ODBC Driver 17 for SQL Server".