Connecting Rails to MS SQL Server 2000

The fasted method to connect Rails to MS SQL Server 2000 is using ODBC. Install gem plug-in using the command below:

gem update --system

gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org

Next steps are very important. It is very common for Windows database developer but not if you are from other platform like OSX or Linux.

Create DSN (Data Source Name) in Administrative Tools, Data Sources (ODBC).

Important!!! Make sure the default database is set to the database you wanted to use. Else it will default to Masters database.

Remember the Data Source Name.

Next is to configure your database.yml file in Rails config directory

Here is the settings:

production:

  adapter: sqlserver

  mode: odbc

  dsn: SMSdb

  username: USERNAME

  password: PASSWORD

Try using the rake migration command in my earlier post.

For DSN less configuration (means does not need to create a DSN in Data Sources (ODBC)).

database.yml settings:

development:

    adapter: sqlserver

    mode: odbc

    dsn: DRIVER=/opt/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=sqlserver.com;DATABASE=DBNAME;Port=14330;uid=dbusername;pwd=dbpassword;