SPROC does not return results.

[GoogleCodeExporter]

What steps will reproduce the problem?

#Run Sproc on MS-SQL Server and fetch results to Python 

import pyodbc
#connection string
cstring='DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;UID=myUID;PWD=myPWD'

#connection 
cnxn = pyodbc.connect(cstring,autocommit=True)

#cursor
cursor = cnxn.cursor()

#The SQL CMD
getSQLCMD="Execute spMovingAverage @weeks=8,@recency=0"

print "The Get SQL Command :", getSQLCMD

#Execute SPROC
cursor.execute(getSQLCMD)

#fetch records
allRecords=cursor.fetchall()

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

allrecords variable should have all the rows returned by the Sproc. Instead
I get this error: 

     15 cursor.execute(getSQLCMD)
     16 #fetch records
---> 17 allRecords=cursor.fetchall()
     18
     19

ProgrammingError: No results.  Previous SQL was not a query.
WARNING: Failure executing file: <Sproc_Test.py>

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

Python 2.5.4
MS SQL Server 2005

Please provide any additional information below.

Original issue reported on code.google.com by sankumar...@gmail.com on 16 Nov 2009 at 7:17

[GoogleCodeExporter]

I am unable to duplicate this with Python 2.6, SQLServer 2005.

Just tested and I was able to run a stored procedure.

Can you please try the following:
import pyodbc
cstring='DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb'
sqlcmd='exec sp_help \'sys.tables\''
conn=pyodbc.connect(cstring,)
csr=cn.cursor()
print "SQL is", sqlcmd
exec sp_help 'sys.tables'
csr.execute(sqlcmd)
recs=csr.fetchall()
print recs

It should give output similar to:
[(u'tables', u'sys', u'view', datetime.datetime(2007, 2, 10, 0, 23, 16, 57000))]

Original comment by todd.w.s...@gmail.com on 22 Dec 2009 at 8:39

[GoogleCodeExporter]

Hello,

We are facing the very same issue as the OP (connecting to SQL Server 2005 with 
the 
2008 native client driver). 

running the same command as the one provided in your response does work, but 
running 
a home made stored procedure does not. 

>>> c.execute('CREATE TABLE test_table (col1 int)')
>>> sp = '''CREATE PROCEDURE test_procedure2 AS
... BEGIN
...     insert into test_table(col1) values(42);
...     select * from test_table;
... END;'''
>>> c.execute(sp)
>>> c.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "logilab\database\sqlserver.py", line 135, in fetchall
    for row in self._cursor.fetchall():
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

Note that if we remove the INSERT statement from the procedure, everything 
works like 
a charm (except no side effect occurs which is the point of the procedure I'm 
trying 
to run...)

Running the above procedure from SQL Server Management studio works fine. 

Original comment by aurelien...@gmail.com on 1 Jun 2010 at 4:06

[GoogleCodeExporter]

This is a weird SQL Server issue. By default, SQL Server returns rowcounts as 
results 
for each insert and delete.  So in your case, you are getting *two* sets of 
results, 
(1) the number of rows inserted and (2) the actual rows.

See the discussion of SET NOCOUNT ON at 
http://code.google.com/p/pyodbc/wiki/StoredProcedures

Since you own the stored procedure, the easiest thing to do is to add SET 
NOCOUNT ON 
at the top of it.

Since I'm sure that is what it is, I'll close this, but please reopen if there 
is 
something else.

Original comment by mkleehammer on 1 Jun 2010 at 4:37

• Changed state: WontFix

[GoogleCodeExporter]

try at the begin of your procedure:

SET NOCOUNT ON;
SET NOCOUNT ON;
SET NOCOUNT ON;
SET NOCOUNT ON;
SET NOCOUNT ON;
SET NOCOUNT ON;
SET NOCOUNT ON;

Original comment by filosde...@gmail.com on 3 Mar 2011 at 8:17