Closed GoogleCodeExporter closed 9 years ago
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
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
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:
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
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
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:
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
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
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
Original issue reported on code.google.com by
brendan....@gmail.com
on 4 Mar 2011 at 9:34