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

22nd
Nov 2017
John Koisch
@jkoisch
Nov 22 2017 20:26

Hi folks ... want to say what a great job has been done. So helpful, great support. So thanks.

Inevitably, I have an issue ...

Rails 5.1.2 app is working against sql server deployed on aws ... in test, we are using sql server express, but in production, we are working against sql server (console says 12.00.4422.0.v1 but select @@version yields Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Jul 27 2015 16:56:19 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) )

Everything is ok in test (against sql server express) but switching to production getting error messages like

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

database adapter is set to
adapter: sqlserver

Relevant version info
Using activemodel 5.1.3
Using arel 8.0.0
Using activerecord 5.1.3
Using tiny_tds 2.0.0
Using activerecord-sqlserver-adapter 5.1.1

is this a known issue? Am I missing something obvious?

Ken Collins
@metaskills
Nov 22 2017 22:28
@Toreno96 No, it does not nor is intended to. That job is up to higher abstractions like Rails or Sequel gems.
That example mentions it but not in a way that says TinyTDS does it for you.
@jkoisch Looks like your production DB does not match local. My guess is that it is not 2012 or greater.
2012 is needed for modern Rails adapter since we use FETCH.
John Koisch
@jkoisch
Nov 22 2017 22:32

see comment above:

in production, we are working against sql server (console says 12.00.4422.0.v1 but select @@version yields Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Jul 27 2015 16:56:19 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) )

maybe I am missing something, but it looks like sqlServer 2014
Ken Collins
@metaskills
Nov 22 2017 22:32
That should be fine.
I wonder if that server is running in an old compatiblity mode.
John Koisch
@jkoisch
Nov 22 2017 22:33
thus my dilemma :-)
Ken Collins
@metaskills
Nov 22 2017 22:33
Have you check that?
John Koisch
@jkoisch
Nov 22 2017 22:33
I checked it yesterday ... I can't recall the enum, but for 2014 it is supposed to be '120' and our production server was '120'
Ken Collins
@metaskills
Nov 22 2017 22:34
Is there documentation around fetch and compat mode?
John Koisch
@jkoisch
Nov 22 2017 22:35
there could be ... I could not find anything per se. But I am not the most patient in trawling thru MSFT documentation
Ken Collins
@metaskills
Nov 22 2017 22:35
Indeed, nor I... I could not find anything off hand in a Google search on it.
Have you run a few manual queries with the connection to debug exactly what SQL it does not like
Assuming sp_executesql is fine.
But you could do a few queries to check.
ActiveRecord::Base.connection.raw_connection.execute(sql).do
John Koisch
@jkoisch
Nov 22 2017 22:41
Here is what I have done ... I have tried putting that derived query straight into SMS and running it ... there is the same problem. I have also tried to run different rails-flavored queries ... Location.first, Location.all, Location.find_by(blah) ... there are consistent problems. I picked this one because it seems foundational
Happy to go that route as well.
Ken Collins
@metaskills
Nov 22 2017 22:42
WOW, so same problem in SMS.
SELECT compatibility_level FROM sys.databases WHERE name = 'YOURDBNAME';
Can you double check that too? Just in case.
John Koisch
@jkoisch
Nov 22 2017 22:43
Our database is named something else, but yeah
i kiidd
Ken Collins
@metaskills
Nov 22 2017 22:43
LOL
John Koisch
@jkoisch
Nov 22 2017 22:44
per the latter ... it is 120
Ken Collins
@metaskills
Nov 22 2017 22:44
EXEC sp_executesql N'SELECT 1 AS [one]'
Does that work?
Hmm, I just noticed something... I dont see a * in this query.
John Koisch
@jkoisch
Nov 22 2017 22:46
i just noticed that too
Ken Collins
@metaskills
Nov 22 2017 22:46
EXEC sp_executesql N'SELECT [Locations]. FROM [Locations] OFFSET 0 ROWS FETCH NEXT 11 ROWS ONLY'
Assuming the 1 as one works... does that one above work with a [Locations].*
John Koisch
@jkoisch
Nov 22 2017 22:47

the '1' works ... but when I run this:

EXEC sp_executesql N'SELECT [Locations]. FROM [Locations] OFFSET 0 ROWS FETCH NEXT 11 ROWS ONLY'

in sms I get

Msg 153, Level 15, State 2, Line 1
Invalid usage of the option NEXT in the FETCH statement.

Ken Collins
@metaskills
Nov 22 2017 22:48
What ruby made that query?
John Koisch
@jkoisch
Nov 22 2017 22:48
2.4.1
Ken Collins
@metaskills
Nov 22 2017 22:49
I mean Rails... stuff like this Location.offset(10).first
Does Location.first work? Location.find
Or certain window functions? Offset/limit?
John Koisch
@jkoisch
Nov 22 2017 22:50
ActiveRecord::StatementInvalid: TinyTds::Error: Invalid object name 'locations'
when I run . ... Location.first
Ken Collins
@metaskills
Nov 22 2017 22:50
What is your model to that table?
Just assumed it was of the same name.
John Koisch
@jkoisch
Nov 22 2017 22:51

class Location < PlatformRecord
self.table_name = "locations"
self.primary_key = 'locationid'

belongs_to :application, :foreign_key => 'ApplicationId'
belongs_to :company, :foreign_key => 'CompanyId'
has_many :raw_entities, :foreign_key => 'LocationId'
end

PlatFormRecord just extends ActiveRecord::Base
Ken Collins
@metaskills
Nov 22 2017 22:52
Does PlatFormRecord have self.abstract_class = true in it?
John Koisch
@jkoisch
Nov 22 2017 22:52
yes
Ken Collins
@metaskills
Nov 22 2017 22:52
Also, I would use self.table_name = "Locations" in the Location model.
John Koisch
@jkoisch
Nov 22 2017 22:53
fwiw, i did try that ... also different flavors of dbo.Locations, dbo.locaitons, etc
but ... trying again :-)
Ken Collins
@metaskills
Nov 22 2017 22:53
Cool, I would keep it simple. Assume the user to log in is dbo so not needed.
Assuming that getting Location.first working is the first step.
John Koisch
@jkoisch
Nov 22 2017 22:54
no, i got rid of it.
Ken Collins
@metaskills
Nov 22 2017 22:54
And that name looked to be the reason why.
John Koisch
@jkoisch
Nov 22 2017 22:54
also remember, this ALL works against SqlServer Express ...
Ken Collins
@metaskills
Nov 22 2017 22:54
Needed to know if that worked.
Well, express could have that table name in lowercase for all I know :)
John Koisch
@jkoisch
Nov 22 2017 22:55
it doesn't
it has the same schema as the production db's ...
Ken Collins
@metaskills
Nov 22 2017 22:55
OK... I'm struggling to find out how you get the reported error when Location.first raises this error.
ActiveRecord::StatementInvalid: TinyTds::Error: Invalid object name 'locations'
John Koisch
@jkoisch
Nov 22 2017 22:56
ahhh, wait a second. SQL Express has compatibility level set to 110
yeah, i agree ... it is strange ... in the one, it can't seem to match the object to the table, but in Location.all, it shows some internal sql thing that has a problem w fetch, offset, and something neat '0'
again, i waited until my pain was high before posting here
Ken Collins
@metaskills
Nov 22 2017 22:58
OK... some helpful info... our Appveyor tests run against 2012 and 2014, all green. I assume they run vanillia installs for SQL Server. Other CI setups like Travis run on 2017 GA on Linux, same story.
So that means all you have to do is find out what is different about your DB or app. So easy :P
I would recommend testing a basic new Rails app to the DB.
To remove the possibility that some bundled gems in your app are conflicting.
We have had reports that some gems cause issues. composite primary keys for example.
But that would only be potential helpful if your Gemfile has production gems not loaded in dev.
If not, then it has to most likely be at the DB level.
Could be some settings configured on the prod server.
These are the ones we set assuming SQL Server defaults as the base.
But I would start with a basic new app to test one model first.
John Koisch
@jkoisch
Nov 22 2017 23:03
ok, i will follow that path and see ... thanks a lot
i will follow up if possible in case someone else runs into similar issues
Ken Collins
@metaskills
Nov 22 2017 23:03
Sure... sorry I could not see whats needed immediately.
John Koisch
@jkoisch
Nov 22 2017 23:03
thanks a lot for the time
Ken Collins
@metaskills
Nov 22 2017 23:04
Sure :)
John Koisch
@jkoisch
Nov 22 2017 23:04
btw, do you have any experience w the octopus gem?
Ken Collins
@metaskills
Nov 22 2017 23:05
Nope... I author this one...
Never needed sharding before like Octopus... but I would imagine it has to monkey patch Rails.
And that it could cause issues for the adapter.