1

I'm creating a #Temp table within this connection, then trying to convert that to a user defined table type and pass it to a stored procedure. this works fine in Microsoft SQL Server Management Studio, but i can't get it to work in python using pyodbc.

SET NOCOUNT ON;
USE TestDB
DECLARE @Temp as dbo.TempType;
INSERT INTO @Temp SELECT * FROM #Temp;
SELECT * FROM @Temp;

(In the actual code, the select statement at the end will be the stored procedure but in testing I'm trying to get this to work). Trying to do fetchall on the cursor returns:

Traceback (most recent call last):
  File "C:\Anaconda\lib\site-packages\IPython\core\interactiveshell.py", line 2883, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-237-dfc0750240b3>", line 1, in <module>
    dt = cur.fetchall()
ProgrammingError: No results.  Previous SQL was not a query.

Within the same connection I can SELECT * FROM #Temp just fine -- but converting to table type doesn't seem to allow me to select. Any ideas ?

NikT
  • 1,590
  • 2
  • 16
  • 29
  • I don't use pyodbc all that much, but one possible reason is that you need to do a `.commit()` on the connection after the INSERT statement before you can do the SELECT statement. – Raceyman Mar 04 '15 at 19:24
  • 1
    I am unable to recreate your issue. I have pasted my test code [here](http://pastebin.com/hAQetHsC) if you want to try it for yourself. – Gord Thompson Mar 05 '15 at 19:28
  • i didn't know i could use the #Temp table in the SP if it was created outside of the SP -- for now i got rid of the table type completely since the only reason i needed it (or thought so) was to pass the temp table to the SP. I think that what you did with declaring the @Temp inside the SP and filling it would work for me which I'll try anyway next week since right now the server is acting up -- using the #Temp inside the SP works though so I'm set -- thanks ! – NikT Mar 06 '15 at 14:08

1 Answers1

2

Not sure if this helps, but it solved my problem when using iPython notebooks and pyodbc with #temp tables:

import pyodbc
con = pyodbc.connect('Driver={SQL Server Native Client 10.0};Server=somedatabaseserver.com;Database=SomeDB;Trusted_Connection=yes;')
cur = con.cursor()

query1 = """SELECT * into #temp FROM [SomeDB].[SomeTable]"""
query2 = """SELECT * FROM #temp"""

cur.execute(query1)
cur.commit()
cur.execute(query2).fetchall()

The trick here is to commit the into #temp query and then execute work against it.

Andres
  • 5,002
  • 6
  • 31
  • 34
  • Not sure if you will see my comment. But I had a situation where I had to create 2+ local temp tables. It seems that the commit method only works if there is only one local temp table. When there are multiple, it seems that commit() will only work if I change the subsequent local temp tables to global ones. I wonder if you know why that is the case. My question is also related to this post: http://stackoverflow.com/questions/37863125/pyodbc-temp-tables-autocommit – Alex Aug 14 '16 at 01:06