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', 1803.710000, '2013-12-10 00:00:00.000000', 'Bloomberg')
EXEC [dbo].spInsertBondTickerValues @rawTbl

EDIT 2 Here is the relevant Python code:

def execSQLwithCommit(self, sql):
    cursor = self.conn.cursor()
    cursor.execute(sql)
    self.conn.commit()

where the connection has been previously made via

self.conn = pyodbc.connect(app      = appName,
                           driver   = '{SQL Server Native client 11.0}',
                           server   = server,
                           database = db,
                           Trusted_Connection = 'yes')

Please update with the relevant python code where pyodbc.Cursor is instantiated and executed. — Bryan, Dec 10 '13 at 20:24
It looks like your SQL script lacks any semicolons. I wonder if it gets parsed at all. SQL Server console might be smart enough to parse it, but is driver equally smart? I also suspect that the script has three distinct statements, each possibly requiring a separate `.execute` call. — 9000, Dec 10 '13 at 20:37
@9000 When there is no foreign key error, the call inserts everything correctly. Don't think it is the semi-colons or multiple statements. Separating them may give a way to test, but I expect `pyodbc` to throw in error cases... — gt6989b, Dec 10 '13 at 21:00

score 13 · Accepted Answer · answered Dec 11 '13 at 08:03

13

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)')

answered Dec 11 '13 at 08:03

Gord Thompson

116,920
32
215
418

Thank you, will try to reproduce and if it works, will accept. Thank you so much for trying to help, I really appreciate it. – gt6989b Dec 12 '13 at 14:39
Your solution helped me out after hours of debugging so many years later. Thanks! But is there any explanation for why this works, or is needed? is this a bug with pyodbc? with SQL server? or is this expected behavior? – Cnoor0171 Jun 12 '20 at 16:53
@Cnoor0171 - It's just the way ODBC works when dealing with the TDS protocol. Without `SET NOCOUNT ON;` two "results" are returned: (1) the row count generated by the INSERT, and (2) the result of the EXEC, which is an error. The first result is not an error, and we are only able to "see" the error from the EXEC if we call [.nextset](https://github.com/mkleehammer/pyodbc/wiki/Cursor#nextset). With `SET NOCOUNT ON;` the error from the EXEC is the first (and only) result returned so pyodbc notices it right away. – Gord Thompson Jun 12 '20 at 17:11
@GordThompson so if there are multiple statements in the same sql string and any of them besides the first has an error, it won't be reported until `.nextset` is called? That's kind scary, but great to know. Thanks! – Cnoor0171 Jun 12 '20 at 20:08
8 years down the road, and this fix is still the solution to get multi-line statements to throw exceptions in Python. Thanks ! – Ture Friese Mar 17 '22 at 08:15

pyodbc does not throw on SQL Server error

1 Answers1

Linked

Related