# Is anyone running Rails 2.x against a MS SQL Server DB?

**URL:** <https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994>\
**Category:** Rails\
**Created:** [March 14, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994 "2008-03-14T22:36:17Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 14, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/1 "2008-03-14T22:36:17Z")

</div>

I am unable to get ActiveRecord session support to work under 2.x  
against a SQL Server database. I’m starting to wonder if anyone is  
running 2.x against SQL Server?

Is anyone running under the following configuration:

Rails 2.x  
SQL Server 2000 DB (using AR adapter in ODBC mode)  
AR store for ActionController session store.

I’d like to chat with you if you are.

Thanks,  
Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 14, 2008, 10:48pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/2 "2008-03-14T22:48:48Z")

</div>

hey Wes,  
I will be running against a sql server 2000 or 2005, but I am still in  
development. So I will keep an eye out for any information that you  
find out.

Thanks,

Rob

On Mar 14, 2:36 pm, Wes G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 14, 2008, 11:33pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/3 "2008-03-14T23:33:12Z")

</div>

Well, I’ve done some digging into the guts of the active record store  
(actionpack-2.0.2\lib\action\_controller\session\active\_record\_store.rb).

I’ve verified the following:

1. Nothing is being saved to my session table in the DB.

2. The update method is being called, which in turn attempts to call  
@session.save. Nothing happens when the save method is called  
(whichever method it is), and so my session record is not being saved.

3. The standard save method in AR::Base is not being called. I am  
unable to figure out where the “save” method that’s being called  
actually lives.

4. The session object is a CGI::Session::ActiveRecordStore::Session  
class and it is a descendent of AR::Base.

(Is there a way to figure out which method is being called  
programmatically, without manually analyzing the inheritance tree of  
classes and/or modules?)

So far, this evidence is internally consistent. If AR::Base.save is not  
being called on this object, then the session won’t be saved.

But the question is what happens in this method in  
active\_record\_store.rb:

def update  
if @session  
ActiveRecord::Base.silence { @session.save }  
end  
end

?

What is @session.save calling? BTW, I’ve already gone through the  
exercise of removing the ActiveRecord::Base.silence block and doing  
@session.save! instead in order to see an exception get raised. I see  
nothing when I do that.

It’s as though the save method is empty somewhere and does nothing.

Thanks for any help,  
Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 14, 2008, 11:10pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/4 "2008-03-14T23:10:33Z")

</div>

I develop against MS SQL Server 2000 db’s all the time.

My company has a whole bunch of legacy db’s that are SQL Server 2000  
db’s. So far, I have not had a problem with the SQL Server AR  
adapter, with FreeTDS, in ODBC mode with Rails 2.x.

But, then again, we don’t do database session store. We use the Rails  
2.x default cookie based session store. So, you may have uncovered a  
bug. However, that seems unlikely. The SQL Server AR adapter was  
around before Rails 2.x. And, prior to Rails 2.x, I was using db  
session store.

The problems I do run into are not really with AR or SQL Server. But,  
rather, I have to deal with a lot of legacy/non-Rails-esque database  
designs. Things like non-surrogate PK’s, composite PK’s, weird FK  
relationships, using PK’s that contain periods and “@'s” . . . just  
about every weird thing you can think of . . . I’ve come across.

The good thing is that so far, I have been able find workarounds for  
all these situations.

If you can be a little bit more specific with you problem, maybe post  
some error messages or something, I would be happy to help you out.

On Mar 14, 4:36 pm, Wes G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 15, 2008, 7:30am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/5 "2008-03-15T07:30:30Z")

</div>

Here is some script/console output. We see that the session is in fact  
an ActiveRecord object, hooked up to the sessions table, but that saving  
the session appears to do nothing. No data end up in the database, and  
notice how session.new\_record? is still true after the save.

Thanks,  
Wes

C:\eclipse\workspace\eSimplyOnlineRails\>ruby script/console  
Loading development environment (Rails 2.0.2)

> > session = CGI::Session::ActiveRecordStore::Session.new  
> > =\> #\<CGI::Session::ActiveRecordStore::Session id: nil, session\_id: nil,  
> > data: ni  
> > l, updated\_at: nil\>
> 
> > session.is\_a?(ActiveRecord::Base)  
> > =\> true
> 
> > session.class.table\_name  
> > =\> “sessions”
> 
> > session.new\_record?  
> > =\> true
> 
> > session[:test\_key] = 3  
> > =\> 3
> 
> > session.save  
> > =\> []
> 
> > session.save!  
> > =\> []
> 
> > session.new\_record?  
> > =\> true
> 
> > session[:test\_key]  
> > =\> 3

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 15, 2008, 5:47am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/6 "2008-03-15T05:47:26Z")

</div>

On Sat, Mar 15, 2008 at 9:33 AM, Wes G.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Well, I’ve done some digging into the guts of the active record store  
> (actionpack-2.0.2\lib\action\_controller\session\active\_record\_store.rb).
> 
> It’s as though the save method is empty somewhere and does nothing.

I used (don’t any more) MS Sql Server using session store.

Double check your table permissions on the SQL Server, maybe something  
is funky here.

Try going into the console and “Session.new” set some param and save  
it, see if it works.

Try using save! instead of save in the console test to raise an  
exception on failed save.

What does your test.log say?

Give us more details 🙂

Mikel

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 15, 2008, 8:01am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/7 "2008-03-15T08:01:34Z")

</div>

Here are the script/console actions:

C:\eclipse\workspace\eSimplyOnlineRails\>ruby script/console  
Loading development environment (Rails 2.0.2)

> > session = CGI::Session::ActiveRecordStore::Session.new  
> > =\> #\<CGI::Session::ActiveRecordStore::Session id: nil, session\_id: nil,  
> > data: ni  
> > l, created\_at: nil, updated\_at: nil\>
> 
> > session.new\_record?  
> > =\> true
> 
> > session[:test\_key] = 3  
> > =\> 3
> 
> > session.save  
> > =\> []
> 
> > session.save!  
> > =\> []

and here are the entries in the development.log file (note that all of  
this is output on the instantiation of the session, nothing gets written  
to the log after that).

e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns  
(0.000000)e[0m e[0;1m  
SELECT  
cols.COLUMN\_NAME as ColName,  
cols.COLUMN\_DEFAULT as DefaultValue,  
cols.NUMERIC\_SCALE as numeric\_scale,  
cols.NUMERIC\_PRECISION as numeric\_precision,  
cols.DATA\_TYPE as ColType,  
cols.IS\_NULLABLE As IsNullable,  
COL\_LENGTH(cols.TABLE\_NAME, cols.COLUMN\_NAME) as Length,  
COLUMNPROPERTY(OBJECT\_ID(cols.TABLE\_NAME), cols.COLUMN\_NAME,  
‘IsIdentity’) as IsIdentity,  
cols.NUMERIC\_SCALE as Scale,  
cols.ORDINAL\_POSITION as Position  
FROM INFORMATION\_SCHEMA.COLUMNS cols  
WHERE cols.TABLE\_NAME = ‘sessions’  
ORDER BY cols.ORDINAL\_POSITION  
e[0m  
MemCache Get (0.020000) parse\_tree\_refresh\_times  
MemCache Get (0.000000) list\_data\_refresh\_times  
e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns  
(0.000000)e[0m e[0;1m  
SELECT  
cols.COLUMN\_NAME as ColName,  
cols.COLUMN\_DEFAULT as DefaultValue,  
cols.NUMERIC\_SCALE as numeric\_scale,  
cols.NUMERIC\_PRECISION as numeric\_precision,  
cols.DATA\_TYPE as ColType,  
cols.IS\_NULLABLE As IsNullable,  
COL\_LENGTH(cols.TABLE\_NAME, cols.COLUMN\_NAME) as Length,  
COLUMNPROPERTY(OBJECT\_ID(cols.TABLE\_NAME), cols.COLUMN\_NAME,  
‘IsIdentity’) as IsIdentity,  
cols.NUMERIC\_SCALE as Scale,  
cols.ORDINAL\_POSITION as Position  
FROM INFORMATION\_SCHEMA.COLUMNS cols  
WHERE cols.TABLE\_NAME = ‘sessions’  
ORDER BY cols.ORDINAL\_POSITION  
e[0m  
MemCache Get (0.010000) parse\_tree\_refresh\_times  
MemCache Get (0.000000) list\_data\_refresh\_times  
e[4;36;1mCGI::Session::ActiveRecordStore::Session Columns  
(0.010000)e[0m e[0;1m  
SELECT  
cols.COLUMN\_NAME as ColName,  
cols.COLUMN\_DEFAULT as DefaultValue,  
cols.NUMERIC\_SCALE as numeric\_scale,  
cols.NUMERIC\_PRECISION as numeric\_precision,  
cols.DATA\_TYPE as ColType,  
cols.IS\_NULLABLE As IsNullable,  
COL\_LENGTH(cols.TABLE\_NAME, cols.COLUMN\_NAME) as Length,  
COLUMNPROPERTY(OBJECT\_ID(cols.TABLE\_NAME), cols.COLUMN\_NAME,  
‘IsIdentity’) as IsIdentity,  
cols.NUMERIC\_SCALE as Scale,  
cols.ORDINAL\_POSITION as Position  
FROM INFORMATION\_SCHEMA.COLUMNS cols  
WHERE cols.TABLE\_NAME = ‘sessions’  
ORDER BY cols.ORDINAL\_POSITION  
e[0m

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 15, 2008, 7:33am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/8 "2008-03-15T07:33:31Z")

</div>

On Sat, Mar 15, 2008 at 5:30 PM, Wes G.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Here is some script/console output. We see that the session is in fact  
> an ActiveRecord object, hooked up to the sessions table, but that saving  
> the session appears to do nothing. No data end up in the database, and  
> notice how session.new\_record? is still true after the save.

Weird,

What does your test / development log say about these actions?

Mikel

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 15, 2008, 8:59am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/9 "2008-03-15T08:59:06Z")

</div>

> and here are the entries in the development.log file (note that all of  
> this is output on the instantiation of the session, nothing gets written  
> to the log after that).

On windows I have sometimes had that the server won’t write to the log  
file if it gets too full sometimes and definately won’t if you have  
say another console or server open.

Close all your servers and consoles that could be writing to  
development.log. Delete or move the development.log and try again and  
see if there is any more output…

Mikel

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 17, 2008, 7:15pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/10 "2008-03-17T19:15:48Z")

</div>

I’m running 2.x against 2005, and all seems to be working fine. I’ve  
ended up modifying the connection adapter to suit my needs though.

I dont think you can set session in the console you’ll need to do it  
from a controller i.e.

class TestController \< ApplicationController  
def index  
session[:test\_key] = 3  
render :text =\> “setting test key to 3”  
end

def read  
render :text =\> session[:test\_key]  
end

end

This works as expected. Using the edge sql server adapter.

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 16, 2008, 9:02am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/11 "2008-03-16T09:02:39Z")

</div>

Some more information:

In an attempt to move my application configuration out of the picture,  
I generated a brand-new Rails project in 2.0.2 against SQL Server using  
ODBC mode for the adapter.

(database.yml below)

development:  
adapter: sqlserver  
mode: odbc  
username: reference  
password: reference  
dsn: reference

I created the schema\_info table, and generated the sessions migration  
using rake db:sessions:create, and ran it. So there are simply 2 tables  
in my database, schema\_info, and sessions. My environment.rb has

config.action\_controller.session\_store = :active\_record\_store

set.

Here is the transcript of a script/console session that attempts to save  
a session.

C:\eclipse\workspace\Rails\_2.0.2\_Reference\>ruby script/console  
Loading development environment (Rails 2.0.2)

> > session = CGI::Session::ActiveRecordStore::Session.new  
> > =\> #\<CGI::Session::ActiveRecordStore::Session id: nil, session\_id: nil,  
> > data: ni  
> > l, created\_at: nil, updated\_at: nil\>
> 
> > session[:test\_key] =3  
> > =\> 3
> 
> > session.save!  
> > ActiveRecord::RecordNotSaved: ActiveRecord::RecordNotSaved  
> > from  
> > c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active\_record  
> > /base.rb:1980:in `save_without_validation!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /validations.rb:944:in`save\_without\_transactions!’  
> > from  
> > c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active\_record  
> > /transactions.rb:112:in `save!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /connection_adapters/abstract/database_statements.rb:66:in`transaction’  
> > from  
> > c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active\_record  
> > /transactions.rb:80:in `transaction' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /transactions.rb:100:in`transaction’  
> > from  
> > c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active\_record  
> > /transactions.rb:112:in `save!' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record /transactions.rb:120:in`rollback\_active\_record\_state!’  
> > from  
> > c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active\_record  
> > /transactions.rb:112:in `save!’  
> > from (irb):3

The reason this appears to fail is that in this method in callbacks.rb,  
the call to “callback(:before\_save)” returns false.  
create\_or\_update\_with\_callbacks then returns false to  
AR::Base.save\_without\_validation! which then raises the RecordNotSaved  
exception

```
def create_or_update_with_callbacks #:nodoc:
  return false if callback(:before_save) == false
  result = create_or_update_without_callbacks
  callback(:after_save)
  result
end

```

While this may be useful, it is not totally related to my original  
issue, since I am unable to verify that the save method in AR::Base is  
being called when I attempt to save the session in my application. What  
I’ve just described above in a default app. is definitely going through  
the expected AR::Base.save code.

Having said that though, I feel like this cast aspersions on whether the  
AR session store works in SQL Server at all.

Can someone generate a brand-new project, and see if they can get a  
session to save using the AR store against SQL Server via ODBC?

Thanks,  
Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 17, 2008, 7:19pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/12 "2008-03-17T19:19:00Z")

</div>

Luke P. wrote:

> I’m running 2.x against 2005, and all seems to be working fine. I’ve  
> ended up modifying the connection adapter to suit my needs though.

Just to clarify that - In my project I’m using a modified sql server  
adapter.

I created a new test project, using rails 2.0.2 and the edge sql  
server adapter. I added that controller to get/set the session and it  
all worked properly.

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 19, 2008, 5:46pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/13 "2008-03-19T17:46:33Z")

</div>

Luke,

I see the same results in a fresh project using 2.0.2 and the 1.0 SQL  
server adapter.

Thanks for the test code.

I’ll post the problem with my other app. when I figure it out.

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 19, 2008, 11:40pm UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/14 "2008-03-19T23:40:00Z")

</div>

My problem appears to be related to the use of the object\_transactions  
plugin (see: [http://code.bitsweat.net/svn/object\_transactions/](http://code.bitsweat.net/svn/object_transactions/)), which  
in turn, relies on the transaction-simple gem.

Removing the object-transactions plugin allows my session to save  
correctly.

I haven’t figured out the exact reason yet, but I will and will post  
when I have it.

If anyone understands the guts of the object\_transactions plugin and/or  
the transaction-simple gem and would like to chime in, I’m all ears.

It’s incredibly frustrating to have moved to the object\_transactions  
plugin as an an effectively mandated part of the upgrade to Rails 2.0,  
and then have it totally not work with the ActiveRecord session store.

My faith in being able to upgrade a Rails app. and even hope for plugins  
to continue to work is extremely shaken.

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 20, 2008, 12:07am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/15 "2008-03-20T00:07:12Z")

</div>

Just one more thing to add some evidence to my claim.

If you install the object\_transactions plugin (and by association, the  
transaction-simple plugin) into a new Rails app. like the one that  
Luke mentioned above, you will see the same behavior - your AR session  
store will not be saved to/from anymore.

I am going to start a new post about this potential bug.

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 20, 2008, 1:29am UTC](https://www.ruby-forum.com/t/is-anyone-running-rails-2-x-against-a-ms-sql-server-db/131994/16 "2008-03-20T01:29:13Z")

</div>

I _think_ the final answer is here:

[http://www.ruby-forum.com/topic/146569](http://www.ruby-forum.com/topic/146569)

Wes