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.
CamelCamelius
  • 41
  • 4
  • 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. – Hannah Vernon Sep 27 '16 at 17:41
  • When you use sqlcmd to get results, are you querying the table you are attempting to `bcp out`? Perhaps the `bcp out` action is being blocked by some other process(es) accessing the table? See [this question](http://dba.stackexchange.com/questions/4760/what-is-blocking-and-how-does-it-happen) for details about that. – Hannah Vernon Sep 27 '16 at 17:45
  • I am guessing, but did you source the environment variable ? I have to do it on sybase when using bcp or isql to connect to SybaseASE instance. – Kin Shah Sep 27 '16 at 18:36
  • Hi @MaxVernon , I tried with different tables without issuing nothing else than **bcp** without any luck. Link you provided explains db locks in a very simple way. Thanks :) +1 – CamelCamelius Sep 27 '16 at 20:07
  • Hi @Kin , I remember once I was working with Sybase and I had some struggling because I didn't know about that and I think I used to get some error messages. For setting up the environment in Sybase I ran the `SYBASE.sh` script located in the Sybase installation folder and the problem was solved...I don't know if there's an analogue process for SQL Server...I would hope something like that might solve the issue :s – CamelCamelius Sep 27 '16 at 20:11

0 Answers0