BCP neither gives results nor outputs anything when using valid statements but it does throw errors when passing invalid parameters
4
votes
0
answers
1998
views
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 (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.
Asked by CamelCamelius
(141 rep)
Sep 27, 2016, 01:32 PM
Last activity: Jul 5, 2022, 11:28 AM
Last activity: Jul 5, 2022, 11:28 AM