0

I want to load a text file (TSV, tab-separated values) to SQL Server using Microsoft's Bulk Copy Program (BCP) utility. I want to call BCP in a python script via python's subprocess module.

Here's the command I'm entering:

subprocess.check_output(['bcp', 'TEST_load_hh20220818', 'in', 'C:\\Users\\xxxx\\some-folder\\_household.tsv', '-S', 'SQL-SVR', '-d', 'MTP2024', '-T', '-q', '-c', '-t', '\\t', '-F', '2'])

But when I run it, I get the error:

*** FileNotFoundError: [WinError 2] The system cannot find the file specified

So far I tried the following:

  • Confirmed that the file path is correct by running os.path.exists('C:\\Users\\xxxx\\some-folder\\_household.tsv') and also successfully loading the file into a Pandas dataframe--again, just to confirm that the file path is correct.

  • Successfully ran the script on three other computers, making me suspect it is a machine issue and not a syntax/script error. All machines that I tested on are running Windows 10.

  • Tried adding shell=True to the subprocess.check_output() parameters, as suggested on this thread. This did not fix the issue.

Any idea what's going on?

Darren C.
  • 101
  • 1
  • 1
  • 6

1 Answers1

0

The path is likely not valid. You mentioned that you verified the path, but hear me out.

When you execute your python command to verify the path, the command is running on the CPU of the same machine you executed the command on. This is likely a separate/distributed machine/CPU than where your SQL Server is running. Is SQL Server running on a different computer than where you run your python command?

If so, when your python script executes the BCP command, the BCP command is sent TO the computer that SQL Server is running on (the SQL Server named with the -S option).

Now, SQL Server has taken that command and is looking at its LOCAL C: drive for the path you've given it. But it's likely that this path is not valid ON the SQL Server. That path only exists on your local computer (workstation?).

To do something like this (build a BCP command that is sent to a remote or distributed SQL Server) you need to use full UNC paths that the SQL Server can see (or wrestle with drive mappings, but that's ugly). Also, the account that SQL Server is running as will need to have access to whatever UNC path you place the file in.

jamie
  • 745
  • 4
  • 11
  • I don't think BCP can accept UNC paths, you must map drives to letters. What's weird is I don't get the error for 3 other machines on the same network, i.e, I can successfully run specifying either a local path on the machine or a remote drive mapped to a letter on the machine--and it still loads to SQL server machine correctly. There seems to be something special about the machine giving me the error, but I don't know what it is. – Darren C. Aug 28 '22 at 19:34
  • BCP can accept UNC paths. You do not have to map drive letters. I do it all the time, in fact I only use UNC paths. Use of drive letters should be discouraged. But if you're stuck using drive letters, then it may simply be that the SQL server service account does not have access to the mapped drive or somewhere in the path. – jamie Aug 29 '22 at 19:35
  • Thanks for inspiring me to try again with UNC paths. You are correct and I was able to correctly load from a UNC path (which is good because I'm with in that messing with letter mappings adds needless complication). Still haven't resolved the issue with the one machine, but thanks for the note re: being able to specify UNC paths with BCP. – Darren C. Sep 01 '22 at 03:22
  • If you're still getting the "file not found" error, it's likely that the domain account that is being used to run the SQL Server service simply does not have access to the network share/UNC path you are using. Have an admin confirm this or check the access yourself The account running the SQL Service needs to have proper access to the location. – jamie Sep 01 '22 at 14:49