# Problems with SQL Server 2008 and migrations using activerecord-odbc-adapter gem **URL:** \ **Category:** Rails\ **Created:** [January 7, 2009, 6:02pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135 "2009-01-07T18:02:01Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 7, 2009, 6:02pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/1 "2009-01-07T18:02:01Z")
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 🙂 ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 7, 2009, 6:15pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/2 "2009-01-07T18:15:38Z")
Have you tried using this instead? > **[GitHub - rails-sqlserver/2000-2005-adapter: Old Home For The ActiveRecord...](https://github.com/rails-sqlserver/2000-2005-adapter/tree/master)** > > 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! ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 8, 2009, 5:42pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/3 "2009-01-08T17:42:42Z")
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 🙂 ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 8, 2009, 7:59pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/4 "2009-01-08T19:59:53Z")
It shouldn’t be hard to remove that restriction from the code 🙂 I wonder what would happen? I don’t have sql2k8 around but I’d love to hack on it. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 12, 2009, 2:40pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/5 "2009-01-12T14:40:19Z")
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. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 12, 2009, 4:31pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/6 "2009-01-12T16:31:33Z")
That’s really good to know and it makes sense. To verify, this is with the activerecord-odbc-adapter, correct? ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [January 13, 2009, 9:00am UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/7 "2009-01-13T09:00:53Z")
Yup, thats correct. ---
**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\ **Post date:** [February 9, 2009, 10:39pm UTC](https://www.ruby-forum.com/t/problems-with-sql-server-2008-and-migrations-using-activerecord-odbc-adapter-gem/157135/8 "2009-02-09T22:39:29Z")
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](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