Using MS SQL server from Linux with Perl DBI

From Suntel Communications knowledgebase

Jump to: navigation, search

Accessing MS-SQL server from Linux used to be very difficult, but thanks to the smart folk over at FreeTDS its now almost a no-brainer.

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. With the combination FreeTDS and DBD::Sybase (The Sybase driver for Perl DBI) it is now possible to access a MS SQL server using Perl DBI in the same manner you would access any other DBI supported SQL server.

[edit]

Setup FreeTDS

  • Download FreeTDS (wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz)
  • Unpack FreeTDS (tar zxvf freetds-stable.tgz)
  • Change to FreeTDS directory (cd freetds-0.62.3 or whatever version you downloaded)
  • Build FreeTDS (Detailed Instructions are at http://www.freetds.org/userguide/config.htm)
  • Export Sybase Environment Variables (export SYBASE=/usr/local/freetds; export TDSVER=8.0)
  • Add Sybase Environment Variables to /etc/profile or system equivalent (On SUSE it's /etc/profile.local)
  • Add a section for your MS SQL server to /usr/local/freetds/etc/freetds.conf (With the correct hostname for your server)
[mssqlserver]
	host = mssqlserver.mydomain.com
	port = 1433
	tds version = 8.0
[edit]

Setup DBD::Sybase

Of course you could do things the hard way and compile and install DBD::Sybase by hand, however if you have perl (And FreeTDS) correctly installed on your system you should be able to do it automatically with

  • perl -MCPAN -e 'install DBD::Sybase'

When the test script asks to connect to your SQL server, you will need to supply the same name you configured in freetds.conf as well as a username and password with permission to connect to a test database on the SQL server. eg.

Sybase OpenClient found.
The DBD::Sybase module need access to a Sybase server to run the tests.
To clear an entry please enter 'undef'
Sybase server to use (default: troll): <strong>mssqlserver</strong>
User ID to log in to Sybase (default: sa): <strong>sqlusername</strong>
Password (default: undef): <strong>sqlpassword</strong>
Sybase database to use on mssqlserver (default: undef): <strong>testdb</strong>

If everything works as it should you now have DBD::Sybase installed which you can use to connect to MS-SQL server from any perl program you wish. If some of the tests fail, you can either fix the problem, or do a force install of DBD::Sybase with the following steps: 

cd ~/.cpan/build/DBD-Sybase-1.02
make install
Retrieved from "http://docs.suntel.com.tr/index.php/Using_MS_SQL_server_from_Linux_with_Perl_DBI"
Personal tools