英文:
(pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')
问题
我正在使用Python脚本连接到数据库。
from sqlalchemy import text, create_engine
Server = ''
Database = ''
Driver = 'SQL Server'
Database_Con = f'mssql://@{Server}/{Database}?driver={Driver}'
#创建用于连接数据库的引擎
engine=create_engine(Database_Con)
con = engine.connect()
在插入到平面表时出现以下错误：
(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)
如果我修改驱动程序，它可以正常工作。我没有在服务器上升级或安装驱动程序的选项。我尝试将数据类型从varchar(max)修改为varchar(4000)。此外，平面表具有datetime列。
请问有关此错误的任何见解吗？
英文:
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?
答案1
得分: 1
这个问题中提到的查询是由SQLAlchemy生成的。它检查[INFORMATION_SCHEMA].[TABLES]来查看特定的表是否存在。
当使用陈旧的“SQL Server” ODBC驱动程序（SQLSRV32.DLL）时，该查询会失败，该驱动程序随Windows一起提供。该驱动程序可以追溯到SQL Server 2000时代，多年来已经被弃用。该查询适用于更近期的ODBC驱动程序，比如“ODBC Driver 17 for SQL Server”。
英文:
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".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论