title: Using Python to execute bcp to export query from remote server to local drive
body: <p>I have the below python group of statements. I am using dynamic built SQL statements using Python. The resulting <code>SELECT</code> statement is then used as the <code>queryout</code> statement in a BCP.</p>
  <p>My problem is that the query itself is too large, and BCP is failing to operate it. I have confirmed that BCP works using:</p>
  <pre><code>BCP &quot;Select * from &lt;&lt;DATABASE.dbo.TABLE&gt;&gt;&quot; queryout &quot;D:\data\test.csv&quot; -t^ -r '0x0A' 
      -U &lt;&lt;USER&gt;&gt; -P &lt;&lt;PASSWORD&gt;&gt; -S &quot;LIVE&quot; -c -C65001
  </code></pre>
  <p>But if the select statement returns a large set of data, the statement fails.  How can I counter this?  The table is large (over 100m records) and all I want to do is use the dynamic SQL to export it from a remote server to a local table.</p>
  <h3>Python Script:</h3>
  <pre><code>def getRoster(self):
      self.conn = pyodbc.connect(self.ConnStr)
      sql = r'SELECT * FROM &lt;&lt;DB&gt;&gt;.dbo.TableConfiguration'
      self.roster = pd.read_sql(sql,self.conn)
  
  def GenerateSQL(self, table):
      exportsql = 'select '
  
      columnsql = &quot;&quot;&quot;select
                      'CASE WHEN ISNULL('+COLUMN_NAME+', '''') = '''' THEN '''' ELSE '+COLUMN_NAME+' END AS '+UPPER(COLUMN_NAME)
                      from &lt;&lt;DB&gt;&gt;.INFORMATION_SCHEMA.COLUMNS
                      where TABLE_NAME = '%s'
                      order by ORDINAL_POSITION&quot;&quot;&quot; % table.tablename
      self.conn = pyodbc.connect(self.ConnStr)
      cursor = self.conn.cursor()
      cursor.execute(columnsql)
      exportsql += ', '.join([field[0] for field in cursor])
      exportsql += ' from {}.dbo.{}'.format(table.dbname, table.tablename)
      exportsql += ' {}'.format(table.Clause)
      return (exportsql)
  
  def ExportTables(self):
      now = datetime.now()
      self.getRoster()
      for row in self.roster.itertuples():
           SQL = self.GenerateSQL(row)
           self.filename = '{}_{}.csv'.format(row.tablename, now.strftime(&quot;%Y-%m-%d&quot;))
           command = 'BCP \&quot;{}\&quot; queryout \&quot;{}\&quot; -t|| -U &quot;&lt;&lt;USER&gt;&gt;&quot; -P &lt;&lt;PASSWORD&gt;&gt; -S &quot;LIVE&quot; -T -r 0x0a -c -C65001'.format(SQL, os.path.join(self.path, self.filename))
           print (command)
           subprocess.run(command)
  </code></pre>
  <p>when testing bcp with <code>SELECT 'test'</code> it comes back working ok.  but when the sql is generated, the sql works in SMSS, but in bcp, the error is:</p>
  <pre><code>SQLState = 28000, NativeError = 18452
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentic
  ation.
  </code></pre>