ActiveRecord::StatementInvalid Incorrect Syntax SQL Server 2012

[aaronkelton]

Hi 👋 I need to determine if what I'm seeing is a problem with how I've configured to use this adapter. Upgrading JRuby/Rails 4 to 5, and getting exceptions running queries in rails console. Here's a taste of what I'm seeing, and the configs I'm using. I'm happy to dive in if there's any bugs that need squashing, but it feels like I just don't have something configured correctly.

not recognizing attribute assignment

> u = User.new
#<User:0x4fa0ee7e> {}
=> nil
> u.first_name = 'Aaron'
Traceback (most recent call last):
        1: from (irb):8:in `evaluate'
NoMethodError (undefined method `first_name=' for #<User >)

User.first fails, but works with order clause (and only returns object notation)

> User.first
  User Load (8.0ms)  SELECT  [users].* FROM [users] OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY  [["LIMIT", 1]]
Traceback (most recent call last):
        1: from (irb):15:in `evaluate'
ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '0'.: SELECT  [users].* FROM [users] OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY)

> User.order(id: :asc).first
  User Load (8.0ms)  SELECT  [users].* FROM [users]  ORDER BY [users].[id] ASC OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY  [["LIMIT", 1]]
=> #<User >

using awesome_print gem returns weird typed object notation with String

> ap User.order(id: :asc).first
  User Load (6.0ms)  SELECT  [users].* FROM [users]  ORDER BY [users].[id] ASC OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY  [["LIMIT", 1]]
#<User:0x15e8c040> {
               "id"String1,
       "first_name"String"Aaron",
       "created_at"String2018-10-30 06:55:11 UTC,
       "updated_at"String2019-07-11 16:54:22 UTC
}

joins query reports unknown primary key

> User.joins(:user_roles)
Traceback (most recent call last):
ActiveRecord::UnknownPrimaryKey (Unknown primary key for table users in model User.)

raw sql appears to work fine

> ActiveRecord::Base.connection.execute("SELECT @@VERSION")
   (2.0ms)  SELECT @@VERSION
=> #<ActiveRecord::Result:0x75dd0f94 @rows=[["Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) \n\tJan  5 2018 22:11:56 \n\tCopyright (c) Microsoft Corporation\n\tDeveloper Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)\n"]], @hash_rows=nil, @columns=[""], @column_types={}>

> ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM USERS;")
   (8.0ms)  SELECT COUNT(*) FROM USERS;
=> #<ActiveRecord::Result:0x54bb1194 @rows=[[100]], @hash_rows=nil, @columns=[""], @column_types={}>

---

Gemfile

gem 'rails', '~> 5.1.0'
gem 'activerecord'
gem 'activerecord-jdbcsqlserver-adapter', '~> 51.0'

database.yml

development:
  adapter: sqlserver
  driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
  database: <%= ENV['database'] %>
  schema: dbo
  url: <%= ENV['url'] %>
  username: <%= ENV['username'] %>
  password: <%= ENV['password'] %>
  pool: 200

[rdubya]

It seems like you have things set up correctly.

As far as the unknown attributes, I would guess that means they aren't being read from the db correctly for some reason but I don't know why that might be.

On the .first call, I believe that was something that I had issues with because sql server doesn't want you to use offset/limit unless you are ordering by something. Apparently something in there is making it so we don't set an order by default. Can you see if you can make a failing test or some way to reproduce it?

[rdubya]

Oh, 1 thing that might be an issue, how are you defining your url? Does it include the database name? If you supply a url, it might ignore the database key.

[aaronkelton]

Thanks for your feedback. I removed the URL key and got this "connection refused" error.

ActiveRecord::JDBCError (The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

I read up on in the Rails guide for configuring database and then had an idea 💡 to try a different SQL Server. At my workplace we don't use SQL Server on our machine, so our "local" database is that remote one I referenced earlier from the SELECT @@VERSION call. Here are those details again, this time from SSMS:

Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 
Jan  5 2018 22:11:56 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

After hooking up to a different database (SQL Server 2014), I was able to successfully query the database from rails console. For posterity, here's the details of the working database:

Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64) 
May 24 2019 19:09:40 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I'm still going to try to understand why the 2012 one wasn't playing nicely with ActiveRecord. I'll close this issue for now since we have a workaround. If I find something out worth contributing back, I'll reopen. Thanks again for your work on this project. 🙌

[aaronkelton]

After digging into the database, we discovered that when setting up SQL Server for local development, there is a setting for default schema prefix, and it was taking the username. For example, if I logged in with username of "aaron", and created a table called "dynamos", it would look like aaron.dynamos (this naming style happened the same way whether it was from Rails migration or raw T-SQL CREATE TABLE script). All my other tables had the expected dbo. prefix, e.g. dbo.users because those tables were copied from a production database schema.

In Rails 5, the table ar_internal_metadata was created with the database's default prefix. So 99% of my tables were dbo.xyzs, and one was aaron.ar_internal_metadata. Apparently this difference was enough to break things. So the solution is we will be setting the default table prefix to dbo. on the SQL Server side of things and like the README says for this repo, putting the following in an initializer:

ActiveRecord::Base.table_name_prefix = 'dbo.'

So it wasn't a 2012 vs 2014 version issue. It was a SQL Server setting issue with default table prefix. 😌

[rdubya]

Glad you figured it out. Thanks for digging in and letting us know the end result!