If you tried to insert records into a table that has an identity column (id in this case) using exec_insert:
connection.exec_insert("insert into [test].[aliens] (id, name) VALUES(1, 'Trisolarans'), (2, 'Xenomorph')"
Then the adapter would first need to turn on IDENTITY_INSERT for the table first. However, instead of running:
SET IDENTITY_INSERT [test].[aliens] ON;
The adapter was ignoring the table schema and trying to turn on IDENTITY_INSERT for the table in the default schema.
SET IDENTITY_INSERT [aliens] ON;
If a table of the same name did not exist in the default schema then an error would be thrown by SET IDENTITY_INSERT. If table of the same name in the default schema did exist then the INSERT query would fail as it would be unable to insert into the identity column.
Note: This is not a new issue. The issue was present in at least v7.0.2.0/7.0.1.0/6.1.3.0 of the gem.
Fix the insertion of records to non-default schema table that has identity column using raw SQL. This fixes https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/1050
If you tried to insert records into a table that has an identity column (
id
in this case) usingexec_insert
:connection.exec_insert("insert into [test].[aliens] (id, name) VALUES(1, 'Trisolarans'), (2, 'Xenomorph')"
Then the adapter would first need to turn on
IDENTITY_INSERT
for the table first. However, instead of running:The adapter was ignoring the table schema and trying to turn on
IDENTITY_INSERT
for the table in the default schema.If a table of the same name did not exist in the default schema then an error would be thrown by
SET IDENTITY_INSERT
. If table of the same name in the default schema did exist then theINSERT
query would fail as it would be unable to insert into the identity column.Note: This is not a new issue. The issue was present in at least v7.0.2.0/7.0.1.0/6.1.3.0 of the gem.