using ActiveRecord to access Microsoft SQL Server via ODBC

Recently I need to insert 400+ rows of data into an eight year old SQL Server 2000 database. It's really a pain in the ass if I have to do it by hand or learning a new tool to do it.

Luckily, I can use ActiveRecord to do that easily thanks to activerecord-sqlserver-adapter and and auto table field mapping of ActiveRecord.

Here is a quick step-by-step guide:

Install Ruby on Windows machine (that run SQL server)
Download One-Click Ruby Installer for Windows for Ruby 1.8.6 P26 at
http://rubyforge.org/frs/download.php/29263/ruby186-26.exe

And install it. If you use the default setting, it will setup ruby at c:\ruby folder with rubygem 0.9.3

Update RubyGems
Open "Command Prompt"
cd c:\ruby\bin gem update --system

Install ActiveRecord with SQLServer Adapter
gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org
If the installation
Successfully installed activesupport-2.2.2 Successfully installed activerecord-2.2.2 Successfully installed activerecord-sqlserver-adapter-1.0.0.9250 3 gems installed
Create a DSN (Data Source Name) Luckily I can reuse DNS from current ASP project so I don't need to create new one.
If you have to, you can follow guideline in [1].
Setup connection and create property ActiveRecord based classes

require "active_record"
 
ActiveRecord::Base.establish_connection(
  :adapter => "sqlserver",
  :mode => "odbc",
  :username => "yourusername",
  :password => "yourpassword",
  :dsn => "yourDSN"
)
 
class AnyNameYouWant < ActiveRecord::Base
  set_table_name "TableNeedToAccess"
  set_primary_key "PrimaryID"
end
 

Start irb by:
cd c:\ruby\bin irb

And paste above chuck of code in to irb. From now, you can use ActiveRecord based classes to read/write/add/remove SQL Server table as in a Rails script/console environment.

I also got a problem with Time.mktime and need to some code adjust by hand

Open
c:\ruby\lib\ruby\gems\1.8\gems\activerecord-sqlserver-adapter-1.0.0.9250\lib\active_record\connection_adapters\sqlserver_adapter.rb

Replace "Time.mktime" by "Datetime.new" will fix the problem [2]

References
[1] http://piao-tech.blogspot.com/2008/02/using-activerecord-with-microsoft-sql.html
[2] http://papillon.nwnx.org/index.php/archives/19