title: pyodbc Error: 'HY090', Invalid string or buffer length (0)
body: <p><strong>Purpose:</strong></p>
  
  <p>Utilizing the <code>read_sql_query()</code> and <code>to_sql()</code> methods of <code>pandas</code>, the goal of my <code>python 3.7</code> script was to perform an ETL of multiple tables from one server to another by reading a <code>.sql</code> file. The connection parameter in both methods utilizes the <code>create_engine</code> module of <code>sqlalchemy</code>.</p>
  
  <p><strong>Error Raised:</strong></p>
  
  <p>After successfully extracting and loading the first set of tables+transactions, the 4th errors out.</p>
  
  <pre><code> sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')
  </code></pre>
  
  <p>Please see below for further details.</p>
  
  <p><strong>Procedure:</strong></p>
  
  <ul>
  <li>Each extracted table is written as a SQL transaction split by <code>;</code> in one SQL file.</li>
  </ul>
  
  <pre><code>
      'ExtractTables.sql'
  
      SET NOCOUNT ON
      SELECT 
          [ID1]       
      ,   [Name]          
      ,   [LastUpdated]   
      ,   [UpdatedBy]     
  
      INTO #table1
      FROM DB1.dbo.table1 
  
      SELECT * FROM #table1
  
      ;
  
      SET NOCOUNT ON
      SELECT 
          [ID1]
      ,   [ID2]
      ,   [Descr]
  
      INTO #table2 FROM DB1.dbo.table2
  
      SELECT * FROM #table2
  
  </code></pre>
  
  <ul>
  <li>ODBC parameters are setup for engines with the <code>create_engine</code> module for each server. Both servers are MS-SQL servers. From my research, I believe my error is coming from the <code>fast_executemany</code> parameter.</li>
  </ul>
  
  <pre><code>    'connection.py'
  
      import pyodbc
      import urllib
      from sqlalchemy import create_engine
  
      #Use trusted connection to connect to server. fast_executemany is mssql specific. Allows for large data loads.
  
      params_H = urllib.parse.quote_plus("DRIVER=ODBC Driver 17 for SQL Server;SERVER=SERVER1;DATABASE=DB1;Trusted_Connection=yes")
      engine_H = create_engine(f'mssql+pyodbc:///?odbc_connect={params_H}', fast_executemany=True)
  
  
      params_b = urllib.parse.quote_plus("DRIVER=ODBC Driver 17 for SQL Server;SERVER=SERVER2;DATABASE=DB2;Trusted_Connection=yes")
      engine_b= create_engine(f'mssql+pyodbc:///?odbc_connect={params_b}', fast_executemany=True)
  
  </code></pre>
  
  <ul>
  <li>Python Script to iterate over each command and use <code>read_sql_query()</code> for each command to extract, and <code>to_sql()</code> to load each table.</li>
  </ul>
  
  <pre><code>
      'LoadTables.py'
  
      import pandas as pd
      import conn
  
  
      def readSQLFile_makeTables(filename):
          # Open and read file
          open_file = open(filename, 'r')
          sql_file = open_file.read()
          open_file.close()
  
          #all SQL commands (split on ';')
          sql_commands = sql_file.split(';')
  
  
          # Execute every command from file
          sql_tables = ['stg_table1', 'stg_table2', 'stg_table3']
  
          i = 0
          for command in sql_commands:
  
              table = pd.read_sql_query(command, con=conn.engine_H)
              print(table)
              table.to_sql(sql_tables[i], con=conn.engine_b, chunksize=5000, index=False, if_exists='append')
              i += 1
  
          print('think this ran')
  
  
  </code></pre>
  
  <p><strong>Error:</strong></p>
  
  <p>For the purposes of StackOverflow I limited my sql code to 2 batches, however it has 4. The first 3 successfully pass the read and the write. However the fourth raises the error on the write. The primary difference in the error table batch versus the rest is its size (7 million rows x 8 columns) and the next highest being (1.5 million x 6 columns). </p>
  
  <p><strong>Troubleshooting:</strong></p>
  
  <p>All errors I've research on this topic point to it being an issue with the ODBC connection. Both servers are 64-bit, I'm utilizing pydobc 4.025, and I've tested only extracting fields of integer values. The first transactions being able to load successfully tells me the majority is working, but the last one has an issue preventing the load. I'm assuming size, which I believe is handled by <code>chunksize=5000</code> and <code>fast_executemany = true</code>, and the error pointing back to a binding parameter.</p>
  
  <p><a href="https://github.com/mkleehammer/pyodbc/issues/548" rel="nofollow noreferrer">https://github.com/mkleehammer/pyodbc/issues/548</a></p>
  
  <p><strong>Traceback:</strong></p>
  
  <pre><code>
      [6721864 rows x 8 columns]
      Traceback (most recent call last):
        File "C:\Users\x\AppData\Local\Continuum\anaconda3\envs\envname\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
          cursor, statement, parameters, context
        File "C:\Users\x\AppData\Local\Continuum\anaconda3\envs\envname\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
          cursor.execute(statement, parameters)
      pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')
  
      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 "PYTHON\Testing\ETL\LoadTables.py", line 21, in readSQLFile_makeTables
          table = pd.read_sql_query(command, con=conn.engine_H)
        File "\lib\site-packages\pandas\io\sql.py", line 314, in read_sql_query
          parse_dates=parse_dates, chunksize=chunksize)
        File "\lib\site-packages\pandas\io\sql.py", line 1063, in read_query
          result = self.execute(*args)
        File "\lib\site-packages\pandas\io\sql.py", line 954, in execute
          return self.connectable.execute(*args, **kwargs)
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 2166, in execute
          return connection.execute(statement, *multiparams, **params)
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute
          return self._execute_text(object_, multiparams, params)
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 1155, in _execute_text
          parameters,
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
          e, statement, parameters, cursor, context
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 1466, in _handle_dbapi_exception
          util.raise_from_cause(sqlalchemy_exception, exc_info)
        File "\lib\site-packages\sqlalchemy\util\compat.py", line 383, in raise_from_cause
          reraise(type(exception), exception, tb=exc_tb, cause=cause)
        File "\lib\site-packages\sqlalchemy\util\compat.py", line 128, in reraise
          raise value.with_traceback(tb)
        File "\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
          cursor, statement, parameters, context
        File "\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
          cursor.execute(statement, parameters)
      sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')
      (Background on this error at: http://sqlalche.me/e/dbapi)
  
  </code></pre>