search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.93k stars 561 forks source link

SQL Server warnings returned as errors #1035

Closed italanchan closed 2 years ago

italanchan commented 2 years ago

Please first make sure you have looked at:

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

Issue

import pyodbc

dbconn = ..... cnxn = pyodbc.connect(dbconn) cursor = cnxn.cursor()

sql = """ declare @tmp table (id int primary key with (ignore_dup_key = on)) insert into @tmp values (1), (2), (3), (4), (1), (2), (3), (4), """ cursor.execute(sql)

This raise error on python with message:

IntegrityError: ('23000', '[23000] [Microsoft] ODBC SQL Server Driver][SQL Server]Duplicate key was ignored. (3604) (SQLExecDirectW).

In fact, this is warning message and don't seem it as error in our case.

v-chojas commented 2 years ago

23000 is an error:

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes

Could you post an ODBC trace? I suspect the execute is actually returning an error and not just success with info.

gordthompson commented 2 years ago

I can reproduce the error using Driver=SQL Server but it does not occur with Driver=ODBC Driver 17 for SQL Server

italanchan commented 2 years ago

Yes, change to use 'Driver=ODBC Driver 17 for SQL Server' to avoid error,

paulstaab commented 2 years ago

But actual warnings like 01003 are currently raised as errors in pyodbc:

pyodbc.Error: ('01003', '[01003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation. (8153) (SQLExecDirectW)')
v-chojas commented 2 years ago

How else would you expect to see them?

paulstaab commented 2 years ago

I would expect that pyodbc issues a python warning with warnings.warn instead of raising an error.

My problem is that I am unsure if my query was successfully executed or not if I receive this exception. The SQL Server did execute it, but I do not know if e.g. pyodbc raised the error before committing the transaction (when using autocommit=True).