0

I am trying to run BCP commands on a remote server using python. I need the data, as well as the xml format file for each table. Since my first table query still fails, i have yet to try the format command.

import pyodbc
import sys
import subprocess
import os
import bcp

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=10.100.100.10;'
                      'Database=CompanyDatabase;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
command = 'bcp.exe CompanyDatabase.dbo.AnyTableName out D:\PROD\BCPLIST\CompanyDatabase_AnyTableName.csv -c -t "|C^|" -r "|R^|" -T -S 10.10.100.100.10'
cursor.execute(command)

Executing the above script, always produces the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '.'. (102) (SQLExecDirectW)")

I'm able to send the command in a batch script and it works fine, but my superiors would really prefer i use python for this. The executing and remote servers are trusted within the network, so the user/password parameters are omitted.

The following working batch script that loops thru a textfile of tablenames, running the below commands for each one. %%x = the name of a table in the database, with delimiters of rows |R^| and columns |C^|

bcp.exe CompanyDatabase.dbo.%%x out D:\PROD\BCPLIST\CompanyDatabase_%%x.csv -c -t "|C^|" -r "|R^|" -T -S 10.100.100.10
bcp.exe CompanyDatabase.dbo.%%x format nul -c -x -f D:\PROD\BCPLIST\CompanyDatabaseFormat_%%x.xml -t "|C^|" -r "|R^|" -T -S 10.100.100.10
King of NES
  • 231
  • 1
  • 4
  • 13
  • `bcp` is a command line tool, like `curl`, `python`,`pip` or `nano`, not a SQL command. You can't run it with `cursor.execute`. You can install it locally and run it targeting the remote server – Panagiotis Kanavos Jan 05 '21 at 15:40
  • `they would really prefer i use python for this` who's "they" and why do they want you to use Python to run a script command instead of just run the script? You have to understand what the actual requirement is. You can't just try things until something works. Even `The executing and remote servers are trusted within the network,` is wrong - there's no such thing. The *account* that runs `bcp` is an Active Directory user with permissions to connect to the database. That's why Windows Authentication works – Panagiotis Kanavos Jan 05 '21 at 15:44
  • they is my company – King of NES Jan 05 '21 at 15:47
  • Well, ask them what they actually want. You haven't understood the requirement yet. Why do they want Python to execute a batch command? Why not use a SQL Server Agent Job or Windows Scheduled Task to execute the script command? – Panagiotis Kanavos Jan 05 '21 at 15:47
  • Does this answer your question? [How to call an external command?](https://stackoverflow.com/questions/89228/how-to-call-an-external-command) – Panagiotis Kanavos Jan 05 '21 at 15:49
  • we are using more python on everything-it is more reliable than powershell for servers running without an active window. – King of NES Jan 05 '21 at 15:49
  • No it's not. If you have problems with your current scripts, why assume the same mistakes won't be made when rewriting the same scripts in another language? Right now you're confusing a CLI command for a SQL command. That's not Powershell's problem. If you use the wrong account to run a script, it's not Powershell's problem. Running a Python script with the wrong account will fail the same way – Panagiotis Kanavos Jan 05 '21 at 15:51
  • i tried the subprocess.call(command). a command windows opened, with no prompts, which disappeared after a moment. and no file was created. Its like it understand what i was trying to do, didn't error, but didnt do anything. – King of NES Jan 05 '21 at 15:53
  • 1
    So you see, the problem isn't Powershell. It won't be fixed if you use Python. – Panagiotis Kanavos Jan 05 '21 at 15:53
  • 1
    actually i spoke too soon. I had to re-add the correct server IP-and it worked fine. THANK YOU sir. – King of NES Jan 05 '21 at 15:57
  • would you want to answer the question with the subprocess.call you mentioned, or would you prefer i go ahead and do it? – King of NES Jan 05 '21 at 15:58
  • You imported the `bcp` package but never used it. If you check [the package docs](https://bcp.readthedocs.io/en/latest/#bcp.core.BCP.dump) you'll see that saving is performed by `my_bcp.dump(query='select * from sys.tables', output_file=file)`. If that works, it's a better option than executing the `bcp` command – Panagiotis Kanavos Jan 05 '21 at 15:58

1 Answers1

0

Expanding on the comment by Panagiotis Kanavos; By using the subprocess package-you can put the entire bcp command into it and circumvent the bcp utilities.

import subprocess
datacommand = 'bcp.exe Databasename.dbo.databasetable out D:\DirectoryPath\Testbcp.csv -c -t "|C^|" -r "|R^|" -T -S 10.100.100.10'
subprocess.call(datacommand)
formatcommand = 'bcp.exe Databasename.dbo.databasetable format nul -c -x -f D:\DirectoryPath\Format.xml -t "|C^|" -r "|R^|" -T -S 10.100.100.10'
subprocess.call(formatcommand)
King of NES
  • 231
  • 1
  • 4
  • 13