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
queryoutoption on a very simple small table already but still no luck. - Credentials are valid, I successfully tested them using
sqlcmdlike 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.
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 asqueryout select * from database.schema.tablei 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-T(trusted connection using integrated security) so I had to specify the server, user and password. Triedqueryouton 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:09TESTSERVER01. Actually I use the same credentials to query the database using sqlcmd, something likesqlcmd -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:17bcp out? Perhaps thebcp outaction is being blocked by some other process(es) accessing the table? See this question for details about that. – Hannah Vernon Sep 27 '16 at 17:45SYBASE.shscript 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