Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ruby on Rails
HowtoConnectToMicrosoftSQLServer (Version #232)

Home Page | All Pages | Recently Revised | Feed

These instructions pertain to connecting from a Windows computer. If you need to connect from a non-Windows computer, follow these instructions: HowtoConnectToMicrosoftSQLServerFromRailsOnLinux. For OSX using iODBC see HowtoConnectToMicrosoftSQLServerFromRailsOnOSX.
For FreeBSD see HowtoConnectToMicrosoftSQLServerFromRailsOnFreeBSD.

Get the latest source distribution of Ruby-DBI from: http://rubyforge.org/projects/ruby-dbi/ and copy the file:

src/lib/dbd_ado/ADO.rb

to:

X:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb 

(you will need to create the ADO directory).

Then simply set up your railsapp/config/database.yml

Here’s mine as a reference:

development:
  adapter: sqlserver
  database: database_name
  host: DBI:ADO:Provider=SQLOLEDB;Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=your_pw_here;
  username: user_name
  password: your_pw_here

And that should be it!

Question: Is there away to set a timeout? If I query a locked row my program hangs.

Note: On the latest version it should look like this:

development:
  adapter: sqlserver
  database: database_name
  host: server_name
  username: user_name
  password: your_pw_here

If the database server is running on the same machine as Rails, you can use ‘.’ as the host name.

Note: This may be obvious but remember to avoid tabs in your yml, especially if you copy and paste this code.

Note: Use this if your SQL Server is on a non-standard port:

development:
  adapter: sqlserver
  database: database_name
  host: server_name,5555
  username: user_name
  password: your_pw_here

Making Rake’s database tasks work (yawn)

Append '<Drive>\<ProgramFolder>\Microsoft SQL Server\MSSQL\Upgrade' to your PATH environment variable – this makes scptxfr.exe et. al. available for Rake.

I didn’t need to do this in SQL Server 2005 with Rails 1.1.4, which is fortunate because this file doesn’t exist in SQL Server 2005, as was noted in the below comments

Lowercasing it all (by RonLusk)

Because our SQL Servers are full of mixed-case field names, I dinked with sqlserver_adapter.rb (found at – x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\activerecord\connection_adapters\sqlserver_adapter.rb)_ to lowercase all the column names. In the columns(table_name, name=nil) method, I turned

  SELECT COLUMN_NAME as...

into

  SELECT LOWER(COLUMN_NAME) as...

(around line 178), and in the private method select(sql, name, @connection) has a line

  record[col] = row[col]

that became

  record[col.downcase] = row[col]

Now all the attributes look lowercase, and I don’t get odd “that’s a constant!” messages from ruby or from my eyes.
Note: I also had to modify the cast_to_time method to dink with the return time if it was before 1970, but that’s a special case for this database, perhaps.

As an alternative to the above you can hack row.rb in the DBI directory to map the colum names to uppercase or lowercase when it creates the hash it uses internally. There is also a patch on the rubyforge site to make the fetching of the rows case insensitive.

Avoiding the \NoMethodError problem

You will have to patch the x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\connection_adapters\sqlserver_adapter.rb file or you will always receive a \NoMethodError on trying to do a simple SELECT on your SQL server table.

The patch details are here – http://dev.rubyonrails.org/ticket/1160 – you will have to cut and paste the code into the sqlserver_adapter.rb file above.

This has been fixed in later versions of activerecord.

Newbie’s experiences with SQLServer 2005 Beta 2

The major fix (apart from the ones suggest above) was to patch sqlserver_adapter.rb to force it to use of the new provider name ‘SQLNCLI’ instead of ‘SQLOLEDB’. One thing different from above was that I didn’t specify the connection string in database.yml’s host line, i only specified the database machine name. I tried with host having the connection string and it didn’t seem to work. At least i’m up and running now. :)

I used a different approach in the database.yml added a provider:

development:

  adapter: sqlserver

  database: database

  username: user

  password: pwd

  host: .\SQLEXPRESS

  mode: DBI:ADO

  provider: SQLNCLI

and in the sqlserver_adapter.rb added

provider    = config[:provider] ? config[:provider].to_s : ‘SQLOLEDB’

and modified the line 39 to:

driver_url = “DBI:ADO:Provider=#{provider};Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};”

and it is working fine.

Another newbie’s experience with SQL Server 2005 final

The rails 1.0 sql server tasks use a “scptxfr.exe” that is not in the 2005 distribution. However, if you have a 7.0/2000 install around, I was able to “up port” the scptxfr.exe (along with its two minor dependencies sqlresld.dll and Resources\1033\scptxfr.rll) to my SQL Server 2005 machine (I put them in rails_app/db/tools so as not to muck around in Program Files) and now rails 1.0/SQL Server 2005 final is working nicely.

Other than “scptxfr.exe”, I didn’t have to make any other patches/fixes/etc. (well, installing ADO.rb aside).

Has anyone been able to confirm this? What is meant by “up port”; did you just copy them from the 7/2000 folders to your new folder?

Based on this link it seems that “up port” or “back port” are the same thing and just means coping the files from a version 7/2000 to somewhere in your path on the ruby machine.

How to go about using Trusted Security

Go into sqlserver_adapter.rb, and modify:

conn = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};")

take out: User Id=#{username};Password=#{password};
add: Integrated Security=SSPI;

      conn = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};Integrated Security=SSPI")

Of course, you need to make sure that the user account that is running webbrick, Apache, IIS, etc., has windows-based privs into the particular database.

Ideally, it would be nice to make a patch so that in database.yml, you could do up your connection like so:

development
  adapter: sqlserver
  database: nomomysql
  host: nomoora
  trusted_connection: true
  ...and any other extended OLEDB flags to pass to this driver as well

and have sqlserver_adapter.rb build the connect string appropriately.

How to fix db_schema_dump and stored procedures

If you get an error like:

DBI::DatabaseError: Count
    OLE error code:8000FFFF in Microsoft OLE DB Provider for SQL Server
      ITransaction::Commit or ITransaction::Abort was called, and object is in a
 zombie state.
    HRESULT error code:0x80020009
      Exception occurred.: EXEC sp_helpindex MyTable

You need to turn off the DBI AutoCommit feature. At the end of environment.rb you can add:

ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false

Question about the above fix

Thank you for posting this fix, it works!

However, does this setting negatively affect other operations where I need to commit? If so, what’s the code that I need to add to my Ruby programs?

Comment about the above fix

When I tried to implement this fix, my migrations stopped working. Was it fully tested?

Additional comment on the above fix

I can confirm that migrations stop working once the above line is added to environment.rb in Rails 1.1.2. While schema.rb may appear to autogenerate as it should, the database itself does not appear to actually execute any changes made in the migration. It seems we’re stuck with working migrations and a corrupt schema.rb, or a “correct” schema.rb with migrations that don’t execute on the DB.

Using the above fix only for db:schema:dump

You can create a customized rake task from the original db:schema:dump and include the above statement into it. Copy the following into mydb.rake and place it to appfolder/lib/tasks:

namespace :mydb do
  task :migrate => :environment do
    ActiveRecord::Migrator.migrate("db/migrate/", ENV["VERSION"] ? ENV["VERSION"].to_i : nil)
    Rake::Task["mydb:schema:dump"].invoke if ActiveRecord::Base.schema_format == :ruby
  end

  namespace :schema do
    task :dump => :environment do
      require 'active_record/schema_dumper'
      File.open(ENV['SCHEMA'] || "db/schema.rb", "w") do |file|
        ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
        ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file)
      end
    end
  end
end

Now you can use “rake mydb:schema:dump” to have a correct schema.rb file. For migration run “rake mydb:migrate”.

How to fix the “OLE error code:800A0E78” error

When calling rake db:schema:dump on a database containing tables without any indexes you get this kind of error:

# Could not dump table "Table" because of following DBI::DatabaseError
#   EOF
    OLE error code:800A0E78 in ADODB.Recordset
      Operation is not allowed when the object is closed.
    HRESULT error code:0x80020009
      Exception occurred.

The solution, as a newsgroup message said:

I have also met this error on tables without any index (on relation tables created by older Rails version).

So I have created an unique index on its keys and the error disappeared.

Regards,

Karel

Connect via ODBC

Connecting via the OLEDB adaptor seems to have it’s quirks. Another posibility is to connect via ODBC (tested with Rails 1.1.6)

e.g. for a trusted connection

development:
  adapter: sqlserver
  mode: odbc
  dsn: Driver={SQL Server};Server=<your server>;Database=<your db>;Trusted_Connection=yes;

(Original message here)

These instructions pertain to connecting from a Windows computer. If you need to connect from a non-Windows computer, follow these instructions: HowtoConnectToMicrosoftSQLServerFromRailsOnLinux. For OSX using iODBC see HowtoConnectToMicrosoftSQLServerFromRailsOnOSX.
For FreeBSD see HowtoConnectToMicrosoftSQLServerFromRailsOnFreeBSD.

Get the latest source distribution of Ruby-DBI from: http://rubyforge.org/projects/ruby-dbi/ and copy the file:

src/lib/dbd_ado/ADO.rb

to:

X:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb 

(you will need to create the ADO directory).

Then simply set up your railsapp/config/database.yml

Here’s mine as a reference:

development:
  adapter: sqlserver
  database: database_name
  host: DBI:ADO:Provider=SQLOLEDB;Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=your_pw_here;
  username: user_name
  password: your_pw_here

And that should be it!

Question: Is there away to set a timeout? If I query a locked row my program hangs.

Note: On the latest version it should look like this:

development:
  adapter: sqlserver
  database: database_name
  host: server_name
  username: user_name
  password: your_pw_here

If the database server is running on the same machine as Rails, you can use ‘.’ as the host name.

Note: This may be obvious but remember to avoid tabs in your yml, especially if you copy and paste this code.

Note: Use this if your SQL Server is on a non-standard port:

development:
  adapter: sqlserver
  database: database_name
  host: server_name,5555
  username: user_name
  password: your_pw_here

Making Rake’s database tasks work (yawn)

Append '<Drive>\<ProgramFolder>\Microsoft SQL Server\MSSQL\Upgrade' to your PATH environment variable – this makes scptxfr.exe et. al. available for Rake.

I didn’t need to do this in SQL Server 2005 with Rails 1.1.4, which is fortunate because this file doesn’t exist in SQL Server 2005, as was noted in the below comments

Lowercasing it all (by RonLusk)

Because our SQL Servers are full of mixed-case field names, I dinked with sqlserver_adapter.rb (found at – x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\activerecord\connection_adapters\sqlserver_adapter.rb)_ to lowercase all the column names. In the columns(table_name, name=nil) method, I turned

  SELECT COLUMN_NAME as...

into

  SELECT LOWER(COLUMN_NAME) as...

(around line 178), and in the private method select(sql, name, @connection) has a line

  record[col] = row[col]

that became

  record[col.downcase] = row[col]

Now all the attributes look lowercase, and I don’t get odd “that’s a constant!” messages from ruby or from my eyes.
Note: I also had to modify the cast_to_time method to dink with the return time if it was before 1970, but that’s a special case for this database, perhaps.

As an alternative to the above you can hack row.rb in the DBI directory to map the colum names to uppercase or lowercase when it creates the hash it uses internally. There is also a patch on the rubyforge site to make the fetching of the rows case insensitive.

Avoiding the \NoMethodError problem

You will have to patch the x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\connection_adapters\sqlserver_adapter.rb file or you will always receive a \NoMethodError on trying to do a simple SELECT on your SQL server table.

The patch details are here – http://dev.rubyonrails.org/ticket/1160 – you will have to cut and paste the code into the sqlserver_adapter.rb file above.

This has been fixed in later versions of activerecord.

Newbie’s experiences with SQLServer 2005 Beta 2

The major fix (apart from the ones suggest above) was to patch sqlserver_adapter.rb to force it to use of the new provider name ‘SQLNCLI’ instead of ‘SQLOLEDB’. One thing different from above was that I didn’t specify the connection string in database.yml’s host line, i only specified the database machine name. I tried with host having the connection string and it didn’t seem to work. At least i’m up and running now. :)

I used a different approach in the database.yml added a provider:

development:

  adapter: sqlserver

  database: database

  username: user

  password: pwd

  host: .\SQLEXPRESS

  mode: DBI:ADO

  provider: SQLNCLI

and in the sqlserver_adapter.rb added

provider    = config[:provider] ? config[:provider].to_s : ‘SQLOLEDB’

and modified the line 39 to:

driver_url = “DBI:ADO:Provider=#{provider};Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};”

and it is working fine.

Another newbie’s experience with SQL Server 2005 final

The rails 1.0 sql server tasks use a “scptxfr.exe” that is not in the 2005 distribution. However, if you have a 7.0/2000 install around, I was able to “up port” the scptxfr.exe (along with its two minor dependencies sqlresld.dll and Resources\1033\scptxfr.rll) to my SQL Server 2005 machine (I put them in rails_app/db/tools so as not to muck around in Program Files) and now rails 1.0/SQL Server 2005 final is working nicely.

Other than “scptxfr.exe”, I didn’t have to make any other patches/fixes/etc. (well, installing ADO.rb aside).

Has anyone been able to confirm this? What is meant by “up port”; did you just copy them from the 7/2000 folders to your new folder?

Based on this link it seems that “up port” or “back port” are the same thing and just means coping the files from a version 7/2000 to somewhere in your path on the ruby machine.

How to go about using Trusted Security

Go into sqlserver_adapter.rb, and modify:

conn = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};")

take out: User Id=#{username};Password=#{password};
add: Integrated Security=SSPI;

      conn = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};Integrated Security=SSPI")

Of course, you need to make sure that the user account that is running webbrick, Apache, IIS, etc., has windows-based privs into the particular database.

Ideally, it would be nice to make a patch so that in database.yml, you could do up your connection like so:

development
  adapter: sqlserver
  database: nomomysql
  host: nomoora
  trusted_connection: true
  ...and any other extended OLEDB flags to pass to this driver as well

and have sqlserver_adapter.rb build the connect string appropriately.

How to fix db_schema_dump and stored procedures

If you get an error like:

DBI::DatabaseError: Count
    OLE error code:8000FFFF in Microsoft OLE DB Provider for SQL Server
      ITransaction::Commit or ITransaction::Abort was called, and object is in a
 zombie state.
    HRESULT error code:0x80020009
      Exception occurred.: EXEC sp_helpindex MyTable

You need to turn off the DBI AutoCommit feature. At the end of environment.rb you can add:

ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false

Question about the above fix

Thank you for posting this fix, it works!

However, does this setting negatively affect other operations where I need to commit? If so, what’s the code that I need to add to my Ruby programs?

Comment about the above fix

When I tried to implement this fix, my migrations stopped working. Was it fully tested?

Additional comment on the above fix

I can confirm that migrations stop working once the above line is added to environment.rb in Rails 1.1.2. While schema.rb may appear to autogenerate as it should, the database itself does not appear to actually execute any changes made in the migration. It seems we’re stuck with working migrations and a corrupt schema.rb, or a “correct” schema.rb with migrations that don’t execute on the DB.

Using the above fix only for db:schema:dump

You can create a customized rake task from the original db:schema:dump and include the above statement into it. Copy the following into mydb.rake and place it to appfolder/lib/tasks:

namespace :mydb do
  task :migrate => :environment do
    ActiveRecord::Migrator.migrate("db/migrate/", ENV["VERSION"] ? ENV["VERSION"].to_i : nil)
    Rake::Task["mydb:schema:dump"].invoke if ActiveRecord::Base.schema_format == :ruby
  end

  namespace :schema do
    task :dump => :environment do
      require 'active_record/schema_dumper'
      File.open(ENV['SCHEMA'] || "db/schema.rb", "w") do |file|
        ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
        ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file)
      end
    end
  end
end

Now you can use “rake mydb:schema:dump” to have a correct schema.rb file. For migration run “rake mydb:migrate”.

How to fix the “OLE error code:800A0E78” error

When calling rake db:schema:dump on a database containing tables without any indexes you get this kind of error:

# Could not dump table "Table" because of following DBI::DatabaseError
#   EOF
    OLE error code:800A0E78 in ADODB.Recordset
      Operation is not allowed when the object is closed.
    HRESULT error code:0x80020009
      Exception occurred.

The solution, as a newsgroup message said:

I have also met this error on tables without any index (on relation tables created by older Rails version).

So I have created an unique index on its keys and the error disappeared.

Regards,

Karel

Connect via ODBC

Connecting via the OLEDB adaptor seems to have it’s quirks. Another posibility is to connect via ODBC (tested with Rails 1.1.6)

e.g. for a trusted connection

development:
  adapter: sqlserver
  mode: odbc
  dsn: Driver={SQL Server};Server=<your server>;Database=<your db>;Trusted_Connection=yes;

(Original message here)

i2 on Rails, powered by Phusion Passenger™ and Ruby Enterprise Edition.