Transaction not returning valid ResultSet

[GoogleCodeExporter]

What steps will reproduce the problem?
Simplifying the script involved to the pertinant steps:

cnxn1 = pyodbc.connect(DBServer.odbc_info, autocommit=True)
c1 = cnxn1.cursor()
insertSQL = 
"""BEGIN
  SET NOCOUNT ON;   
  declare @message varchar(300)
  BEGIN TRANSACTION insert_bill
  BEGIN TRY
    declare @newBillId int
    select @newBillId = MAX(bill_id)+1 from bill
    insert into bill (bill_id,updated,updated_by) 
    values (@newBillId,CURRENT_TIMESTAMP,'PythonTool') 
    insert into bill_item values (@newBillId,1,123.45)
    insert into bill_item values (@newBillId,2,678.90)
    COMMIT TRANSACTION insert_bill
    SELECT @message=@newBillId --as 'Message'
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION insert_bill
    SELECT @message='ERROR! '+ERROR_MESSAGE() --as 'Message'
  END CATCH
  select @message as 'message'       
END"""

SQLresult = c1.execute(insertBillSQL)
for row in SQLresult:
   print row.message

What is the expected output? What do you see instead?
Expected:
Transaction should _always_ return a single row with a single column called 
message. If the inserts were successful, this message will be the new bill_id 
used. If there were errors (ie PK or FK conflict or bad data type), the message 
will be the text of the error message.

Instead:
The when I try to access the rows of SQLresult, I get:
"pyodbc.ProgrammingError: No results.  Previous SQL was not a query."
This is unexpected because the transaction should always return 1 result row.

What version of the product are you using? On what operating system?
python 2.6 (32bit)
eclipse 3.4.2 
pyodbc 2.1.8 (for win32 2.6)
Win7 Pro (64bit)

Please provide any additional information below.
I am running thousands of these transactions, and most are fine. However, a 
sizable chunk (say 5-10%) are hitting this error.

More interestingly, the transaction is actually fully successful even when this 
occurs. I can look up the desire new data in the database, and it is fully 
intact. Moreover, copying the exact same query into MS SQL Studio execute 
exactly as expected, with the expected single row result.

This makes it very difficult for me to diagnose, because identical SQL works as 
expected most of the time, and it is even successful in these istances. I'm not 
sure why this error occurs.

While I can just catch the exception and move on (since they have been 
successful so far), I would like to know when the transaction has actually 
failed, and with this error I cannot be sure of that.

I'm happy to provide further diagnostics if you need them.

Thanks,
Brendan

Original issue reported on code.google.com by brendan....@gmail.com on 4 Mar 2011 at 9:34

[GoogleCodeExporter]

Forgot to say that the database server is: MS SQL Server 2008, Version 
10.0.4000.0 (SP2) Standard Edition (64-bit)

Original comment by brendan....@gmail.com on 4 Mar 2011 at 9:50

[GoogleCodeExporter]

I'd also like to add that I've tried this with autocommit=False. It did not 
seem impact the outcome.

Original comment by brendan....@gmail.com on 4 Mar 2011 at 10:09

[GoogleCodeExporter]

I noticed that the above test script contains a few errors (eg insertBillSQL is 
not defined) so I take it to be paraphrased rather than what was actually run.

Here is a complete standalone test script which actually works correctly for me 
(see attached).  Brendan does this work for you too?  If so then can you adjust 
it to reproduce your error?

Original comment by lukedell...@gmail.com on 7 Mar 2011 at 2:13

Attachments:

• pyodbc-161.py

[GoogleCodeExporter]

Whoops, sorry. Yes, I've stripped this longer query to the essentials and 
apparently changed the core SQL variable name in only one of its two places. In 
the actual script the string variable and execution command do indeed match. 
Sorry about that.

I will try the script you posted and report back.

Original comment by therequ...@gmail.com on 7 Mar 2011 at 4:01

[GoogleCodeExporter]

Comment #4 was me under another account my mistake.

I've run the posted script, and as expected it runs fine. I modified it to run 
many thousands of inserts, and they all run fine. I inserted a bad data type, 
and the expected "ERROR!" message was returned correctly.

In short, I have not been able to recreate the error in the standalone test.

My trouble stems from the fact that I don't understand what server-side 
behaviour is causing this result. Taking the exact same SQL which causes this 
error via pyodbc will run exactly as expected/intended in MS SQL Manager.

I have, however, identified a common thread between the statements that fail 
(involving a foreign key excluded from the scope of my example above). This 
leads me to believe that my server is doing something under these conditions 
which pyodbc does not know how to handle, but which is invisible via SQL 
Manager.

Tomorrow, I will test some of these transactions using a different connection 
from a C# project and see how it responds.

This common thread at least eliminates the relevance of the ~25% (after careful 
measurement) occurrence of this issue. That's just the appearance rate of these 
conditions in my source data.

I still think there is an issue to be concerned about, as some condition or 
action on my server is causing pyodbc to not handle the results of these 
queries correctly, despite my best effort to capture all errors.

I will update after further investigation.

Thanks for your help.

Original comment by brendan....@gmail.com on 7 Mar 2011 at 7:46

[GoogleCodeExporter]

Ok, I've identified the cause of the issue, and can reproduce it with a small 
modification to the standalone test script (attached).

The problem was that there was a "print" output statement in a trigger on one 
of my tables, only executed under the FK conditions mentioned above.

I have modified the standalone test script to create an analagous trigger on 
the bill table which does indeed cause the error I have been experiencing.

I am now able to resolve the issue for myself by disabling that (unnecessary) 
output, but I am happy to continue to help diagnose this issue to have it 
corrected.

Thanks,
Brendan

Original comment by brendan....@gmail.com on 7 Mar 2011 at 8:22

Attachments:

• pyodbc_issue_161_test.py

[GoogleCodeExporter]

I observe that in this last example there are *two* result sets returned by the 
server.  The first result set contains no data (as indicated by 
cursor.description being None, and by the exception you have observed if you 
try to read rows from the cursor).  You can then progress to the next result 
set using cursor.nextset(), and then the data you are after is available in 
this second result set.

I expect that the message from your print statement would be associated with 
the first result set, available via the SQLGetDiagRec function in the ODBC API 
(not exposed to python via pyodbc).  This is the same concept as what happens 
when you omit "SET NOCOUNT ON", in which case the server sends extra 
informational messages which in python look like extraneous empty result sets.

Really pyodbc is doing the right thing here.  It would be nice if pyodbc 
provided a way to read informational messages via SQLGetDiagRec, but otherwise 
it is faithfully returning the multiple result sets returned by the server.

Original comment by lukedell...@gmail.com on 9 Mar 2011 at 6:54

[GoogleCodeExporter]

I see. That is unexpected, and admittedly is not something I considered 
(obviously).

Yes, it would be helpful to be able to read that data, if only to confirm that 
no SQL errors occurred.

Is there any command that can be used like SET NOCOUNT ON to suppress these 
messages? I couldn't find reference to anything like that in my searching.

Thanks again for your help with this.
-Brendan

Original comment by brendan....@gmail.com on 9 Mar 2011 at 7:15

[GoogleCodeExporter]

Unfortunately there isn't that I know of.

I don't know who l...@deller.id.au is, but I am very grateful for the 
troubleshooting here...

Original comment by mkleehammer on 13 Sep 2011 at 11:20

• Changed state: NoFix