0

I am attempting to load a .csv file from ubuntu 16.04 using msobdcsql17 driver loaded via bcp. The Linux server is sitting in an active directory environment, and I am using a username/password combo to access the data.

When I run the command, it loads no files, and it gives no output.

I decided to make it as simple as possible, so here is my example. (code via this site)

The Table (SomeTable)

CREATE TABLE dbo.SomeTable
  (
    SomeTableID INT IDENTITY(1,1) NOT NULL --This is column "1"
    ColA INT --This is column "2"
    ,ColB INT --This is column "3"
    ,ColC INT --This is column "4"
  )
;

The Text File (test.csv)

Column_C,Extra,Column_A
1,2,3
4,5,6
7,8,9

The Format File (format.fmt)

14.0
3
1     SQLCHAR   0     8000     ","         4   ColC        ""
2     SQLCHAR   0     8000     ","         0   NotUsed     ""
3     SQLCHAR   0     8000     "\r\n"      2   ColA        ""

The Command

bcp SomeTable in "/full/path/to/test.csv" -d DatabaseName -U"username" -P"password" -S"SERVERNAME,1432" -f"/full/path/to/format.fmt" -e error.txt

The Result

[Tue 12  9:16AM]:~/
ॐ  bcp SomeTable in "/full/path/to/test.csv" -d DatabaseName -U"username" -P"password" -S"SERVERNAME,1234" -f"/full/path/to/format.fmt" -e error.txt

[Tue 12  9:16AM]:~/
♻

While the result shows nothing, that change in the little symbol says that it returned with an error.

NOTE: I am trying to access an instance (reason for the port number 1234). Using a named instance does not work with the linux driver, so a fixed port must be assigned and used.

What I have tried:

  • Adjust credentials: It shows an error indicating it can't login with the wrong credentials
  • Exporting a format file: Works fine
  • Dumping from the same credentials/config: Works fine
  • Uninstalling all ODBC drivers, the reinstalling msodbcsql17: Fails, no output
  • Using Sql Server ODBC driver v13: Fails, no output
  • Using an XML format file: Fails, no output
  • Tried removing the format file and using '-c' option: Fails, no output

Possible ideas of what could be wrong:

  • it is unable to access the file on the server: would I need to map a drive to the linux server on the sql box?
  • ???????????

If anyone knows how to get some verbose output so I can troubleshoot, or make this happen, would love the help...

Dan Williams
  • 3,769
  • 1
  • 18
  • 26
  • To confirm, you are trying to bcp.exe to SQL on ubuntu, correct? if there is an error, bcp.exe should return it, unless the CSV file is empty? If the file is not empty, maybe post the contents of your format file. – Greg Feb 11 '19 at 21:47
  • @Greg, so I cleaned up the question with a simple example that fails, and included my output. Let me know if that helps. – Dan Williams Feb 12 '19 at 14:46
  • I would do process of elimination. If that is what your data looks like, get rid of the format file since it probably isnt needed, but ensure you add parameter -c. – Greg Feb 12 '19 at 14:57
  • Sorry, what I meant is, do hcp out and then in, without a format file, but use -c for character mode. – Greg Feb 12 '19 at 14:58
  • @Greg same blank output for -c without a format file. I am moving to add a samba share now to see if it is a visibility error – Dan Williams Feb 13 '19 at 13:20
  • There is a more recent bcp on linux example by Microsoft, I would also try that step by step. If no additional responses here, you should try msdn forums – Greg Feb 13 '19 at 13:28
  • Thanks @Greg! Those are both great suggestions! I'll check it out and see what I can see. – Dan Williams Feb 13 '19 at 13:31

0 Answers0