Connect to an MSSQL database from a Ruby on Rails application running on Ubuntu

Question

I have a situation where I am trying to create a web application that takes the total number of records in a table and displays it on the screen. Sounds easy ...?

The main problem I am facing is that the database I want to look at is MSSQL. I did not configure such a DB connection from Rails, so I was hoping that someone could point me in the right direction.

My RoR application will run on the Ubuntu server (and is being developed on the OSX Leopard system).

EDIT:

• I must clarify that the MSSQL DBMS runs on SQL Server 2005 and is located in a Windows server environment.

• I tried checking the wiki.rubyonrails.org manual, but the site does not seem to be working (sadly)

Thanks!

Answer 1

This question in Stackoverflow can help: Rails and MSSQL 2008 - will we face barriers?

Basically you will need to install the MSSQL database adapter (not MySQL or Postgres, which will go through most of the tutorials) and configure your database.yml accordingly:

http://rorblog.techcfl.com/2008/04/14/ruby-on-rails-connection-to-sql-server/

http://the-banana-peel.saltybanana.com/2008/06/connecto-to-microsoft-sql-server-from.html

http://wiki.rubyonrails.org/database-support/ms-sql (Although wiki rails look down while writing)

PS I assume that the MSSQL server will run on a separate Microsoft server somewhere.

Answer 2

Take a look at the example I did on how to use the specified actionrecord-sqlserver adapter here

You can use rail model mappings and use ActiveModel helpers.

Answer 3

I will have a hit and say that you probably need to connect to mssql DB via ODBC. There seem to be some gems that do this. We hope that one of them will have the documents to put you on the right track.

ODBC rubygems

Answer 4

This stone is great and easy to set up: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter

Answer 5

I used Sequel with DBI and ODBC and it seems to work.

require "dbi" require "sequel" Sequel.datetime_class = DateTime p "testing dbi" # to setup a DSN, Start->Settings->CtlPanel->AdminTools->DataSources conn = DBI.connect('DBI:ODBC:dsn') p conn.connected? p conn.select_one("SELECT @@VERSION") conn.disconnect p "testing sequel" db = Sequel.odbc('(odbc_dsn_goes_here)', :db_type=>'mssql') db.fetch("SELECT TOP 2 * FROM TABLE") do |row| p row end 

Answer 6

I used activerecord-sqlserver-adapter with tiny_tds and it works!

Here is database.yml

 development: adapter: sqlserver username: 'user' password: 'secret' dataserver: 'dbserver_name\instance_name' database: 'dbname' appname: 'my app name' 

Answer 7

A gem called active-record-sql-adapter allows you to connect to a SQL Server database through ActiveRecord. You can do something like

 class RemodeDB establish_connection(:remote_db) #<= self.abstract_class = true # to avoid Rails' no associated model exception end 

then your classes inherit the connection like this:

 class Product < RemoteDB self.table_name ... end 

Note that to connect to earlier versions of SQL Server (2005 in your case), you will need an earlier version of gem, which may not be compatible with your current version of Rails.