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
Did you get an answer? I am having a similar issue with an error being
thrown for 'Unable to set Identity Insert to ON"
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
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
-
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:inexecute’
/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:ininsert_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:increate_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:increate_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:increate_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:insetup’ -
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:inteardown’
1 tests, 0 assertions, 0 failures, 2 errors
WARNING: #ODBC::Statement:0x2b345843a0d8 was not dropped before
garbage collection.
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 N. [email protected] 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
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
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.