Temporary Table Not Accessible Despite Using the Same Connection Object

[blake-showoff]

Environment

• Rails Version: 7.2.2.1
• activerecord-sqlserver-adapter Version: 7.2.4
• TinyTDS Version: 3.2.0

Issue Summary

When creating a temporary table (for example: #temp_users) and then attempting to insert data into it using the same connection object, an error is raised stating that the table does not exist. This suggests that exec_query might be retrieving a different connection from the pool rather than using the same session where the temporary table was created.

Error Source

The error originates from the following piece of code, which only added for version 7 and does not exist in version 6-1-stable or 8-0-stable:
activerecord-sqlserver-adapter-7.2.4/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:569:in 'block in column_definitions'

# Since Rails 7, it's expected that all adapter raise error when table doesn't exists.
# I'm not aware of the possibility of tables without columns on SQL Server (postgres have those).
# Raise error if the method return an empty array
columns.tap do |result|
  raise ActiveRecord::StatementInvalid, "Table '#{table_name}' doesn't exist" if result.empty?
end

Sample Code to Reproduce

module External
  class Base < ActiveRecord::Base
    self.abstract_class = true

    establish_connection(
      adapter: 'sqlserver',
      encoding: 'utf8',
      database: ENV['EXTERNAL_DATABASE_NAME'],
      username: ENV['EXTERNAL_DATABASE_USERNAME'],
      password: ENV['EXTERNAL_DATABASE_PASSWORD'],
      host: ENV['EXTERNAL_DATABASE_HOST'],
      dataserver: ENV['EXTERNAL_DATABASE_HOST'],
      tds_version: ENV['EXTERNAL_DATABASE_TDS_VERSION'],
      port: ENV['EXTERNAL_DATABASE_PORT'],
      timeout: ENV['EXTERNAL_DATABASE_TDS_TIMEOUT']
    )
  end
end

External::Base.connection_pool.with_connection do |conn|
  temp_table = "#temp_users"
  conn.exec_query("IF OBJECT_ID('tempdb..#{temp_table}') IS NOT NULL DROP TABLE #{temp_table}")

  puts "Creating table"
  conn.exec_query("CREATE TABLE #{temp_table} (id INT IDENTITY(1,1), name NVARCHAR(100))")

  puts "Selecting table"
  result = conn.exec_query("SELECT * FROM #{temp_table}")
  puts "Result: #{result.to_a}"

  puts "Inserting into table"
  
  # ❌ This raises "Table doesn’t exist" error
  conn.exec_query("INSERT INTO #{temp_table} (name) VALUES ('John')")

  # ✅ Workaround: Only runs if the table still exists in this session
  conn.exec_query("IF OBJECT_ID('tempdb..#{temp_table}') IS NOT NULL INSERT INTO #{temp_table} (name) VALUES ('John')")

  # ✅ Workaround: raw_connection works without issue
  conn.raw_connection.execute("INSERT INTO #{temp_table} (name) VALUES ('John')")

  puts "Selecting table again"
  result = conn.exec_query("SELECT * FROM #{temp_table}")
  puts "Result: #{result.to_a}"
end

Expected Behavior

The temporary table (#temp_users) should persist within the session and remain accessible throughout the with_connection block.
All queries inside the block should use the same connection session to prevent losing access to the temporary table.

Seeking Fixes or Suggestions

I would appreciate any insights or recommendations on how to properly handle session-bound temporary tables in the SQL Server adapter.

• Is there a way to ensure that exec_query uses the same connection session as with_connection?
• Can the adapter be modified to allow all queries inside the block to use the same connection session to prevent losing access to the temporary table?
• Are there any best practices for working with temporary tables in Rails 7 with SQL Server?

[aidanharan]

The issue is that the adapter tries to retrieve the temporary table's column information from the default database (ENV['EXTERNAL_DATABASE_NAME'] in the example above) rather than the temporary database TEMPDB.

[blake-showoff]

Thanks, @aidanharan, for working on this issue! I noticed that the tagged version for the fix is 8.1. Any chance there will be a new release for Rails 7.2 (e.g., 7.2.5)?

[aidanharan]

Thanks, @aidanharan, for working on this issue! I noticed that the tagged version for the fix is 8.1. Any chance there will be a new release for Rails 7.2 (e.g., 7.2.5)?

Yes, I'll back-port any fix to Rails 7.2

[aidanharan]

Fix in https://rubygems.org/gems/activerecord-sqlserver-adapter/versions/7.2.5