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
bulkcopy fails when trying to populate a temporary table unless
repro code
autocommit=False, #local temporary table
autocommit=False, ##global temporary table
autocommit=True, #local temporary table
autocommit=True, ##global temporary table
(works as expected)
additional info
I tried adding
UseFMTONLY=YesandColumnEncryption=Enabledas suggested inhttps://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: