Issue 79: SPROC does not return results.
Status:  WontFix
Owner: ----
Closed:  Jun 2010
Reported by sankumar...@gmail.com, Nov 16, 2009
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.


Dec 22, 2009
#1 todd.w.s...@gmail.com
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))]

Jun 1, 2010
#2 aurelien...@gmail.com
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. 
Jun 1, 2010
Project Member #3 mkleehammer
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 
https://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.
Status: WontFix
Mar 3, 2011
#4 filosde...@gmail.com
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;