I am attempting to insert a record into a table with multiple primary key fields.  The SF Business Object has the following properties set:

PrimaryKeyIsAutoIncremented = False
PrimaryKeyIsUpdateable = True

When I run my code, the error generated tells me:

Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.

Dude...wudzupwiddat?
Bill

Hi Bill,

This looks like an SQL error - check the BOL for IDENTITY_INSERT:

"At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time."

Cheers, Peter

 

LOL....I needed a good laugh   I like your attitude!  I think that Pete gave you excellent advice.  Since your PK is not being auto-incremented then you will have to specify the PK value yourself (like you needed me to tell you that part  ) if it is not being set on the SQL side (i.e. default value, trigger, etc.). 

The first thing that I would look at would be INSERT_IDENTITY to see if you have an issue on that side.  if you get stuck, then give me some more details and we can go to the next step

Peter and Trent,

Thanks.  I actually love the BOL.  When this error came up I read the same entry that you mentioned, Peter.  It does provide decent insight into what SQL Server is doing when manipulating the identity columns manually; however, I am not really doing that.  I am inserting a record into a table that has multiple primary keys.  None of the keys are identity columns.  So, I think (I know...dangerous, at times) that this may be a setting on the BO somewhere.  I will do a little more checking, now that I had a good night's sleep.  I am sure something will pop out at me.

Bill

Here is what just popped out...on the first table that I am updating, there are two primary key fields.  As I mentioned, neither of these are identity columns.  As I searched through the table, I discovered that there is indeed an identity column.  It is indexed, but not part of the primary key for the table.  I know that the SF framework is quite flexible and that you guys have thought of just about everything.  Was this thought of and how do I handle it in the BO?  My thinking is that I will need to resort to a stored procedure to insert these records.

Thanks for all of your help!
Bill

Btw, I really hope not.  This is a really complex procedure and I have about 400 lines of code invested with several new business objects handling all of the heavy lifting.  To relocate all of this logic (much of it is business logic and fits perfectly into the business object environment) to a stored procedure will be a nightmare.  And, to explain this all to my manager will be even worse.

So, my hope is that SF can come through for me.  Can you?


Bill

Hi Bill,

Please check out this post: http://forum.strataframe.net/Topic17032-6-1.aspx?Highlight=BOM

I believe this may be related to your needs.

Edhy Rijo

Hey Edhy,

Related?  Nope.  Right on!!!  That is exactly what I needed.    I have made the necessary adjustments to my BOs and am now testing the procedure.  So far, the results are good...very good.  Man, am I glad that I did not have to rewrite any of this in TSQL. 

Thanks!
Bill

Good answer, Edhy!

Hi Bill,

I am glad that was the answer.  I was following this thread, but somehow got a bit lost with your situation, then with your last post, I saw the light

Edhy Rijo