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.

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...")