jakesylvestre / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

SQL Server temporary tables not persistent when created with cursor parameters #377

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?

The following SQL statements are all run within the same database connection 
and cursor:

sql = r"SELECT X, Y, Z INTO #temp_table1 FROM mytable WHERE A = ?"
mycursor.execute(sql, 'banana')
sql = r"SELECT COUNT(*) FROM #temp_table1"
mycursor.execute(sql)  # fails with "Invalid object name '#temp_table1'"

whereas:

sql = r"SELECT X, Y, Z INTO #temp_table2 FROM mytable WHERE A = 'banana'"
mycursor.execute(sql)
sql = r"SELECT COUNT(*) FROM #temp_table2"
mycursor.execute(sql)  # succeeds

It appears the temporary table is immediately dropped if it is generated using 
parameterized SQL.

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

I would expect the temporary table to be persistent (until the database 
connection is dropped) regardless of what command was used to generate it.

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

This is using Python 3.4.0 and pyodbc 3.0.7, on a CentOS 6.5 box, with the 
Microsoft ODBC Driver 11 for SQL Server (and unixODBC 2.3.0), connecting to SQL 
Server 2008 R2 Enterprise Edition SP2.

Please provide any additional information below.

Original issue reported on code.google.com by toastie...@gmail.com on 11 Jul 2014 at 4:02

GoogleCodeExporter commented 9 years ago
On further investigation, this could be a SQL Server thing.  The following 
Stackoverflow question might be relevant here:

http://stackoverflow.com/questions/13420671/dynamically-created-temporary-table-
does-not-persist

Also, see the 'Remarks' section of the Microsoft documentation:
http://msdn.microsoft.com/en-us/library/ms188332%28v=sql.105%29.aspx
(third paragraph, starting "Because remote stored procedures...")

Original comment by toastie...@gmail.com on 11 Jul 2014 at 4:20