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).
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'.
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:
-T (trusted connection using integrated security) for bcp so I have to specify the server, user and password.queryout option on a very simple small table already but still no luck.sqlcmd like the following: sqlcmd -S server -U user -P password -Q 'SELECT * FROM really_small_table'.Hopefully you've already solved your problem, but I had a similar problem configuring PyODBC for Python 3.4 on RedHat 7.1. I was trying to connect to Microsoft SQL Server 2016. Hopefully this will help someone else.
The real problem for me was ODBC/FreeTDS configuration. Here are the installation steps I took and the eventual solution.
yum install unixODBC-devel.x86_64yum install freetds.x86_64tsql (FreeTDS debugging tool, more on that below), but never via Python. I eventually stumbled upon this blog, which pointed me to the correct driver via ldconfig -p | grep libtdsodbc (I believe this points to the FreeTDS driver, hence why Microsoft's driver probably isn't needed).The eventual configuration I ended up with is as follows:
/etc/odbc.ini (note: I had to create this file as the ODBC/FreeTDS installation process didn't create it for me.)
[SQLServer]
Description = TDS driver (Sybase/MS SQL)
Driver = SQLServer
Servername = your_sql_hostname
TDS Version = 0.95
Database = your_database_name (not to be confused with instance name)
Port = 1433
/etc/odbcinst.ini
[ODBC]
# Enables ODBC debugging output. Helpful to see where things stop working.
Trace = yes
TraceFile = /etc/odbcinst.trace
[SQLServer]
Description=TDS driver (Sybase/MS SQL)
Driver=/lib64/libtdsodbc.so.0
Driver64=/lib64/libtdsodbc.so.0
UsageCount=1
tsql is debugging/testing tool that comes with FreeTDS. It is your friend. I was able to use it early on to verify that the networking side of things was correct (i.e. no firewalls blocking access, hostnames resolve, etc).
Use tsql -H your_sql_hostname -L to get the instance names and ports from your SQL server. Note that you'll use port 1433 (which is the MS SQL instance discovery port) in your ODBC configuration above, not these port numbers.
ServerName your_sql_hostname
InstanceName instance_1_name
IsClustered No
Version 13.0.x.x
tcp 5555
ServerName your_sql_hostname
InstanceName instance_2_name
IsClustered No
Version 13.0.x.x
tcp 6666
Use the port from the previous tsql command like so:
tsql -H your_sql_hostname -p your_sql_instance_port -D your_database_name -U your_username -P your_password
You should get a prompt. Try querying some data from your database (note that you have to type GO on a separate line to actually run your query). If this works but you still can't connect via Python/other-app, it usually means ODBC/FreeTDS are installed correctly and there are no networking problems, but your ODBC configuration isn't correct.
1> SELECT TOP 1 * FROM your_table
2> GO
{a row will be returned here}
1> # prompt returns, use `exit` to get out