Using TinyTDS to connect to SQL Server with Ruby on Rails 3 (for Mac OSX)

By kenglish

Note: please see comments by Ken Collins (metaskills.net) at the bottom of this entry. He mentions a better way to do this without even installing FreeTDS.

You will need FreeTDS to connect to MSSQL Server on Unix-based environments. You MUST use Homebrew to install FreeTDS on the Mac. MacPorts is evil and will not install FreeTDS correctly. Installation of Homebrew is covered excellently in the Homebrew installation docs:
https://github.com/mxcl/homebrew/wiki/installation

Edit the FreeTDS configuration for the FreeTDS library:

brew edit freetds

Replace from “def install” to “end ” with:

def install 
  args = ["--prefix=#{prefix}",
          "--with-tdsver=7.0",
          "--enable-msdblib",
          "--mandir=#{man}"]
 
  system "./configure", *args
  system 'make'
  system 'make install'
end

Install FreeTDS:

brew install freetds

Add an entry to the bottom of your freetds.conf for your MSSQL Server. This file is found at /usr/local/etc/freetds.conf:

[my_sql_server] 
host = <hostip>
port = 1433 
tds version = 7.0

Add the TinyTDS and activerecord-sqlserver-adapter gems to your Rails Gemfile.

  gem 'tiny_tds'
  gem 'activerecord-sqlserver-adapter', :require => false

Run bundler to install the gems:

bundle install

Finally, add the connection to your database.yml file:

development:
  adapter: sqlserver
  mode: dblib
  dataserver: my_sql_server
  database: my_database_name
  username: my_username
  password: xxxxx
  timeout: 5000

A reference for installing UnixODBC (which I don’t recommend), you can follow this tutorial:
MSSQL2005 on Rails on Snow Leopard (the easiest way I know how.)


, ,

categoriaProgramming, Tech commento7 Comments dataJanuary 13th, 2011

About...

This author published 77 posts in this site.

Share

FacebookTwitterEmailWindows LiveTechnoratiDeliciousDiggStumbleponMyspaceLikedin

Comments


Ezekiel Templin
January 31st, 2011

Thanks for the post, Kevin. This got me about 99% of the way there. I had to link a homebrew install of libiconv (“brew link libiconv”) before installing the tiny_tds gem. This might be an issue that is specific to my install, but if others have problems installing the tiny_tds gem after following these directions, it is worth a try.


Jimmy
March 27th, 2011

<3 — Thank you for this.


Luke
July 8th, 2011

Thanks for this very helpful. In case it’s helpful to anyone else I didn’t need to edit the brew file for my install I just left it as tdsver 8.0 which seems to work well for Sql Server 2008 R2.


Ken Collins
July 12th, 2011

Technically you do not even need FreeTDS or libiconv installed via MacPorts or Homebrew. The TInyTDS project includes Luis Lavena’s excellent rake-compiler and Miniportile projects so that you can just build your own custom native gem right within a clean checkout of the project. See the README on the github page for more info. Basically.

$ bundle install
$ rake compile
$ rake native gem

Then install the gem from the pkg directory.


Ken Collins
July 12th, 2011

Also, MacPorts is not evil. I maintain my own port file for FreeTDS 0.91 till it is officially released. Here is my ports with easy instructions to setup.

https://github.com/metaskills/macports

My port will install the latest (currently 0.91RC2) for FreeTDS and even give you options to install with OpenSSL, great if you want to use Azure with TinyTDS. Works like a champ.

https://github.com/metaskills/macports/blob/master/databases/freetds/Portfile

But again, all moot if you use what the project has built into it and no need for any system components!


kenglish
July 12th, 2011

Thanks for the update Ken!

Kevin


Ken Collins
August 24th, 2011

This is even more out of date now that 0.91 has been released and that homebrew is “almost” got their install script right. If this pull request has been done you can install FreeTDS and totally ignore the conf file as TinyTDS does not need it with 0.91 and up.

Leave a comment