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

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 :)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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!

On Wed, Jan 7, 2009 at 11:01 AM, Ben
<benperry-KuiJ5kEpwI4qdlJmJB21zg@public.gmane.org>
wrote:
>
> 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 :)
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 :)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On Thu, Jan 8, 2009 at 10:41 AM, Ben
<benperry-KuiJ5kEpwI4qdlJmJB21zg@public.gmane.org>
wrote:
>
> 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 :)
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Mon, Jan 12, 2009 at 7:39 AM, Ben
<benperry-KuiJ5kEpwI4qdlJmJB21zg@public.gmane.org>
wrote:
>
> 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.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Yup, thats correct.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---