3

I have to use bcp command-line tool to export data from an SQL Server database to a file in a Red Hat server. I am (apparently) using valid statements but bcp is not producing any kind of output/results. However, when I execute statements with missing or invalid parameters it displays the respective error. I am looking for the reason of this issue (e.g. defective installation, bad usage of bcp, lack of permissions or any other known conflict) and how to fix it.


bcp statement:

bcp fully_qualified_table_name out ./data.txt -c -S server -U user -P password

bcp usage:

usage: /opt/microsoft/bin/bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-q quoted identifier]
  [-t field terminator]     [-r row terminator]
  [-a packetsize]           [-K application intent]
  [-S server name or DSN if -D provided]             [-D treat -S as DSN]
  [-U username]             [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-d database name]

bcp version:

BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 11.0.2270.0

SQL Server version (SELECT @@VERSION):

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
   May 14 2014 18:34:29
   Copyright (c) Microsoft Corporation
   Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Distribution:

Red Hat Enterprise Linux 6.7 (KornShell).

Invalid statements with respective error message (examples).

bcp THAT_TUB_ACE.oh_nerd.table_name out ./data.txt -c -S sr._bear -U you_sr. -P pass_sword

    SQLState = S1T00, NativeError = 0
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
    SQLState = 08001, NativeError = 11001
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
    SQLState = 08001, NativeError = 11001
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]TCP Provider: Error code 0x2AF9

...

bcp fully_qualified_table_name ./data.txt -c -S valid_server -U valid_user -P bad_word

    bcp fully_qualified_table_name out ./data.txt -c -S valid_server -U valid_user -P bad_word
    SQLState = 28000, NativeError = 18456
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'valid_user'.

SUMMARY.

The objective is to generate a datafile using the following syntax (or similar):

bcp fully_qualified_table_name out ./data.txt -c -S server -U user -P password

The facts are:

  • When running a valid bcp statement there's nothing in the window at all (no output) and no datafile is created.
  • I cannot use option -T (trusted connection using integrated security) for bcp so I have to specify the server, user and password.
  • Tried queryout option on a very simple small table already but still no luck.
  • Credentials are valid, I successfully tested them using sqlcmd like the following: sqlcmd -S server -U user -P password -Q 'SELECT * FROM really_small_table'.
  • The bcp statements under "Invalid statements with respective error message (examples)" section of this question are just examples of invalid statements to show that bcp actually does something but giving the expected results.
2
  • I don't know anything about bcp, but reading the usage statement in your Q, is seems to me that the -c option wants a "character type" which you don't provide.
    – Jeff Schaller
    Sep 29 '16 at 15:23
  • Hi @JeffSchaller , there are some options to specify the kind of data bcp is dealing with...I am originally intending to use a format file but that adds complexity to the statement so in order to keep it simple I included -c option which will make bcp to store the exported data as SQLCHAR ( a native file storage type ), no matter if it's whether an integer, a date or any other type. So basically by specifying -c you get rid of any datatype consideration and simply store the data as delimited strings. Sep 29 '16 at 21:03
1

What I have found on my Debian platform is that the Microsoft ODBC installation process put libodbc.so into /usr/lib64. This isn't an expected location for libraries and bcp cannot find it. But instead of telling you that it can't find a necessary library it simply exits.

Solution for Debian. Your situation should be similar.

  1. Find where the installation procedure has put libodbc.so:

    find / -type f -name libodbc.so
    
  2. Add its directory to a new file /etc/ld.so.conf.d/odbc.conf. I've invented the name, and on my system /etc/ld.so.conf.d is included from /etc/ld.so.conf:

    # Required but not documented by MS ODBC installation for SQL Server
    /usr/lib64
    
  3. Run ldconfig to update the list of library locations

  4. Retry the bcp command

You will need to be root to perform steps 2 and 3. I assume that's no big deal given that you've managed to install the SQL Server libraries.

2
  • 1
    Hi @roaima ... all I can do is up-vote your answer because (after long communication and bureaucratic procedures) the Unix admin team didn't follow up the issue so unfortunately it will remain unsolved :( I couldn't agree more with your original (unedited) answer: this is wild!. I appreciate the efforts though :) Oct 24 '16 at 19:10
  • @HectorTorres no problem, I quite understand! (It was a nicely-researched question, by the way.)
    – roaima
    Oct 24 '16 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.