Tuesday, February 1, 2011

Rails 3 with SQLServer 2000

I've been working on a new front-end to our legacy system and was disappointed to find that ActiveRecord 3 would not support SQLServer 2000 due to some limitations with the Arel gem. So after trying without much success to find a patch that would make this work, I wrote my own.

One big change is that the table reference for :set_table_name is now case sensitive. I don't believe it was in the past. So either make sure those names match or you will see cryptic messages like:

NoMethodError: undefined method 'eq' for nil:NilClass (yeah that was fun to track down)

There are a couple of other basic changes that need to happen so make it work at all. You need to change the SUPPORTED_VERSIONS constant and also change the TOP definition in the Arel SQLServer Visitor ...

module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter
      SUPPORTED_VERSIONS = [2000,2005,2008].freeze
    end
  end
end

module Arel
  module Nodes
    module Visitors
      class SQLServer
        def visit_Arel_Nodes_Limit(o)
          "TOP #{visit o.expr}"
        end
      end
    end
  end
end


This will get the basics to work. You still won't be able to use some of the advanced features like offsets without further modification but this did the job for us. Well almost, there were a few other changes that needed to be made in our case since our column names are mixed-case. If you would like more info on these, let me know in the comments.

All the code here is found within the activerecord-sqlserver-adapter gem. I created overrides for all the methods and placed them inside the Rails app so that gem updates wouldn't cause any issues.