My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 377: SQL Server temporary tables not persistent when created with cursor parameters
1 person starred this issue and may be notified of changes. Back to list
Status:  New
Owner:  ----


 
Reported by toastie...@gmail.com, Jul 11, 2014
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.


Jul 11, 2014
#1 toastie...@gmail.com
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...")


Powered by Google Project Hosting