# MS SQL query strangeness for ActiveRecord in Rails **URL:** \ **Category:** Rails\ **Created:** [March 30, 2006, 5:26pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368 "2006-03-30T17:26:13Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 30, 2006, 5:26pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/1 "2006-03-30T17:26:13Z")
Hi, I am currently trying to move my rubyonrails app from Linux to Windows server utilizing MS SQL instead of MySQL due to “business” reason. All my listing screen utilize a drop down list in each column to allow user to “filter” the listing based upon the value chosen from the drop down list. In the controller, I used find() method but with customized parameters such as the following: ``` Member.find( :all, :select => "members.id as id, members.last_name, members.first_name, members.phone, members.company_id, companies.name", :order => @sort, :joins => ", companies, engagements_members, engagements", :conditions => [@cond_str] + @cond_params, :limit => @user.rows_per_page, :group => 'members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name', :offset => @page * Integer(@user.rows_per_page) ) ``` While this perfectly ok with MySQL, MS SQL choked on this. And the sqlserver\_adapter seems to generic a rather strange looking query which causes the error as the following:- ``` Exception occurred.: SELECT * FROM (SELECT TOP 7 * FROM (SELECT TOP 7 members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name FROM members , companies, engagements_members, engagements WHERE (members.company_id = companies.id AND engagements_members.member_id = members.id AND engagements_members.engagement_id = engagements.id) GROUP BY members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name ORDER BY members.last_name, members.first_name, members.phone, companies.name ) AS tmp1 ORDER BY members.last_name DESC, members.first_name DESC, members.phone DESC, companies.name DESC) AS tmp2 ORDER BY members.last_name, members.first_name, members.phone, companies.name ``` Does anyone has any suggestion/pointer or other online resource that can help me out here? I am trying to avoid direct SQL (so no find\_by\_sql()) hoping to keep the code portable for different database. Thanks in advance. ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 30, 2006, 9:18pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/2 "2006-03-30T21:18:10Z")
Look like MS SQL 2000 doesn’t support OFFSET like MySQL and PostgreSQL does. This is really a bummer… Anyone experience the same problem and figure out a workaround? ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 30, 2006, 5:28pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/3 "2006-03-30T17:28:41Z")
Sorry, typo voice… > While this is perfectly ok with MySQL, MS SQL choked on this. And the > sqlserver\_adapter seems to generic a rather strange looking query which generic =\> generate ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 31, 2006, 2:00am UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/4 "2006-03-31T02:00:03Z")
Hmm… I think the problem lies with the sqlserver driver which uses top twice (each with different sorting order) to emulate the offset feature. In the process of this “emulation”, it uses generic table name tmp1, and tmp2. by the order by is still assuming what I have given in the :order\_by which also includes the original table name. And hence MS SQL of course won’t like it because the “AS tmp1” and “AS tmp2” already rename table. So the solution is use unique name for columns or at least use alias during the SQL to ensure unique column name… ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 31, 2006, 9:41pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/5 "2006-03-31T21:41:56Z")
Chester C. wrote: > Hmm… I think the problem lies with the sqlserver driver which uses top > twice (each with different sorting order) to emulate the offset feature. > In the process of this “emulation”, it uses generic table name tmp1, and > tmp2. by the order by is still assuming what I have given in the > :order\_by which also includes the original table name. And hence MS SQL > of course won’t like it because the “AS tmp1” and “AS tmp2” already > rename table. So the solution is use unique name for columns or at least > use alias during the SQL to ensure unique column name… OK. I have a fixed for sqlserver\_adapter.rb to address this issue. Can someone please tell me what do I need to do to have the fix in the rails distribution? TIA. ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 31, 2006, 11:43pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/6 "2006-03-31T23:43:42Z")
Chester C. wrote: > Hmm… I think the problem lies with the sqlserver driver which uses top > twice (each with different sorting order) to emulate the offset feature. > In the process of this “emulation”, it uses generic table name tmp1, and > tmp2. by the order by is still assuming what I have given in the > :order\_by which also includes the original table name. And hence MS SQL > of course won’t like it because the “AS tmp1” and “AS tmp2” already > rename table. So the solution is use unique name for columns or at least > use alias during the SQL to ensure unique column name… Alternative way without touch any db driver is not use the :limit and :offset. Instead, use slice() on the return array. ---
**Author:** ![Chester\_C](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ce73a5/32.png) [@Chester\_C](https://www.ruby-forum.com/u/Chester_C)\ **Post date:** [March 31, 2006, 9:55pm UTC](https://www.ruby-forum.com/t/ms-sql-query-strangeness-for-activerecord-in-rails/56368/7 "2006-03-31T21:55:17Z")
Chester C. wrote: > Chester C. wrote: > > > Hmm… I think the problem lies with the sqlserver driver which uses top > > twice (each with different sorting order) to emulate the offset feature. > > In the process of this “emulation”, it uses generic table name tmp1, and > > tmp2. by the order by is still assuming what I have given in the > > :order\_by which also includes the original table name. And hence MS SQL > > of course won’t like it because the “AS tmp1” and “AS tmp2” already > > rename table. So the solution is use unique name for columns or at least > > use alias during the SQL to ensure unique column name… > > OK. I have a fixed for sqlserver\_adapter.rb to address this issue. Can > someone please tell me what do I need to do to have the fix in the rails > distribution? TIA. nevermind… found it…