Valid SQL returns error "No results. Previous SQL was not a query."

[GoogleCodeExporter]

This issue seems to be related to others that return the same error message, 
i.e., 
http://code.google.com/p/pyodbc/issues/detail?id=215
http://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!

Original issue reported on code.google.com by WSKi...@gmail.com on 8 Nov 2012 at 3:57

[GoogleCodeExporter]

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... 

Original comment by j...@duckworth.me on 5 Feb 2013 at 11:03

[GoogleCodeExporter]

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

Original comment by j...@duckworth.me on 5 Feb 2013 at 11:06

[GoogleCodeExporter]

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(). 

Original comment by mkleehammer on 29 Mar 2013 at 12:09

• Changed state: Hold