Problems with SQL Server 2008 and migrations using activerecord-odbc-adapter gem

Rails
1

Can any one else please help me to confirm that migrations are broken
against SQL Server 2008? I’m running Rails 2.2.2 and activerecord-odbc-
adapter 2.0 gem. I’ve created a system DSN to my test database using
an authenticated connection.

I’ve created a single migration to make a table with a few columns in
it and when you run the ‘rake db:migrate’ the first time, everything
works correctly. However, if you run it again (which shouldn’t change
anything) I get the following error

'rake aborted!
S0001 (2714) [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named ‘schema_migrations’ in the database.

I think the ‘activerecord-odbc-adapter’ gem isn’t compatible with SQL
Server 2008 as I’ve tested the same migration against SQL Server 2000
and that seems to work fine. Does anyone else know of another gem/
plugin that allows authenticated connections to SQL Server 2008 that
works properly?

Thanks :slight_smile:

2

Have you tried using this instead?

We don’t use the activerecord-odbc adapter for our apps. We also
install ruby-odbc and ruby dbi from source rather than from gems, but
I hear that gems work fine

$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4

I can’t say if this works on 2008 because I’ve been too busy to test
it, so if it doesn’t, I apologize. However, I’ve monkeyed with the
sqlserver adapters in the past and patching it should be trivial.

Let us know how it goes!

3

Once I figured out how to use it, it usefully tells you its only
supported for SQL Server 2000 and 2005 databases.

rake db:migrate
rake aborted!
Currently, only 2000 and 2005 are supported.

So it looks like I’m on my own to try and fix activerecord-odbc-
adapter joy!

Unless any else knows of yet another adapter…?

Thanks :slight_smile:

4

It shouldn’t be hard to remove that restriction from the code :slight_smile: I
wonder what would happen? I don’t have sql2k8 around but I’d love to
hack on it.

5

Ok. After further investigation it turns out there isn’t a problem
with the adapter, apart from lack of obvious documentation.

The adapter detects if the SQL Server database uses ‘security
schemas’ (SQL Server 2005/08) and if it does it enforces those schemas
which was the cause of my problems. I’d advise that when creating a
SQL Server 2005/08 database to be used with Rails, you create a
security schema named identically to the user that is going to be
running the migrations and assign it to that user; note, this should
be done before running any migrate tasks. If I’m correct, the result
of this is that ONLY that user can run migrations properly.

Under most circumstances a user is assigned the ‘dbo’ schema. So I ran
the migrate for the first time and the tables were associated with the
dbo schema. The second time I ran the migrate the schemas are enforced
and because of this and the way the adapter works, it wasn’t able to
properly build the list of ‘already existing tables’. So because it
hadn’t detected the existing tables, it tried to create them again and
that is where the second migration was falling over.

6

That’s really good to know and it makes sense. To verify, this is with
the activerecord-odbc-adapter, correct?

7

Yup, thats correct.

8

Huh, good post, I was having the same issue approx the same time
(There is already an object named 'schema_migrations' in the - Rails - Ruby-Forum), wish I had found your
post then.

I’m on a SqlServer 2005 db, but it was the same exact issue. Ended up
switching to the rails-sqlserver-2000-2005-adapter and most problems
went away. But good to know the actual cause of the original issue.

Thanks!

-Ben