Issue 297: Valid SQL returns error "No results. Previous SQL was not a query."
Status:  Hold
Owner: ----
Reported by WSKi...@gmail.com, Nov 8, 2012
This issue seems to be related to others that return the same error message, i.e., 
https://code.google.com/p/pyodbc/issues/detail?id=215
https://code.google.com/p/pyodbc/issues/detail?id=243


What steps will reproduce the problem?

Simple example below:


# -*- coding: utf-8 -*-

import pyodbc

driver0 = '{SQL Server Native Client 10.0}'
server0 = '<servername>'
database0 = '<dbname>'

cnxn = pyodbc.connect('trusted_connection=yes',
                      driver=driver0, 
                      server=server0, 
                      database=database0)

cursor=cnxn.cursor()

noCount = """
SET NOCOUNT ON;

"""

simpleExampleQ = """
Declare @xytable Table(
	x int NOT NULL,
	y int);
Insert Into @xytable (x, y)
	Values (1, 2);
Insert Into @xytable (x, y)
	Values (3, 4);

Select * from @xytable
"""

try:
    print "without SET NOCOUNT ON:"
    cursor.execute(simpleExampleQ)
    rows=cursor.fetchall()

except Exception, err:
    print err, sys.exc_info()[0]


print
print "with SET NOCOUNT ON:"
cursor.execute(noCount + simpleExampleQ)
rows=cursor.fetchall()
print rows

cnxn.close()


What is the expected output? What do you see instead?

With the default setting of NOCOUNT OFF, fetchall raises an error.  But with NOCOUNT ON, the query is successful.

Output:

without SET NOCOUNT ON:
No results.  Previous SQL was not a query. <class 'pyodbc.ProgrammingError'>

with SET NOCOUNT ON:
[(1, 2), (3, 4)]



What version of the product are you using? On what operating system?

pyodbc version 3.0.6
Python version 2.7.3
Windows XP SP3
Microsoft SQL Server 2008 R2 (version 10.50.1777 RTM)


Please provide any additional information below.

Setting NOCOUNT On is an easy works-around that effectively solves the issue for me.  But this problem seems to have arisen for others and it seems worth fixing.

Thanks!
Feb 5, 2013
#1 j...@duckworth.me
I get the same issue using Ubuntu 12.04 with FreeTDS and unixodbc connecting to SQL server 2005. The work around works but it's really held me up for a long time figuring it out... 
Feb 5, 2013
#2 j...@duckworth.me
Probably need to check the resultset for DONE_IN_PROC... see here: http://ask.sqlservercentral.com/questions/1390/what-does-set-nocount-on-do.html

"A while back, some ODBC libraries got entirely confused by DONE_IN_PROC messages, so it was actually needed in order to make client code function correctly. I don't believe that is the case any more with current generation libraries."

...except pyodbc
Mar 28, 2013
Project Member #3 mkleehammer
Thanks.  It is often confusing, but I'm not sure it shouldn't work that way.  The INSERT statements generate a result (which is controlled by the database).

I'm going to keep this open and think about it.  The library does recognize that there are no rows (by asking for the number of columns or rows).  It could try going to the next result set automatically and if there isn't one, keep the previous rowcount.  This would work for people that use a single statement per execute(). 

Status: Hold