Forum: Ruby on Rails SQLServer IDENTITY_INSERT

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
6dc185130e0ec5c84e165b265c141f38?d=identicon&s=25 christer.nilsson (Guest)
on 2005-11-17 23:33
(Received via mailing list)
When running the first test in the AWDR book I ran into this error
message:
 Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Cannot insert explicit value for identity column in table 'Users' when
IDENTITY_INSERT is set to OFF.
HRESULT error code:0x80020009
INSERT INTO users (id, name,password) values(10,'Christer','secret')
 I'm using SQL Server.
 It seems like the adapter does not recognize that id is an identity
column.
As a consequence "SET IDENTITY_INSERT users ON" is not executed. After
that
the INSERT barfs.
 I have tried ActiveRecord 1.12.2 and 1.13.0. Same error.
 Christer
Dc662775654c3dac272f14a22bbf62a8?d=identicon&s=25 Kevin (Guest)
on 2005-11-20 08:50
Did you get an answer? I am having a similar issue with an error being
thrown for 'Unable to set Identity Insert to ON"
Af95bdaf87958c40150b813e94381bfd?d=identicon&s=25 Christer Nilsson <janchrister.nilsson@gmail.com> (Guest)
on 2005-11-20 13:50
Kevin wrote:
> Did you get an answer? I am having a similar issue with an error being
> thrown for 'Unable to set Identity Insert to ON"

No, I gave up and switched to MYSQL as nobody answered my question.

cheers

Christer
20cb7c667e025a3711e787b5251d0175?d=identicon&s=25 Richard (Guest)
on 2006-06-28 17:03
I have the answer, so basically in the sqlserver_adapter.rb 'class' ( i
call it a class cos i come from a java background :-) ) there is a line
which switches on identity insert, if the user that you are connecting
to the database has not got permission to do this then it will fail and
your whole insert will fail, try this in your query analyzer or similar
on the database ( logged in as the user that you have setup in rails
)and check the error message: "SET IDENTITY_INSERT <table_name> ON"
shoudl be something like: "The current user is not the database or
object owner of table 'table_name'. Cannot perform SET operation." so
make sure the user details that you have specified in your connection
file ( database.yml ) has permission.

Any more question let me know

Rich

Christer Nilsson <janchrister.nilsson@gmail.com> wrote:
> Kevin wrote:
>> Did you get an answer? I am having a similar issue with an error being
>> thrown for 'Unable to set Identity Insert to ON"
>
> No, I gave up and switched to MYSQL as nobody answered my question.
>
> cheers
>
> Christer
A41d9c6796c26836ea3356edcddf7439?d=identicon&s=25 Fred (Guest)
on 2007-04-02 02:10
Richard wrote:
> I have the answer, so basically in the sqlserver_adapter.rb 'class' ( i
> call it a class cos i come from a java background :-) ) there is a line
> which switches on identity insert, if the user that you are connecting
> to the database has not got permission to do this then it will fail and
> your whole insert will fail, try this in your query analyzer or similar
> on the database ( logged in as the user that you have setup in rails
> )and check the error message: "SET IDENTITY_INSERT <table_name> ON"
> shoudl be something like: "The current user is not the database or
> object owner of table 'table_name'. Cannot perform SET operation." so
> make sure the user details that you have specified in your connection
> file ( database.yml ) has permission.
>
> Any more question let me know
>
> Rich
>

I am having the same problem with the identity problem.  I give the user
name tons of permissions on the test DB, and was able to run the command
in QA.  But I still get the following error  - sorry about the garbage
output.  Just adding it on the off chance its useful

  1) Error:
test_truth(LogEditTest):
ActiveRecord::ActiveRecordError: IDENTITY_INSERT could not be turned ON
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:275:in
`insert'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:296:in
`execute'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:288:in
`insert_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:287:in
`insert_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:257:in
`create_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:257:in
`create_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/connection_adapters/abstract/database_statements.rb:51:in
`transaction'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:255:in
`create_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/base.rb:794:in
`silence'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:248:in
`create_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:565:in
`load_fixtures'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:512:in
`setup'

  2) Error:
test_truth(LogEditTest):
NoMethodError: You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occured while evaluating nil.-
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/transactions.rb:112:in
`unlock_mutex'
    /usr/lib64/ruby/gems/1.8/gems/activerecord-1.14.3/lib/active_record/fixtures.rb:534:in
`teardown'

1 tests, 0 assertions, 0 failures, 2 errors
WARNING: #<ODBC::Statement:0x2b345843a0d8> was not dropped before
garbage collection.
A41d9c6796c26836ea3356edcddf7439?d=identicon&s=25 Fred (Guest)
on 2007-04-02 03:31
Some addition info -

I was playing around in the fixture file and when I removed the id
assignment line (but left the remaining assignments) the test ran just
fine.

Looks like its still dealing with the identity insert bit.

Also, I keep getting the warning about the #<ODBC::....> was not dropped
before garbage collection.  Is this something to worry about, or will
the gc eventually collect the memory?

Thanks!

Fred
A41d9c6796c26836ea3356edcddf7439?d=identicon&s=25 Fred (Guest)
on 2007-04-15 01:00
No Idea's on how to fix this or hacks to get around it huh?  I haven't
had any luck finding any other examples on how to fix this.
This topic is locked and can not be replied to.