6

I have a situation where I'm trying to build a web app which takes a total count of records in a table and outputs it to the screen. Sounds simple right...?

The main problem I'm having is that the DB I want to look at is MSSQL. I haven't set up this kind of DB connection from Rails before so I was hoping someone could point me in the right direction.

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

EDIT:

  • I should clarify that the MSSQL DB is running on SQL server 2005 and is on a windows server environment.

  • I tried checking out the wiki.rubyonrails.org guide but the site seems to be down (sad)

Thanks!

Ganesh Shankar
  • 4,826
  • 8
  • 43
  • 56

7 Answers7

8

This Stackoverflow question might help: Rails & MSSQL 2008 - Will We Hit Barriers?

Basically you will need to install a MSSQL database adapter (rather than MySQL or Postgres that most tutorials step you through), and configure your database.yml appropriately:

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 the rails wiki looks down at time of writing)

P.S. I am assuming the MSSQL server will be running on a separate Microsoft server someplace.

Community
  • 1
  • 1
Clinton
  • 3,638
  • 3
  • 26
  • 33
  • Yes sorry I didn't clarify that. It's MSSQL 2005 running on a Windows server. Thanks for the guides, I'll check them out! (Yes the wiki is down and has been for a while, I was hoping it would have what I needed but haven't been able to get to it or a cached copy for hours... sad) – Ganesh Shankar Mar 03 '11 at 04:14
2

Take a look at an example I've made on how to use the mentioned actionrecord-sqlserver adaptor here

You can use mappings with rails models and use the ActiveModel helpers.

Paulo Fidalgo
  • 21,709
  • 7
  • 99
  • 115
0

I used Sequel with DBI and ODBC and it seems to be working.

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
Cody
  • 1
0

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

Here's database.yml 

development:
  adapter: sqlserver
  username: 'user'
  password: 'secret'
  dataserver: 'dbserver_name\instance_name'
  database: 'dbname'
  appname: 'my app name'
fmnoise
  • 109
  • 4
  • Advice: If you are using Rails 5, there's no way to install that gem to support sql server 2005/2008 at the time of writing this comment. – tommyalvarez May 02 '17 at 19:12
  • SQL Server adapter for ActiveRecord v5.1 supports SQL Server 2012 or higher. – fmnoise Apr 23 '18 at 21:05
0

A gem called active-record-sql-adapter lets you connect to an SQL Server db via 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 have your classes inherit the connection like so

class Product < RemoteDB
   self.table_name ... 
end

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

dimitry_n
  • 2,939
  • 1
  • 30
  • 53
0

I'll have a stab, and say that you'll probably need to connect to a mssql DB via ODBC. There appears to be a few gems that do this. hopefully one of them will have docs to put you on the right track.

ODBC rubygems

David Barlow
  • 4,914
  • 1
  • 28
  • 24
0

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

wantrapreneur
  • 410
  • 4
  • 13