Possible Bug: Cannot insert explicit value for identity column
I've been using Talend for a while now and am trying to do something with MS SQL that I have done many times before in MySQL. I have a job that is taking a single column from on table, and is passing it to another table that has two columns, one being the column that's reciving the data, the other column being an auto increment identity.
As you will see in the attached screen cap, I have the tmap sending the data from the input table, to the NON-KEY column in the desitnation table. Though I am getting the following error when running the job....
Cannot insert explicit value for identity column in table 'Companies' when IDENTITY_INSERT is set to OFF.
I've done this the wxact same way with MySQL and have had no problems. Also, please note that this is pulling from and inserting into tables that are in a MS SQL 2000 environment running in simple mode. I don't know if that makes any difference.
Hi PeterS I think it's not the problem of your job,but the table of the database. Maybe the table is readonly and you can't do any DML operation.
We have actually started writing stored procedures to do the ETL for this as it's a rather simple process, however we would still like to determine the cause of this issue. I have checked and the database is not in read-only mode. Also, we are able to perform normal DML statements (insert, update, delete) on the table in question, so the table itself is in RW mode.
The error that's being provided seems to occur when you are trying to insert into an identity field. As you can see in the job, there is nothing that is going into the key field in the table, but still it looks like Talend is trying to pass something to it.
- Peter S
You have two options to resolve:
1. Remove the identity column from the schema (in the output of the tMap and propogate to the DBOutput component). Ensure that the DB output component has the "Turn on Identity Insert" set to off. SQL server takes care of the identity column and you just need to map all other columns in the schema.
2. Leave the identity column in the schema. Ensure "Turn on Identity Insert" is set to on in the DB output component and map an explicit value to the identity column (using a sequence).
Either option should work. Your choice.
Regards,
Mark
You have two options to resolve: 1. Remove the identity column from the schema (in the output of the tMap and propogate to the DBOutput component). Ensure that the DB output component has the "Turn on Identity Insert" set to off. SQL server takes care of the identity column and you just need to map all other columns in the schema. 2. Leave the identity column in the schema. Ensure "Turn on Identity Insert" is set to on in the DB output component and map an explicit value to the identity column (using a sequence). Either option should work. Your choice. Regards, Mark
I am not passing anything to the identity column of the table. I know that when I use MySQL components for this type of task it works without fail, but with MS this isn't working. Let me break this down....
There are two columns in the table that is recieving the data, one that is ID, the other that just holds values. In the T-map you will see that the data is only being sent to the second, non identifying/key column. Normally when this is passed I would expect to see an insert statment similar to this....
insert into table (datacolumn2) VALUES ('test data').
When you do this insert there is nothing being passed to the ID column of the table, and since this is auto_inrement, it should automatically be populated with the next int value.
I've done this MANY times using TOS and MySQL and, as expected, the next auto_inc value is populated. So in this case, despite the fact that I'm not passing any data to the identity column, it's still giving me this error.
I COMPLETELY agree with you that one of these two options would need to be taken if I was passing data to that ID column, however I'm not so I shouldn't be seeing this error. Again, this works every time with MySQL (and I can only assume that were I passing data to the ID column that I would get a similar error) but with MS SQL 2000 this is not working.
At this point we have already coded something outside of TOS that has handled this issue. The data has been moved and this is no longer critical for us, however this isn't the last time that we'll be using TOS for something like this, and knowing the cause of this problem would still be very benficial.
- Peter S
Hi Peter,
I don't believe you read my response properly.
Option 1 was provided for your exact scenario where you don't want to pass a value to the identity column and want to allow sql server to auto-increment this column when rows are inserted. To achieve this in Talend, you need to REMOVE THE IDENTITY COLUMN FROM THE OUTPUT SCHEMA in the tMAP and the DB OUTPUT component ie the schema will reflect all columns except the identity column. Talend will then construct a SQL statement like:
insert into table (datacolumn2) VALUES ('test data').
and SQL server will set the value of the identity column automatically for you on insert. If you leave the identity column IN the output schema, albeit with no value mapped, Talend will construct a SQL statement that includes the identity column and attempt to set it to null - undesirable as you have discovered:
insert into table (identitycolumn, datacolumn2) VALUES (null, 'test data').
Regards,
Mark
Hi, Followed the steps mentioned to resolve the issue of identity insert . But, I don't see data being inserted in the DB Table.Please find the screenshot
