Ruby on Rails connection to SQL Server

Recently I needed an application to be able to connect to a SQL Server database and import data into MySQL. After reading a number of different articles on the easiest way to accomplish this with each one being different and still not letting me accomplish my connection, I figured I would run through the steps that I used to make Rails work with SQL Server.

First off, I had to install the active record SQL Server gem.

install activerecord-sqlserver-adapter—source=http://gems.rubyonrails.org

Next, get the latest source distribution of Ruby-DBI from: http://rubyforge.org/projects/ruby-dbi/ and copy the file:

lib/dbd/ADO.rb

to:

X:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb

Then in the your config/database.yml file place:

sql_server_database_name:
adapter: sqlserver
database: database_name
host: server_name or local_machine_name
username: user_name
password: your_pw_here

user_name has to be a valid login for the SQL Server DB that has connect and select permissions

Lastly, place in any model that is going to need to establish a connection to SQL Server:

establish_connection :sql_server_database_name

It is that simple. Unfortunately, I never found a set of instructions on accomplishing this that worked for me, I had to take a number of examples and through trial and error, find out what combination worked. Hope this helps.

Tagged as: ruby on rails, sql