Currently I am working on executing Perl script in Unix box. The Perl script internally uses BCP command for for fetching the data from MS-SQL DB. The same perl script is properly running in Windows. But when I am executing it into Unix (using Putty). It is showing following error message on console.
sh: bcp: command not found
Below is my sql command in perl script.
"bcp \"select drive_id, ilf, xcoord, ycoord
from $mdtdb\.dbo\.$mdtcentraldtdtable a where
drive_id = $driveid and not exists
(select 'x' from $mdtdb\.dbo\.$mdtcentralaudittable b where
a.drive_id = b.drive_id and a.ilf = b.ilf)\"
queryout $tempdatafile -o $bcpoutfile -S $mdtsvr -q -c -t ,
-U $user1 -P $pw1";
I am successfully able to connect with the DB.
I need your help to resolve above bcp issue.
On unix, there is a bcp tool for sybase clients. It works only for Sybase Products.
There is an open-source replacement for bcp that works with MSSQL server: freebcp But the freetds library is a bit difficult to configure, and (being a reverse engineering effort by volunteers) it works only with relatively simple datatypes.
To install it on ubuntu/debian based linux machines, run this
apt-cache search freetds
freetds-common - configuration files for FreeTDS SQL client libraries freetds-dev - MS SQL and Sybase client library (static libs and headers) libct4 - libraries for connecting to MS SQL and Sybase SQL servers libdbd-freetds - Freetds database server driver for libdbi libsybdb5 - libraries for connecting to MS SQL and Sybase SQL servers tdsodbc - ODBC driver for connecting to MS SQL and Sybase SQL servers freetds-bin - FreeTDS command-line utilities libaprutil1-dbd-freetds - Apache Portable Runtime Utility Library - FreeTDS Driver sqsh - commandline SQL client for MS SQL and Sybase servers libqt4-sql-tds - Qt 4 FreeTDS database driver
This command is optional:
apt-file list freetds-bin
freetds-bin: /usr/bin/bsqldb freetds-bin: /usr/bin/bsqlodbc freetds-bin: /usr/bin/datacopy freetds-bin: /usr/bin/defncopy freetds-bin: /usr/bin/fisql freetds-bin: /usr/bin/freebcp freetds-bin: /usr/bin/osql freetds-bin: /usr/bin/tdspool freetds-bin: /usr/bin/tsql
These commands install most of what you need
apt-get install freetds-bin apt-get install freetds-dev
You must then enter your connection data into configfile
/etc/freetds/freetds.conf [OurMSSQLServer] host = 1xx.xxx.xxx.xx port = 1433 tds version = 8.0
Maybe something else in this configfile has to be changed. This I don't remember right now.
From here on you are on your own. Please read the freetds docs: http://www.freetds.org/userguide/
The freetds software is up-to-date (actively developed). But the dev team is very small I think.
You can also compile freetds from source, but that may take more effort.
The error is very clear: the bcp command is not found in your $PATH and therefore it fails, but the real issue is that bcp is a MS SQL server utility and you won't find it on UNIX
You might be able to get programmatic access to an MSSQL database from Perl on unix by using DBI → DBD::ODBC → Easysoft SQL Server ODBC Driver