Ruby on Rails
HowtoConnectToMicrosoftSQLServer (Version #295)

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.

Starting with Rails 2.0 the ActiveRecord sqlserver adapter is no longer included by default and must be installed separately:

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

Rails 1.X (Rails 2.X needs to do this, too)
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

Then in your database.yml

For Rails 2.X:

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

For Rails 1.X:

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!

Note that setting the username, password, or database name in the ‘host:’ string does NOT work in 2.X, because the database adaptor overrides the host: string with the values given for database:, username:, and password:

If the database server is running on the same machine as Rails, you can use ’.’ as the host name. If you have more than one SQL Server running, or your SQL Server is named, the server_name may look like “LYNX\MSSMLBIZ” if your computer name is “LYNX” and your SQL Server instance is named “MSSMSBIZ”, or ”.\MSSMLBIZ” on the localhost.

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

Got OLE error code 80004005
I’ve tried to follow exactly the previous steps with NO success, what I got is the following error:
Open OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server (Connect()).]SQL Server does not exist or access denied. HRESULT error code:0×80020009 Exception occurred.

Any idea on this? .......After several trials I got the next solution:

I got and copy the last Ruby-DBI gem and ADO.rb file as indicated in the beginning of this article.
In MY CASE I did NOT use the parameter
”host: DBI:ADO:Provider=SQLOLEDB;Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=your_pw_here;” that appears in the databe.yml file <= Every time I use it I got the same OLE error code.

Instead I used a little changed format in my database.yml file, here is mine for reference:


development:
  adapter: sqlserver
  database: demoPba0_development
  host: LAP-2F89FF92BC
  username: sa
  password: password1

test:
  adapter: sqlserver
  database: demoPba0_test
  host: LAP-2F89FF92BC
  username: sa
  password: password1

production:
  adapter: sqlserver
  database: demoPba0_production
  host: LAP-2F89FF92BC
  username: sa
  password: password1

Where:
-LAP-2F89FF92BC is the name of my machine.

Also is important to ensure to include a colon ’:’and a space ’ ’ after the name of each parameter, for instance:

“username: sa” is right
“username:sa” is not recommended

Best Regards !!!

Configuring SQL Server for SQL Server Authentication

There is lots of info on MSDN, but don’t forget to enable SQL Server Authentication (SQL Server only allows Windows Authentication mode by default). Use SQL Server Management Studio (Express), connect to the server, right-click on the server, choose Properties, choose the Security page, and choose SQL Server and Windows Authentication mode.

(You can check the SQL Server log after a failed login attempt (depending on those Security Properties) to see what the problem is.)

Then, in the treeview, select Security/Logins, right-click the list view, choose New Login…, and set it up for SQL Server Authentication.

Then, once you have set up your login, you need to create your databases and enable your new login to access them. From the treeview, click on Databases, and in the list view, right-click, choose New Database…, and give it a name (like mydb_development, mydb_test, and mydb_production). Then, in the treeview, click on each database, select Security/Users, and in the list view, click “New User…”. Enter both a “User name” (which is arbitrary) and a login name (which matches the login name you created at the previous step). Add “Role Memberships” of db_datareader, db_datawriter, and db_ddladmin. Click OK and you are ready to rake!

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

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\active_record\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.

Note for ActiveRecord 1.15:

The select method has changed, so you have to change

row_hash[handle.column_names[i]] = value

to this

row_hash[handle.column_names[i].downcase] = value

Also, in the columns method, you’re looking for this:


SELECT
cols.COLUMN_NAME as ColName

and changing it to


SELECT
LOWER(cols.COLUMN_NAME) as ColName

Further fix required when lowercasing column names

You need to make the following further fixes to avoid unexpected behaviour:

In the insert method:

  id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"]

to:

  id_value || select_one("SELECT @@IDENTITY AS ident")["ident"]

In the update method:

  end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]

to:

  end || select_one("SELECT @@ROWCOUNT AS affectedrows")["affectedrows"]

In the add_limit_offset! method:

  total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i

to:

  total_rows = @connection.select_all("SELECT count(*) as totalrows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:totalrows].to_i

One symptom of the problem is the ActiveRecord::Base#create method returning nil ids.

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.

There is a patch in the Rails bug-tracker that enables you to set up your connection in database.yml like so:

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

and have sqlserver_adapter.rb build the connect string appropriately. To help get this feature into the next version of rails, check out ticket 8862.

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)

SQL Server 2005 Express

I managed to connect to a SQL Server 2005 express box using ODBC and a trusted windows connection. Futher more in my particular case i need to exectue some stored procedures which I did by having to set up a custom subclass of ActiveRecord::Base as you effectivly have to call the whole SQL server SQL, so I just made a wrapper for that, nice and simple – hope this helps somone.

I am using the example from the database.yaml at the top of this page and am getting the following error when I try to start the WEBrick server (using Rails 1.2.2):

=> Booting WEBrick…
C:/development/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record
/connection_adapters/abstract/connection_specification.rb:34: uninitialized clas
s variable @@allow_concurrency in ActiveRecord::Base (NameError) from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `gem_original_require’ from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `require’ from C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/
active_support/dependencies.rb:495:in `require’ from C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/
active_support/dependencies.rb:342:in `new_constants_in’ from C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/
active_support/dependencies.rb:495:in `require’ from C:/development/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/
active_record/connection_adapters/abstract_adapter.rb:10 from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `gem_original_require’ from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `require’ ... 27 levels… from C:/development/ruby/lib/ruby/gems/1.8/gems/rails-1.2.2/lib/commands
/server.rb:39 from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `gem_original_require’ from C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.
rb:27:in `require’ from script/server:3

I am able to connect to the database from a non-Active Record Ruby application.

I am also getting the same error if I try to use ODBC.

Is this a bug in 1.2.x?

How to fix “Not associated with trusted SQL Server” error

I was unable to get this to work using Windows Authentication in the SQL Server database. I needed to set my database server to “SQL Server and Windows” authentication. Then I created a user in Enterprise Manager using Tools/Wizards (Database) “Create Login Wizard.” I set my database.yml file to use that user.

SQL Server 2000 error with Rails 1.2.3

I get the same error:
C:\www\demoapp>ruby script\server
=> Booting WEBrick…
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/abstrac
t/connection_specification.rb:204:in `establish_connection’: development database is not configu
red (ActiveRecord::AdapterNotSpecified) from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_ada
pters/abstract/connection_specification.rb:195:in `establish_connection’ from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:229:in `initialize_da
tabase’ from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:88:in `process’ from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:43:in `send’ from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:43:in `run’ from ./script/../config/../config/environment.rb:13 from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_requi
re’ from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require’ ... 9 levels… from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/commands/server.rb:39 from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_requi
re’ from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require’ from script/server:3

trying to connect to SQL 2000 using rails 1.2.3

Is this information summarized somewhere ?

As of July 13, 2007, what it the actual state of using Rails 1.2 with SQL Server 2005?

There’s a lot of info above, but it’s confusing.

DBI and ODBC and Sql Native Client

I don’t know about Rails specific issues since I’m just using DBI directly. The easiest way I’ve found to connect is using ODBC. I just use windows Admin Tools ODBC Connections to configure the ODBC connection. I tell it to use SQL Native Client since I have it. Then it is easy to specify the connection as “DBI:ODBC:connection_name” where connection_name is the name I specified in the windows ODBC configuration.

SQL Server 2005: Transactions Inside Migrations Do Not Work Correctly When Using SQL Native Client (SQLNCLI)

Using Rails 1.2.3 connecting to SQL Server 2005, I noticed that transactions inside of migrations were not rolling back correctly when an error occurred. I switched the provider to SQLOLEDB and transactions began to work. I followed the steps described above to install the ADO stuff and modify the SQL Server adapter. The following is an example of the database configuration in my database.yml file.


development:
  adapter: sqlserver
  database: <your db>
  username: sa
  password: password
  host: .
  mode: ADO
  provider: SQLOLEDB

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.

Starting with Rails 2.0 the ActiveRecord sqlserver adapter is no longer included by default and must be installed separately:

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

Rails 1.X (Rails 2.X needs to do this, too)
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

Then in your database.yml

For Rails 2.X:

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

For Rails 1.X:

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!

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

Any idea on this? .......After several trials I got the next solution:

I got and copy the last Ruby-DBI gem and ADO.rb file as indicated in the beginning of this article.
In MY CASE I did NOT use the parameter
”host: DBI:ADO:Provider=SQLOLEDB;Data Source=server_name;Initial Catalog=database_name;User Id=user_name;Password=your_pw_here;” that appears in the databe.yml file <= Every time I use it I got the same OLE error code.

Instead I used a little changed format in my database.yml file, here is mine for reference:


development:
  adapter: sqlserver
  database: demoPba0_development
  host: LAP-2F89FF92BC
  username: sa
  password: password1

test:
  adapter: sqlserver
  database: demoPba0_test
  host: LAP-2F89FF92BC
  username: sa
  password: password1

production:
  adapter: sqlserver
  database: demoPba0_production
  host: LAP-2F89FF92BC
  username: sa
  password: password1

Where:
-LAP-2F89FF92BC is the name of my machine.

Also is important to ensure to include a colon ’:’and a space ’ ’ after the name of each parameter, for instance:

“username: sa” is right
“username:sa” is not recommended

Best Regards !!!

Configuring SQL Server for SQL Server Authentication

(You can check the SQL Server log after a failed login attempt (depending on those Security Properties) to see what the problem is.)

Then, in the treeview, select Security/Logins, right-click the list view, choose New Login…, and set it up for SQL Server Authentication.

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

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)


  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.

Note for ActiveRecord 1.15:

The select method has changed, so you have to change

row_hash[handle.column_names[i]] = value

to this

row_hash[handle.column_names[i].downcase] = value

Also, in the columns method, you’re looking for this:


SELECT
cols.COLUMN_NAME as ColName

and changing it to


SELECT
LOWER(cols.COLUMN_NAME) as ColName

Further fix required when lowercasing column names

You need to make the following further fixes to avoid unexpected behaviour:

In the insert method:

  id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"]

to:

  id_value || select_one("SELECT @@IDENTITY AS ident")["ident"]

In the update method:

  end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]

to:

  end || select_one("SELECT @@ROWCOUNT AS affectedrows")["affectedrows"]

In the add_limit_offset! method:

  total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i

to:

  total_rows = @connection.select_all("SELECT count(*) as totalrows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:totalrows].to_i

One symptom of the problem is the ActiveRecord::Base#create method returning nil ids.

Avoiding the \NoMethodError problem

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

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

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.

There is a patch in the Rails bug-tracker that enables you to set up your connection in database.yml like so:

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

and have sqlserver_adapter.rb build the connect string appropriately. To help get this feature into the next version of rails, check out ticket 8862.

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

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)

SQL Server 2005 Express

I am using the example from the database.yaml at the top of this page and am getting the following error when I try to start the WEBrick server (using Rails 1.2.2):

I am able to connect to the database from a non-Active Record Ruby application.

I am also getting the same error if I try to use ODBC.

Is this a bug in 1.2.x?

How to fix “Not associated with trusted SQL Server” error

SQL Server 2000 error with Rails 1.2.3

trying to connect to SQL 2000 using rails 1.2.3

Is this information summarized somewhere ?

As of July 13, 2007, what it the actual state of using Rails 1.2 with SQL Server 2005?

There’s a lot of info above, but it’s confusing.

DBI and ODBC and Sql Native Client

SQL Server 2005: Transactions Inside Migrations Do Not Work Correctly When Using SQL Native Client (SQLNCLI)


development:
  adapter: sqlserver
  database: <your db>
  username: sa
  password: password
  host: .
  mode: ADO
  provider: SQLOLEDB

Created on January 25, 2008 09:42 by Anonymous Coward (88.97.245.57)

Ruby on RailsHowtoConnectToMicrosoftSQLServer (Version #295)

Configuring SQL Server for SQL Server Authentication

Making Rake’s database tasks work (yawn)

Lowercasing it all (by RonLusk)

Avoiding the \NoMethodError problem

Newbie’s experiences with SQLServer 2005 Beta 2

Another newbie’s experience with SQL Server 2005 final

How to go about using Trusted Security

How to fix db_schema_dump and stored procedures

Question about the above fix

Comment about the above fix

Additional comment on the above fix

Using the above fix only for db:schema:dump

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

Connect via ODBC

SQL Server 2005 Express

Configuring SQL Server for SQL Server Authentication

Making Rake’s database tasks work (yawn)

Lowercasing it all (by RonLusk)

Avoiding the \NoMethodError problem

Newbie’s experiences with SQLServer 2005 Beta 2

Another newbie’s experience with SQL Server 2005 final

How to go about using Trusted Security

How to fix db_schema_dump and stored procedures

Question about the above fix

Comment about the above fix

Additional comment on the above fix

Using the above fix only for db:schema:dump

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

Connect via ODBC

SQL Server 2005 Express

Ruby on Rails
HowtoConnectToMicrosoftSQLServer (Version #295)