| Issue 79: | SPROC does not return results. | |
| 1 person starred this issue and may be notified of changes. | Back to list |
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.
Jun 1, 2010
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
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
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; |
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))]