These are chat archives for rails-sqlserver/activerecord-sqlserver-adapter

23rd
Nov 2017
John Koisch
@jkoisch
Nov 23 2017 00:15

ok ... can confirm an issue>>

Location.all
Location Load (65.1ms) EXEC sp_executesql N'SELECT [Locations].* FROM [Locations] ORDER BY [Locations].[LocationId] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 [["LIMIT", nil]]

That executes correctly. It is using a stripped down rails application to connect to a sqlserver express db configured as above w compatibility set to 110. It fetches all rows correctly.

Changing the database to a sqlserver 2014 database configured as above w compatibility set 120, we see these errors

Location.all
Location Load (168.9ms) EXEC sp_executesql N'SELECT [Locations]. FROM [Locations] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 [["LIMIT", nil]]
ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '0'.: EXEC sp_executesql N'SELECT [Locations].
FROM [Locations] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11

irb(main):002:0> Location.first
Location Load (188.9ms) EXEC sp_executesql N'SELECT [Locations]. FROM [Locations] ORDER BY [Locations].[locationid] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]]
ActiveRecord::StatementInvalid: TinyTds::Error: Invalid object name 'Locations'.: EXEC sp_executesql N'SELECT [Locations].
FROM [Locations] ORDER BY [Locations].[locationid] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1

i notice that the actual SQL that gets written is different for Location.all ... there is no ORDER BY clause

and note that when I use

irb(main):005:0> Location.order('LocationId').all
Location Load (206.6ms) EXEC sp_executesql N'SELECT [Locations]. FROM [Locations] ORDER BY LocationId OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 [["LIMIT", nil]]
ActiveRecord::StatementInvalid: TinyTds::Error: Invalid object name 'Locations'.: EXEC sp_executesql N'SELECT [Locations].
FROM [Locations] ORDER BY LocationId OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11

Ken Collins
@metaskills
Nov 23 2017 13:22
@jkoisch Thanks. From here I would focus on...
Location.columns
That will show the DB reflection AR column objects.
I bet they would be different in each env.
Which might come back to a perms issue with the user to connect to prod and/or how that DB or specific tables are setup with a certain user.