ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near 'OFFSET'

[daveomcd]

Setup

rails 5.1.6 activerecord 5.1.6 activerecord-sqlserver-adapter 5.1.6 tiny_tds 2.1.1 freetds 1.00.27 TDS version 7.3 SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

[TinyTds][v1.3.0][tsql]: /home/daveomcd/.rvm/gems/ruby-2.3.3/gems/tiny_tds-1.3.0/exe/tsql
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.27
             freetds.conf directory: /home/daveomcd/.rvm/gems/ruby-2.3.3/gems/tiny_tds-1.3.0/ports/x86_64-pc-linux-gnu/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: no

Description

I'm having a issue where I'm getting the following error:

ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near 'OFFSET'.: EXEC sp_executesql N'SELECT [non_pro_db_college_player_extracts].* FROM [non_pro_db_college_player_extracts] INNER JOIN [ncaa_matches] ON [non_pro_db_college_player_extracts].[college_player_id] = [ncaa_matches].[non_pro_db_college_player_extract_id] WHERE [ncaa_matches].[player_id] = @0 OFFSET 0 ROWS FETCH NEXT @1 ROWS ONLY', N'@0 int, @1 int', @0 = 257000, @1 = 11

It happens when I try to access the non_pro_db_college_player_extracts association from a player record.

Example:

player = Player.first
player.non_pro_db_college_player_extracts.any? # errors out here

There is a unique case with the "non_pro_db_college_player_extracts" table. It is actually a view that lives in a database that's outside of my application database. I have it's model and association setup as follows:

class RavensIncomingModel < ApplicationRecord
  self.abstract_class = true
  self.table_name_prefix = "RavensIncoming.dbo."
  establish_connection "ravens_incoming_#{Rails.env}".to_sym
end

class NonProDbCollegePlayerExtract < RavensIncomingModel
  self.table_name   = 'non_pro_db_college_player_extracts'
  self.primary_key  = 'college_player_id'

  has_many :ncaa_matches, foreign_key: "player_id"
  has_many :players, through: :ncaa_matches

  public

end

The ncaa_matches table is a simple "join table" that does exist on the applications database. I'm unsure what else I can add, but if you think of anything please let me know and I'll add it. Thanks!

[metaskills]

I think that SQL looks fine. I would do a test in SMS to see if that 2012 supports OFFSET/FETCH. It could be that it is running in an compatibility mode and that feature is turned off?

[daveomcd]

@metaskills I'm looking through google and noticed someone on StackOverflow mentioning that OFFSET/FETCH needs an "order by". Not sure if that is true or not, but by adding an order by to my statement in SSMS it does make it work.

EXEC sp_executesql N'SELECT [non_pro_db_college_player_extracts].* FROM RavensIncoming.dbo.[non_pro_db_college_player_extracts] INNER JOIN [ncaa_matches] ON [non_pro_db_college_player_extracts].[college_player_id] = [ncaa_matches].[player_id] WHERE [ncaa_matches].[player_id] = @0 ORDER BY [ncaa_matches].[player_id] OFFSET 0 ROWS FETCH NEXT @1 ROWS ONLY', N'@0 int, @1 int', @0 = 257000, @1 = 11

[daveomcd]

Here's a link to where I'm finding the information: https://stackoverflow.com/a/30322555/394241

[daveomcd]

@metaskills If I were going to take a crack at correcting this issue, would it likely be a correction to tiny-tds or active-record-sqlserver?

[metaskills]

Ah, this would be adapter related. It could be that NonProDbCollegePlayerExtract.primary_key is coming back nil for some reason deep down in the adapter Arel visitor. https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/arel/visitors/sqlserver.rb#L203-L208

I'd do some console debugging. This is the info you need to debug/share.

NcaaMatches.primary_key # => ...
NcaaMatches.columns_hash['player_id'] # => 

[daveomcd]

The information requested for sharing is below. Also I looked on NonProDbCollegePlayerExtract for any records with a nil value for my primary key of college_player_id, and there were no nil values.

NcaaMatch.primary_key # => "id"
NcaaMatch.columns_hash['player_id'] # => #<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x0055b950133490 @sqlserver_options={:ordinal_position=>2, :is_primary=>false, :is_identity=>false}, @name="player_id", @table_name="ncaa_matches", @sql_type_metadata=#<ActiveRecord::ConnectionAdapters::SQLServer::SqlTypeMetadata:0x0055b950133620 @sqlserver_options={:sqlserver_options=>{:ordinal_position=>2, :is_primary=>false, :is_identity=>false}}, @sql_type="int(4)", @type=:integer, @limit=4, @precision=nil, @scale=nil>, @null=true, @default=nil, @default_function=nil, @collation=nil, @comment=nil> 

[metaskills]

How about the output from NcaaMatch.columns_hash['id']

[daveomcd]

Output for NcaaMatch.columns_hash['id']

#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x0055b950133828 @sqlserver_options={:ordinal_position=>1, :is_primary=>true, :is_identity=>true}, @name="id", @table_name="ncaa_matches", @sql_type_metadata=#<ActiveRecord::ConnectionAdapters::SQLServer::SqlTypeMetadata:0x0055b9501339e0 @sqlserver_options={:sqlserver_options=>{:ordinal_position=>1, :is_primary=>true, :is_identity=>true}}, @sql_type="int(4)", @type=:integer, @limit=4, @precision=nil, @scale=nil>, @null=false, @default=nil, @default_function=nil, @collation=nil, @comment=nil>

[metaskills]

Hmm... so I just noticed I am missing some model examples. I see Player too and I never saw the code for NcaaMatch. May I suggest that ActiveRecord is not having a good time getting info from NonProDbCollegePlayerExtract on the primary key. You can do a bundle open and puts some debuggers (puts) here if you want to see.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/arel/visitors/sqlserver.rb#L175-L177

Hard for me to dig in your code with comments... and offer good advice, but have you tried adding an order (for default) to he association to help ActiveRecord define one vs looking for the PK on that view?

[daveomcd]

@metaskills , ahh my apologies. And yes I was hoping it wouldn't have taken this much work on your part helping me track down the issue. Adding the following to my model resolved this issue for me:

default_scope { order(player_id: :desc) }

I will also attempt to debug the issue without that line to see if there is a way of figuring out the underlying issue. Thanks a ton for all your assistance with this, it's been a very big help.

[metaskills]

Another option would be to add the order to the association vs the model as a whole. I'm sure it all comes down to both the view and the schema information for the column objects not finding the primary key. Most join tables are just two foreign keys. Other areas to play with... sometimes the adapter has to dig down with sp_helptext stored procedure to get view information. I've seen issues where the app user in production does not have the ability to leverage that store procedure. Search the adapter issues for related issues around view and that proc and you might find some matches.

[marcelo-rebello]

I had a similar error that takes almost a week to solve... Looks like it was related to user permissions on SQL server. I use the same user for different projects and for the one that was given the "OFFSET error" the user had no default schema set on DB. After I change this and grant db_owner permission to the user, everything starts working just fine...

[jeremysenn]

I have an issue that seems somewhat similar to this, but a bit more peculiar. We recently upgraded to SQL Server 2019, and now have some problems properly connecting to the database.

tiny_tds: 2.1.2 activerecord-sqlserver-adapter: 6.0.0

The first issue that we noticed the was the "Incorrect syntax near 'OFFSET'" when doing any searches of the database in Ruby (e.g. Image.find(1) ).

Upon further investigation we found the following:

Image.columns_hash => {}

However, we have no problem listing columns via the console when when using the tds client directly:

results = client.execute("SELECT * FROM INFORMATION_SCHEMA.COL MNS WHERE TABLE_NAME = N'images'")

From the client in the console the compatibility level is correct:

{"compatability_level" => 150}

However, client.tds_version_info gives:

"DBTDS_7_3 - Microsoft SQL Server 2008"

Any help would be greatly appreciated!

[jeremysenn]

We changed the default schema for the user to dbo, which seems to have solved the issue at this point.

[ogiogiovictor]

"exception": "#<ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near 'OFFSET'.>", in rails that is the error i am still getting using mssql server and will_paginate

[ogiogiovictor]

We changed the default schema for the user to dbo, which seems to have solved the issue at this point.

please i need your help