pyodbc does not throw on SQL Server error

Question

I am trying to use `pyodbc` (with Python 2.7) to call a stored procedure to insert records into a SQL Server 2012 table. I am passing a temporary table. I dumped out my sql and when executed through the SQL Server Management console, it generated the following Foreign Key error: ``` Msg 547, Level 16, State 0, Procedure spInsertBondTickerValues, Line 26 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__BondTickerValue__756D6ECB". The conflict occurred in database "QuantDev", table "dbo.Tickers". The statement has been terminated. ``` However, `pyodbc` did not raise an exception. How would I test the resulting cursor or connection to know that a problem occurred, and how do I get the error message? Thank you very much. EDIT Here is the full sql text: ``` DECLARE @rawTbl [dbo].TickerValueTableType INSERT INTO @rawTbl (Ticker, BBName, LastValue, ValueTime, SourceDescr) VALUES ('IBM', 'Equity', 179.230000, '2013-11-01 00:00:00.000000', 'Bloomberg'), ('SPX', 'Index', 180…

Accepted Answer

I was able to recreate your issue using the following code, which fails silently:

```
import pyodbc
cnxn = pyodbc.connect('DSN=myDb;')
cursor = cnxn.cursor()
sql = """   
DECLARE @rawTbl dbo.ClientAddressInputType;
INSERT INTO @rawTbl (ClientID, Addr1) VALUES 
(2, 'higgy'), 
(3, 'jiggy'); 
EXEC dbo.AddClientAddress @rawTbl
"""
cursor.execute(sql)
cursor.commit()
cnxn.close()
```

However, I can get the code to throw the appropriate `IntegrityError` exception by simply adding `SET NOCOUNT ON;` at the beginning of the `sql` string:

```
import pyodbc
cnxn = pyodbc.connect('DSN=myDb;')
cursor = cnxn.cursor()
sql = """   
SET NOCOUNT ON;
DECLARE @rawTbl dbo.ClientAddressInputType;
INSERT INTO @rawTbl (ClientID, Addr1) VALUES 
(2, 'higgy'), 
(3, 'jiggy'); 
EXEC dbo.AddClientAddress @rawTbl
"""
cursor.execute(sql)
cursor.commit()
cnxn.close()
```

which results in

```
Traceback (most recent call last):
  File "C:\Users\Gord\Desktop\pyOdbc.py", line 12, in <module>
    cursor.execute(sql)
IntegrityError: ('23000', '[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ClientAddresses_Clients". The conflict occurred in database "myDb", table "dbo.Clients", column \'ClientID\'. (547) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. (3621)')
```

pyodbc does not throw on SQL Server error

Solution

Problem