Using Python to execute bcp to export query from remote server to local drive

Question

I have the below python group of statements. I am using dynamic built SQL statements using Python. The resulting SELECT statement is then used as the queryout statement in a BCP.

My problem is that the query itself is too large, and BCP is failing to operate it. I have confirmed that BCP works using:

BCP "Select * from <<DATABASE.dbo.TABLE>>" queryout "D:\data\test.csv" -t^ -r '0x0A' 
    -U <<USER>> -P <<PASSWORD>> -S "LIVE" -c -C65001

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.

Python Script:

def getRoster(self):
    self.conn = pyodbc.connect(self.ConnStr)
    sql = r'SELECT * FROM <<DB>>.dbo.TableConfiguration'
    self.roster = pd.read_sql(sql,self.conn)

def GenerateSQL(self, table):
    exportsql = 'select '

    columnsql = """select
                    'CASE WHEN ISNULL('+COLUMN_NAME+', '''') = '''' THEN '''' ELSE '+COLUMN_NAME+' END AS '+UPPER(COLUMN_NAME)
                    from <<DB>>.INFORMATION_SCHEMA.COLUMNS
                    where TABLE_NAME = '%s'
                    order by ORDINAL_POSITION""" % 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("%Y-%m-%d"))
         command = 'BCP \"{}\" queryout \"{}\" -t|| -U "<<USER>>" -P <<PASSWORD>> -S "LIVE" -T -r 0x0a -c -C65001'.format(SQL, os.path.join(self.path, self.filename))
         print (command)
         subprocess.run(command)

when testing bcp with SELECT 'test' it comes back working ok. but when the sql is generated, the sql works in SMSS, but in bcp, the error is:

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.

Answer 1

Remove the -T parameter from your BCP command line and use SQL Server authentication.

bcp Utility - T

When the bcp utility is connecting to SQL Server with a trusted connection using integrated security, use the -T option (trusted connection) instead of the user name and password combination. When the bcp utility is connecting to SQL Database or SQL Data Warehouse, using Windows authentication or Azure Active Directory authentication is not supported. Use the -U and -P options.