To create the table in sql server (from SSMS):
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](15) NOT NULL,
[Description] [text] NULL,
[Picture] [image] NULL,
PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here is my code snipit:
table_name = :Categories
columns = ["CategoryID","CategoryName","Description"]
data = [[1,"Beverages","Soft drinks, coffees, teas, beers, and
ales"],[2,"Condiments","Sweet and savory sauces, relishes, spreads, and
seasonings"],[3,"Confections","Desserts, candies, and sweet breads"]]
connection_string = 'ado:///northwind_mssql?host=localhost\\MD' # MD is the
instance of my local sql server database
Sequel.connect(connection_string) do |db|
db.synchronize do
db.run("SET IDENTITY_INSERT #{table_name} ON;")
db[table_name].import(columns, data)
ensure
db.run("SET IDENTITY_INSERT #{table_name} OFF;")
end
ds = db.fetch("select * from #{table_name}")
p ds.all
end
Here is the error:
C:/Ruby27/lib/ruby/gems/2.7.0/gems/sequel-5.39.0/lib/sequel/adapters/ado.rb:151:in
`method_missing': WIN32OLERuntimeError: (in OLE method `Execute': )
(Sequel::DatabaseError)
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.
HRESULT error code:0x80020009
On Tuesday, February 9, 2021 at 11:06:09 PM UTC-7 Jeremy Evans wrote:
> 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/c54b67b4-e374-406a-9952-f2cafae8103fn%40googlegroups.com.
