4

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.
  • I've just ran bcp database.schema.table out file.csv -S Machine\Instance -T -N (Using -T for login and -N to output the native SQL format instead of writing up a format file). and its working completely fine, When you say no output / results is there anything in the window at all? aditionally have you tried running as queryout select * from database.schema.table i know its ugly but may be worth as a test (also as testing depending on the size of your table consider running with -b 500 (small batch) just to limit what is going on – Ste Bov Sep 27 '16 at 14:38
  • Is sr._bear a valid fully qualified server name? Did you forget to change the login name from 'valid_user' when testing the 2nd BCP statement? – James Anderson Sep 27 '16 at 15:29
  • Hi @SteBov , I cannot use option -T (trusted connection using integrated security) so I had to specify the server, user and password. Tried queryout on a very simple small table already but still the same. You are right, no output / results means that there's nothing in the window at all and no file created with the exported data. – CamelCamelius Sep 27 '16 at 16:09
  • 1
    Hi @JamesAnderson , I use a valid server name like TESTSERVER01. Actually I use the same credentials to query the database using sqlcmd, something like sqlcmd -S server -U user -P password -Q 'SELECT * FROM really_small_table' with valid results. The 2nd and 3rd bcp statements are just examples of invalid statements to show that bcp actually does something but giving the expected results. – CamelCamelius Sep 27 '16 at 16:17
  • @HectorTorres - you should add those details to your question using the edit button at the bottom of the question. – Max Vernon Sep 27 '16 at 17:41

Your Answer

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

Browse other questions tagged or ask your own question.