Link to home
Create AccountLog in
Avatar of Zberteoc
ZberteocFlag for Canada

asked on

Python with pyodbc and SQL Server

I am building a query batch of multiple queries to be executed from python in sql server like:

update tbl1 set... where...
insert tbl2 values...
update tbl3 set... where...

If the insert causes a PK violation the execution of the batch does not report anything. It is like the execution is successful. However the same query executed directly in SQL server will fail.

How can I catch the error?
ASKER CERTIFIED SOLUTION
Avatar of mankowitz
mankowitz
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Zberteoc

ASKER

I need to rectify my question a bit. My problem isn't that the error is not caught but the error message which is not explicit, doesn't really say what happened:

      ErrNo:      HY007
      ErrMsg:      [HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared (0) (SQLNumResultCols)


I also tied to wrap the SQL query batch in TRY ... CATCH and if there is error (and there is PK violation) I use RAISERROR SQL statement to bring it back in Python.

However in this case the RAISERROR is completely ignored by Python and it won't report error unless the first command in the query batch produces it!

I want to catch the real error message, even if produced in the middle of the batch and bring it back to python to log it.
I am not sure what happened but now I get the error after a combination of try..catch in python with try..catch with raiserror in SQL that I execute. Anyway I consider the question answered.