Description
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?
endSample 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}"
endExpected 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?