How do I access a SQL Server database from a Perl script in Linux?

Question

I have a Perl script on a Linux (Ubuntu 8.10) machine and I need to write data to a SQL Server Database. I've been trying to use the DBD::ODBC module but I can't get it to connect. Where can I get a free/open source driver to use to use for the ODBC connection or is there another way to do this from Perl on Linux?

Answer 1

Use the DBD::Sybase module, at one point Sybase and MS SQL Server shared a common codebase.

You may also want to investigate the open source FreeTDS libraries. See the FreeTDS FAQ Question "Which Perl library should I use".

Answer 2

I connect to SQL Server 2005 with the stack of unixODBC, freeTDS (this is the driver) and DBD::ODBC.

After you install these components, edit /etc/unixODBC/odbc.ini to read like this:

[DNS]
Description = my database
Driver = /usr/lib/libtdsodbc.so #path to freeTDS driver
Server = ServerName
Database = DatabaseName
Port = 1433 #sql server default port
TDS_Version = 9.0 #9.0 is sql server 2005
try domain login = yes
try server login = yes
nt domain = DOMAIN

If all goes well, you should be able to connect with:

$dbh = DBI->connect('dbi:ODBC:DNS', "userName", "passWord");

Good luck!

Answer 3

The following iODBC - Perl HowTo details how this can be done via ODBC once a suitable ODBC Driver for the target database is in place:

http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/IODBCPerlHOWTO

Best Regards

Hugh Williams

OpenLink Software