<p dir="auto"><strong>Migrated issue, originally created by Michael Schurter (<a href="https://github.com/schmichael">@schmichael</a>)</strong></p>
<p dir="auto">Support batch inserts using multiple values() calls like:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; tbl.insert().values(a=1, b=1).values(a=2, b=2)
INSERT INTO tbl (a, b) VALUES (1, 1) (2, 2);"><pre class="notranslate"><code class="notranslate">&gt;&gt;&gt; tbl.insert().values(a=1, b=1).values(a=2, b=2)
INSERT INTO tbl (a, b) VALUES (1, 1) (2, 2);
</code></pre></div>
<p dir="auto">This would make tbl.insert().values(...).values(...) conceptually similar to tbl.select().where(...).where(...) where multiple calls extend the existing query instead of replacing it.</p>
<p dir="auto"><strong>Describe the bug</strong></p>
<p dir="auto">Reflecting a table's columns using a combination of the Microsoft ODBC SQL Server drivers with SQL Server 2005 raises an exception.</p>
<p dir="auto"><strong>Expected behavior</strong><br>
I would expect to get a list of columns in the table with no exception (which happens with the FreeTDS driver).</p>
<p dir="auto"><strong>To Reproduce</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine, inspect

source_engine = create_engine(&quot;mssql+pyodbc:///?odbc_connect=[OMITTED]&quot;)

insp = inspect(source_engine)
insp.get_columns('TABLE NAME', schema='SCHEMA NAME')"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>, <span class="pl-s1">inspect</span>

<span class="pl-s1">source_engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"mssql+pyodbc:///?odbc_connect=[OMITTED]"</span>)

<span class="pl-s1">insp</span> <span class="pl-c1">=</span> <span class="pl-en">inspect</span>(<span class="pl-s1">source_engine</span>)
<span class="pl-s1">insp</span>.<span class="pl-en">get_columns</span>(<span class="pl-s">'TABLE NAME'</span>, <span class="pl-s1">schema</span><span class="pl-c1">=</span><span class="pl-s">'SCHEMA NAME'</span>)</pre></div>
<p dir="auto"><strong>Error</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1276, in _execute_context
    self.dialect.do_execute(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py&quot;, line 593, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', &quot;[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)&quot;)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py&quot;, line 390, in get_columns
    col_defs = self.dialect.get_columns(
  File &quot;&lt;string&gt;&quot;, line 2, in get_columns
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py&quot;, line 52, in cache
    ret = fn(self, con, *args, **kw)
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py&quot;, line 2195, in wrap
    return _switch_db(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py&quot;, line 2219, in _switch_db
    return fn(*arg, **kw)
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py&quot;, line 2741, in get_columns
    cursor = connection.execute(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 2235, in execute
    return connection.execute(statement, *multiparams, **params)
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1011, in execute
    return meth(self, multiparams, params)
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\sql\elements.py&quot;, line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1316, in _execute_context
    self._handle_dbapi_exception(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1510, in _handle_dbapi_exception
    util.raise_(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\util\compat.py&quot;, line 182, in raise_
    raise exception
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1276, in _execute_context
    self.dialect.do_execute(
  File &quot;C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py&quot;, line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', &quot;[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)&quot;)
[SQL: sp_columns @table_name = ?, @table_owner = ?]
[parameters: ('XXX', 'YYY')]
(Background on this error at: http://sqlalche.me/e/13/f405)"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py", line 390, in get_columns
    col_defs = self.dialect.get_columns(
  File "&lt;string&gt;", line 2, in get_columns
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py", line 52, in cache
    ret = fn(self, con, *args, **kw)
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2195, in wrap
    return _switch_db(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2219, in _switch_db
    return fn(*arg, **kw)
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2741, in get_columns
    cursor = connection.execute(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 2235, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: sp_columns @table_name = ?, @table_owner = ?]
[parameters: ('XXX', 'YYY')]
(Background on this error at: http://sqlalche.me/e/13/f405)
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: Windows 10 [&amp; Debian 10 in docker image on a RedHat 7 host]</li>
<li>Python: 3.9</li>
<li>SQLAlchemy: 1.3.21</li>
<li>Database: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)</li>
<li>DBAPI: pyodbc 4.0.30</li>
<li>Drivers:
<ul dir="auto">
<li>SQL Server 10.00.14393.00</li>
<li>SQL Server Native Client 11.0 2011.110.7462.06</li>
<li>ODBC Driver 17 for SQL Server 2017.174.01.01</li>
</ul>
</li>
</ul>
<p dir="auto"><strong>Additional context</strong><br>
This bug doesn't happen with the FreeTDS Linux driver, or with never versions of SQL Server.</p>
<p dir="auto">The way the expression is written matters. Using the implicit <code class="notranslate">EXEC</code> (as in the library)</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="rs = source_engine.execute(&quot;sp_columns @table_name = ?, @table_owner = ?&quot;, ('XXX', 'YYY'))"><pre class="notranslate"><span class="pl-s1">rs</span> <span class="pl-c1">=</span> <span class="pl-s1">source_engine</span>.<span class="pl-en">execute</span>(<span class="pl-s">"sp_columns @table_name = ?, @table_owner = ?"</span>, (<span class="pl-s">'XXX'</span>, <span class="pl-s">'YYY'</span>))</pre></div>
<p dir="auto">raises the exception seen above. However, using <code class="notranslate">EXEC</code> explicitly results in the correct behaviour (and should also be compatible with later SQL Server versions that do support the implicit form).</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="rs = source_engine.execute(&quot;EXEC sp_columns @table_name = ?, @table_owner = ?&quot;, ('XXX', 'YYY'))
rs.fetchone()"><pre class="notranslate"><span class="pl-s1">rs</span> <span class="pl-c1">=</span> <span class="pl-s1">source_engine</span>.<span class="pl-en">execute</span>(<span class="pl-s">"EXEC sp_columns @table_name = ?, @table_owner = ?"</span>, (<span class="pl-s">'XXX'</span>, <span class="pl-s">'YYY'</span>))
<span class="pl-s1">rs</span>.<span class="pl-en">fetchone</span>()</pre></div>
<p dir="auto"><strong>Have a nice day!</strong></p>