Rails - MS-SQL Server problems (unixODBC, FreeTDS) on Mac 10.6

Question

Followed the instructions on the Rails wiki and have had success connecting to SQL Server 2000 with TSQL -- both with DSN-less and DNS connections. I'm running Mac OS X 10.6.3.

Wiki instructions here.

Installed ruby-odbc, dbi (0.4.0), dbd-odbc (2.4.5), activerecord-sqlserver-adapter (2.3.5).

In my database.yml (Rails 2.3.6):

development:
  adapter: sqlserver
  mode: ODBC
  dsn: 'DRIVER=/usr/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=mssql01.discountasp.net;DATABASE=DB_164368_dmusd;Port=1433;uid=DB_164368_dmusd_user;pwd=Schools77;'

This yields the following error: ODBC::Error: S1090 (0) [unixODBC][Driver Manager]Invalid string or buffer length

When I attempt to use a DSN connection, I get the following error: ODBC::Error: IM002 (0) [unixODBC][Driver Manager]Data source name not found, and no default driver specified

I have in fact verified that the FreeTDS driver (libtdsodbc.so) is installed and the path correct.

Can anyone spot the error of my ways? Thanks in advance.

Answer 1

I would split the configuration between:

freetds.conf

[somesqlserver]
 host = HOST_ADDRESS
 port = 1433
 tds version = 8.0 # for SQL2000    

and

odbc.ini

[server_connection]
Driver = /usr/local/lib/libtdsodbc.so
Server = ip_address_of_server
Database = database_name
client charset = UTF-8  #needed only on osx

in database.yml

development:
adapter: sqlserver
mode: odbc
dsn: server_connection
username: your_username
password: your_password

Very good article for reference on osx but easily adaptable to unix

Answer 2

I would strongly advise that you forget UnixODBC on Mac OS X. Instead, update the iODBC components to the latest version.

iODBC -- the ODBC driver manager Apple chose to ship with Mac OS X -- comes from the same source as the ODBC Adapter for Ruby on Rails -- OpenLink Software.

It may also be worth your while to test, if not deploy, with a commercial ODBC driver, such as that from OpenLink. This can help narrow down the source of errors you encounter when trying to use a "no cost" driver. (There's always a cost -- the only question is whether you spend money or time.)

Answer 3

Follow this tutorial to connect your rails app to MS SQL Server

Connect To MicrosoftSQLServer From Rails On Linux Box: http://wiki.rubyonrails.org/database-support/ms-sql