INSERT INTO temporary table fails to create table if parameter used in query

[maxquar...@gmail.com]

Python 2.7 32-bit on Windows 8.1 64-bit connecting to SQL Server 2008R2 via pyodbc 3.0.7.

This works: curs.execute("select distinct lhbon_ordn into #temptab from lhbonus where lhbon_pkdt = 2014195")

This fails: curs.execute("select distinct lhbon_ordn into #temptab from lhbonus where lhbon_pkdt = ?", (2014195))

The statement itself appears to work but the temporary table is not created. An attempt to query it fails with "Invalid object name '#temptab'. (208)".

The parameter isn't human-generated (it's just a year and day-of-year) so I can substitute into the string and not use the parameter, but it would be interesting to know what's going on.

It works without the "into #temptab" clause just fine.

The login which runs the query doesn't have permission to create permanent tables - if I remove the # from the table name I get a "CREATE TABLE permission denied" error.

If I then grant permission to the login to create permanent tables, the query succeeds - though bizarrely the name of the created table is "tempdb" not "temptab".

So it looks like some interaction between the use of a parameter and the # character in the table name.

[Keith Erskine]

In SQL Server, parameterized queries are executed within their own session, hence (local) temporary tables are not visible to the rest of your code.  See here for some more info:

http://code.google.com/p/pyodbc/issues/detail?id=377

One solution is to write to a global temporary table instead (e.g. ##temptab).