1

I'm using rails 5.1.6 with activerecord-sqlserver-adapter 5.1.6, tiny_tds 2.1.2. and SQL Server 2017.

Databaseaccess is OK,

SQL (1.1ms) USE [MIST_Test]

User Load (1.5ms) EXEC sp_executesql N'SELECT [users].* FROM [users] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]]

ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '0'.: EXEC sp_executesql N'SELECT [users].* FROM [users] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1

from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/activerecord-sqlserver-adapter-5.1.6/lib/active_record/connection_adapters/sqlserver/database_statements.rb:368:in `each'

Caused by TinyTds::Error: Incorrect syntax near '0'.

from C:/Ruby25-x64/lib/ruby/gems/2.5.0/gems/activerecord-sqlserver-adapter-5.1.6/lib/active_record/connection_adapters/sqlserver/database_statements.rb:368:in `each'

SQL Server needs an 'ORDER BY' to use the 'OFFSET' clause.

Can somebody help?

tryingToLearn
  • 10,691
  • 12
  • 80
  • 114
user322958
  • 23
  • 4
  • 1
    Can you explain a bit more about the query you are firing and exact use case? – Charles Skariah Mar 20 '19 at 07:39
  • In my rails user controller i am trying to find first record by using User.first its throws an error as shown below.EXEC sp_executesql N'SELECT [users].* FROM [users] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 – user322958 Mar 20 '19 at 07:52
  • Is it specifically for User.first query or all queries? Can you please try User.all ? – Charles Skariah Mar 20 '19 at 07:57
  • User.all is displays all records .currently we are upgrading rails 3.2 ruby(1.9.3) to rails 5.1.6(ruby2.5). rails active-record session store its always throws offset issues. Our database is sql server 2017 with group managed account.adapter: sqlserver #username: XXXXX$ host: localhost Trusted_Connection: yes Integrated Security: true; – user322958 Mar 20 '19 at 08:06
  • I have the same problem - went from Rails 4.2 / SQL Server 2008 to Rails 5.2 / SQL Server 2016 and get exactly the same error. User.find(1) => ActiveRecord::StatementInvalid (TinyTds::Error: Incorrect syntax near 'OFFSET'.: EXEC sp_executesql N'SELECT [Users].* FROM [Users] WHERE [Users].[idUsers] = @0 ORDER BY [Users].[idUsers] ASC OFFSET 0 ROWS FETCH NEXT @1 ROWS ONLY', N'@0 int, @1 int', @0 = 1, @1 = 1) Interestingly when I connect to SQL Server 2016 with Rails 4.2 this error goes away, so it's likely related to a change in the ActiveRecord / SQLServer Adapter or tinytds layer. – patschiboy Nov 05 '19 at 00:58

0 Answers0