On Thu, Feb 11, 2021 at 6:39 AM Michael Davis <[email protected]>
wrote:
> 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
>
The code works fine with the tinytds adapter. It also works fine with the
ado adapter if you specify an appropriate :provider option. See
http://sequel.jeremyevans.net/rdoc/files/doc/opening_databases_rdoc.html#label-ado
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/CADGZSSf4R9hvGPDH7%3DFQUfEqwodCQVtQOn02OCSMjs66i0raWw%40mail.gmail.com.
