bulkcopy fails for temporary tables

[gordthompson]

bulkcopy fails when trying to populate a temporary table unless

• the table is a ##global temporary table, and
• autocommit is enabled

repro code

import mssql_python

cnxn = mssql_python.connect(
    "SERVER=192.168.0.199;"
    "DATABASE=test;"
    "UID=scott;PWD=tiger^5HHH;"
    "TrustServerCertificate=yes;"
)

# cnxn.setautocommit(True)
print(f"{cnxn.autocommit=}")

crsr = cnxn.cursor()

table_name = '#local_temp'
print(f"[{table_name=}]")

crsr.execute(f"CREATE TABLE {table_name} (id int, txt nvarchar(1))")

params = [(1, 'Ώ',), (2, 'π',), (3, 'α',)]
crsr.bulkcopy(table_name, params)

autocommit=False, #local temporary table

cnxn.autocommit=False
[table_name='#local_temp']
Traceback (most recent call last):
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\gdt_demo.py", line 22, in <module>
    crsr.bulkcopy(table_name, params)
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\cursor.py", line 2706, in bulkcopy
    raise type(e)(str(e)) from None
RuntimeError: Failed to retrieve destination metadata: Sql Error: 208: Class 16: State 0: Invalid object name '#local_temp'. on 5ec209db8c16 in  at line 1

autocommit=False, ##global temporary table

cnxn.autocommit=False
[table_name='##global_temp']
Traceback (most recent call last):
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\gdt_demo.py", line 22, in <module>
    crsr.bulkcopy(table_name, params)
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\cursor.py", line 2706, in bulkcopy
    raise type(e)(str(e)) from None
RuntimeError: Failed to retrieve destination metadata: Timeout Error: Elapsed: deadline has elapsed

autocommit=True, #local temporary table

cnxn.autocommit=True
[table_name='#local_temp']
Traceback (most recent call last):
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\gdt_demo.py", line 22, in <module>
    crsr.bulkcopy(table_name, params)
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\cursor.py", line 2706, in bulkcopy
    raise type(e)(str(e)) from None
RuntimeError: Failed to retrieve destination metadata: Sql Error: 208: Class 16: State 0: Invalid object name '#local_temp'. on 5ec209db8c16 in  at line 1

autocommit=True, ##global temporary table

(works as expected)

additional info

I tried adding UseFMTONLY=Yes and ColumnEncryption=Enabled as suggested in

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#---workaround-1-odbc-driver-17-for-sql-server-and-usefmtonlyyes-or-columnencryptionenabled

but they were rejected:

Traceback (most recent call last):
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\gdt_demo.py", line 3, in <module>
    cnxn = mssql_python.connect(
        "SERVER=192.168.0.199;"
    ...<3 lines>...
        "ColumnEncryption=Enabled;"
    )
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\db_connection.py", line 46, in connect
    conn = Connection(
        connection_str, autocommit=autocommit, attrs_before=attrs_before, timeout=timeout, **kwargs
    )
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\connection.py", line 240, in __init__
    self.connection_str = self._construct_connection_string(connection_str, **kwargs)
                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\connection.py", line 363, in _construct_connection_string
    parsed_params = parser._parse(connection_str)
  File "C:\Users\gord\PycharmProjects\mssql_python_demo\.venv\Lib\site-packages\mssql_python\connection_string_parser.py", line 271, in _parse
    raise ConnectionStringParseError(errors)
mssql_python.exceptions.ConnectionStringParseError: Connection string parsing failed:
  Unknown keyword 'columnencryption' is not recognized

[github-actions]

Hi Gord Thompson (@gordthompson), thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[bewithgaurav]

Hi Gord Thompson (@gordthompson) - thanks a lot for raising this issue & sharing all the details, we will be triaging this further and get back with updates this week.

[bewithgaurav]

Gord Thompson (@gordthompson) - This is a known limitation of the current bulkcopy design - for temptables with a single #, the connection for creating the temp table and the bulkcopy connection need to be shared (right now we're spinning up a new conn for bulkcopy)
referencing conversation from earlier GH Discussion #414 (link to the comments)
We will keep this as an enhancement request & keep this issue open for tracking - marking this as feature/enhancement and triage done
Thanks a lot for raising this! :)

cc: Saurabh Singh (@saurabh500)