We help IT Professionals succeed at work.

Python with pyodbc and SQL Server

4,119 Views
Last Modified: 2013-12-29
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?
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.