2

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?

SC_Python
  • 17
  • 3
  • Why are you casting your parameters to a `MAX` in your query? Objects names are a `sysname` (a synonym for `nvarchar(128) NOT NULL`); there's no need to define your parameters as *likely* to have a value **over** *4,000* characters. Also [3+ part naming on Columns will be Deprecated](https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) and should be avoided. You are far better off aliasing your objects in the `FROM` and using those to qualify your columns. – Thom A Jul 17 '23 at 16:21
  • @ThomA, i'm guessing it's probably alchemy that does that. For OP: why not just skip alchemy and create populate your data by hand – siggemannen Jul 17 '23 at 16:36
  • I hope it's not, @siggemannen , that might force an implicit convert on the column on the table and result in a scan. If it *is* that's terrible implementation by SQLAlchemy. – Thom A Jul 17 '23 at 16:38
  • Yeah, but they are reading table metadata, hopefully the OP doesn't have millions of rows there. Plus, it seems they're sitting on a rather old version :) – siggemannen Jul 17 '23 at 16:43
  • @ThomA : I am not casting anywhere in my query. I have created the flat tables ahead and appending the data each time the script is run Code: Facility_df.to_sql('tablename',con=engine,schema=None,if_exists='append',index=False,index_label=None,dtype=None,method=None) – SC_Python Jul 17 '23 at 17:47
  • @siggemannen : I didn't get you. I am calling the restapi and getting the data. Its a survey data and here are 100's of columns in the table. Do you mind showing to skip alchemy and create populate your data by hand. Yes, we have old version of driver installed – SC_Python Jul 17 '23 at 17:51
  • *"I am not casting anywhere in my query."* Check again, you have multiple `CAST(? AS NVARCHAR(max))` expressions. – Thom A Jul 17 '23 at 17:59
  • We were discussing the error message you attached, it contained some bad sql. Regarding raw sql, you could use raw python sql stuff with cursors etc and just running executemany on tuples. Something like: https://stackoverflow.com/questions/29638136/how-to-speed-up-bulk-insert-to-ms-sql-server-using-pyodbc – siggemannen Jul 17 '23 at 18:11
  • @Thom A: The error shows nvarchar(max) expressions. But the python script doesnt have any conversions. – SC_Python Jul 17 '23 at 19:15

1 Answers1

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".

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418