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 _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Did you get an answer? I am having a similar issue with an error being thrown for ''Unable to set Identity Insert to ON" christer.nilsson wrote:> 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-- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.
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-- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---