table variable creation and query fails

What steps will reproduce the problem?

In [22]:cx = 
pyodbc.connect('DSN=<dsn>;Description=<desc>;UID=<uid>;PWD=<pwd>;WSID=<wsid>;DAT
ABASE=<database>;QuotedId=Yes;AnsiNPW=No')

In [23]: cursor = cx.cursor()

In [24]: cursor.execute("""
   ....: DECLARE @LOG TABLE 
   ....:    (
   ....:    ID varchar(10)
   ....:    )
   ....:  
   ....: INSERT INTO @LOG (ID)
   ....: SELECT TOP 10 ID
   ....: FROM NAME
   ....: 
   ....: SELECT ID
   ....: FROM @LOG
   ....: 
   ....: """)
Out[24]: <pyodbc.Cursor object at 0x044EB1A8>

In [25]: cursor.fetchall()
------------------------------------------------------------
Traceback (most recent call last):
  File "<ipython console>", line 1, in <module>
ProgrammingError: No results.  Previous SQL was not a query.

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

In [26]: cursor.execute("""
   ....: SELECT TOP 10 ID
   ....: FROM NAME
   ....: """)
Out[26]: <pyodbc.Cursor object at 0x044EB1A8>

In [27]: cursor.fetchall()
Out[27]: 
[('1', ),
 ('10', ),
 ('100000', ),
 ('100001', ),
 ('100002', ),
 ('100003', ),
 ('100004', ),
 ('100005', ),
 ('100006', ),
 ('100007', )]

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

pyodbc-3.0.6.win32-py2.7.exe on Windows 7, querying MS SQL Server 2000

Please provide any additional information below.

I was able to successfully run the above query with Eclipse SQL Explorer, which 
also connects via ODBC. I believe this issue might be related to issue 215.

Original issue reported on code.google.com by dmlvia...@gmail.com on 19 Dec 2012 at 11:15

I think what is happening is that 3 SQL statements are being executed, so you 
should get 3 "results".  The first one isn't a select statement, so it returns 
the error you see.

The most obvious, portable solution is to use 3 calls to execute(), and it 
would also be my recommendation long term.

However, for cases where you really want this to work, you probably need to 
call cursor.next() to move from one result set to the next.  I'm not sure if 
you need to call it once or twice -- I know you'll get results for the INSERT 
statement.  In particular, you should get the number of rows inserted in 
cursor.rowcount.

You can also put SET NOCOUNT ON at the top to disable the generation of results 
for non-query operations.

I'm going to close this because I'm pretty confident that is the issue.  Please 
re-open if next() does not fix it.  Good luck.

Original comment by mkleehammer on 28 Mar 2013 at 11:58

Changed state: NoFix

methew / pyodbc

table variable creation and query fails #303