Working with Microsoft SQL Server

If you've been doing any Microsoft development then you've most likely encountered Microsoft SQL Server. If you don't plan to use Microsoft SQL Server with your Rails projects, you can safely skip this section.

Rails has built-in support for this as long as your database design allows it. There are a few issues you should be aware of before starting:

  1. SQL Server lets you name tables using mixed-case, dashes, and other strange characters. Rails simply can't handle those well, so if you can't rename your tables, you may have to get creative. Table and column names should be defined using lowercase letters and underscores.

  2. Views work just like tables but you're limited when it comes to doing updates.

  3. When you use Rake to migrate your database, created tables are owned by the user that you specify in database.yml, not by the database owner.

  4. Stored procedures aren't easy to pull off but can be done provided you are careful in how you construct the return values from your procedure. I've had success executing procedures that return a single recordset.

There are two methods to connect your Rails applications to a Microsoft SQL Server database: an ODBC connection or an ADO connection.

I am going to assume that you have access to Microsoft SQL Server 2000 or higher and that you can already connect to that server using Enterprise Manager on your development machine. I also assume you've got a database created for your Rails application and that you've granted a user ...

Get Rails on Windows now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.