On Tue, Feb 9, 2021 at 9:25 PM Michael Davis <[email protected]>
wrote:
> Trying to insert rows, using import, into a table with auto increment
> primary key and seeing this error (the insert includes the primary key
> because I am wanting to preserve them):
>
> Sequel::DatabaseError:
> WIN32OLERuntimeError: (in OLE method `Execute': )
> OLE error code:80040E14 in Microsoft OLE DB Provider for ODBC
> Drivers
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> explicit value for identity column in table 'Categories' when
> IDENTITY_INSERT is set to OFF.
>
> I have tried to run/execute_ddl("SET IDENTITY_INSERT Categories ON") but
> it has no effect. How can I set IDENTITY_INSERT in mssql so the inserts
> with the primary key will work?
>
First, you should make sure you are running that on the same connection, so
you should do it inside a Database#synchronize block. You also should
unset it after the import:
DB.synchronize do
DB.run("SET IDENTITY_INSERT Categories ON")
DB[:categories].import(...)
ensure
DB.run("SET IDENTITY_INSERT Categories OFF")
end
You may want to ensure that you are quoting the table the same way as
Sequel is doing in the import statement, though I don't think that should
matter.
If that doesn't work, I'm not sure what the issue is. At that point, you
may want to drop down to the driver level and see if you can still
reproduce the issue. If you can show a minimal self-contained example
using the driver directly, I can probably figure out how to get it working
with Sequel.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSd7mUKyjwG%3DOdGZfpicMR6LTHB8zji_h5UN0p46cVTEjg%40mail.gmail.com.
