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

rubyonrails-talk
1

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!

2

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.

3

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.

4

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

5

Yup, thats correct.

6

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