Does anybody here have positive experience of working with MS SQL Server 2005 from Rails 2.x?
Our developers use Mac OS X, and our production runs on Linux. For legacy reasons we should use MS SQL Server 2005.
We're using ruby-odbc and are running into various problems, too depressing to list here. I get an impression that we're doing something wrong.
I'm talking about the no-compromise usage, that is, with migrations and all.
Thank you,
I would strongly suggest you weigh up migrating from the legacy database. You'll probably find yourself in a world of pain pretty quickly. From experience, Rails and legacy schemas don't go too well together either.
I don't think there's a "nice solution" to this one, I'm afraid.
Instead of running your production server on Linux have you considered to run rails on Windows? I am currently developing an application using SQL Server and until know it seems to run fine.
These are the steps to access a SQL Server database from a Rails 2.0 application running on Windows.
The SQL Server adapter is not included by default in Rails 2. It is necessary to download and install it using the following command.
gem install activerecord-sqlserver-adapter
--source=http://gems.rubyonrails.org
Download the latest version of ruby-dbi from
http://rubyforge.org/projects/ruby-dbi/
and then extract the file from ruby-dbi\lib\dbd\ADO.rb
to C:\ruby\lib\ruby\site_ruby\1.8\DBD\ADO\ADO.rb.
Warning, the folder ADO does not exist, so you have to create it in advance.
It is not possible to preconfigure rails for SQL Server using the --database option, just create your application as usual and then modify config\database.yml in your application folder as follows:
development:
adapter: sqlserver
database: your_database_name
host: your_sqlserver_host
username: your_sqlserver_user
password: your_sqlserver_password
Run rake db:migrate to check your installation. If everything is fine you should not receive any error message.
Have you considered using JRuby? Microsoft has a JDBC driver for SQL Server that can be run on UNIX variants (it's pure Java AFAIK). I was able to get the 2.0 technology preview working with JRuby and Rails 2.1 today. I haven't tried migrations yet, but so far the driver seems to be working quite well.
Here's a rough sketch of how to get it working:
jruby -S gem install rails)jruby -S gem install activerecord-jdbcmysql-adapterjruby -S rails hello)jruby script/console and creating a model.
development:
host: localhost
adapter: jdbc
username: sa
password: kitteh
driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://localhost;databaseName=mydb
timeout: 5000
Note: I'm not sure you can use Windows Authentication with the JDBC driver. You may need to use SQL Server Authentication.
Best of luck to you!
Ben
Has anyone been able to do this with Cygwin?
I've read these sites:
I've tried the activerecord-sqlserver-adapter, also mentioned above, but I don't know where to put the ADO.rb file (since I'm not in a regular windows install, the path is different). I got my ADO.rb file from dbi-0.2.2.tar.gz
I've also tried the ODBC method. I installed ruby-odbc-0.9995.tar.gz But I get "time out of range"
ArgumentError: time out of range
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:1718:in `gm'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:1718:in `convertOdbcValToGenericVal'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:780:in `select_all'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:778:in `each'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:778:in `select_all'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:776:in `each'
/usr/lib/ruby/gems/1.8/gems/activerecord-odbc-adapter-2.0/lib/active_record/connection_adapters/odbc_adapter.rb:776:in `select_all'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:586:in `find_by_sql'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:1345:in `find_every'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:1307:in `find_initial'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:538:in `find'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:548:in `first'
Here are my pertinent installed gems:
activerecord (2.1.1)
Implements the ActiveRecord pattern for ORM.
activerecord-odbc-adapter (2.0)
ODBC Data Adapter for ActiveRecord
activerecord-sqlserver-adapter (1.0.0.9250)
SQL Server adapter for Active Record
dbi (0.4.0)
A vendor independent interface for accessing databases, similar to
Perl's DBI
rails (2.1.1)
Web-application framework with template engine, control-flow layer,
and ORM.
Our developers use Mac OS X, and our production runs on Linux. For legacy reasons we should use MS SQL Server 2005.
We are developing on Ubuntu 8.04, but our production servers are running Linux (Centos) and we are also using SqlServer 2005.
From our experiences the initial setup and config was quite painful - it took a couple of weeks to get everything to play nicely together. However, it's all seemless now, and I find SqlServer works perfectly well.
We use the FreeTDS ODBC drivers which once configured are fine.
DO NOT run productions Rails apps on Windows - you're asking for trouble. It's fine for development but nothing more. Rails doesn't scale well on Windows platforms.
Hope that helps.
@EdJ, can you please explain in detail (steps that you'd taken to resolve this)? I'm also facing a problem for couple of days trying to connect remote sql server and getting an error "DBI::InterfaceError could not load drivers..." I've installed latest dbi, dbd gems.
Any help in this will be greatly appreciated... Thanks in Advance
DPP