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

Have you tried using this instead?

http://github.com/rails-sqlserver/2000-2005-adapter/tree/master

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!

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.

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.

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

Yup, thats correct.

Huh, good post, I was having the same issue approx the same time
(http://www.ruby-forum.com/topic/176528#773255), 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