Sitelet https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/1417
Skip to content

change_column_null re-declares a datetime2(n) column as datetime(n), which SQL Server refuses #1417

Description

@ekzobrain

Description

change_column_null rebuilds the column type from the column's abstract type:

# lib/active_record/connection_adapters/sqlserver/schema_statements.rb
sql = "ALTER TABLE #{table_id} ALTER COLUMN #{column_id} #{type_to_sql column.type, limit: column.limit, precision: column.precision, scale: column.scale}"

For a column created as :datetime with a precision, the adapter makes a datetime2(n) column, and that rule
is applied in TableDefinition#new_column_definition and in change_column
(type = :datetime2 unless options[:precision].nil?). But change_column_null calls type_to_sql directly:
the column's type is :datetime and its precision is 6, so the statement says datetime(6):

ALTER TABLE [events] ALTER COLUMN [at] datetime(6) NOT NULL
TinyTds::Error: Column, parameter, or variable #4: Cannot specify a column width on data type datetime.

Steps to reproduce

activerecord-sqlserver-adapter 8.0.10, Rails 8.0 (the method is the same on main):

connection.create_table(:events) { |t| t.column :at, :datetime, precision: 6 }
connection.columns(:events).find { |c| c.name == "at" }.sql_type # => "datetime2(6)"
connection.change_column_null(:events, :at, false)
# => ActiveRecord::StatementInvalid: TinyTds::Error: ... Cannot specify a column width on data type datetime.

Expected

ALTER TABLE [events] ALTER COLUMN [at] datetime2(6) NOT NULL

Suggested fix

Apply the same rule where the type is turned into SQL, so every caller gets it — e.g. in type_to_sql:

def type_to_sql(type, limit: nil, precision: nil, scale: nil, **)
  type = :datetime2 if type.to_s == "datetime" && !precision.nil?
  # ...
end

or map the type in change_column_null the way change_column does.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions