IDENTITY_INSERT

[Anonymous]

I am reicieveing and error of
Cannot insert explicit value for identity column in table 'MEDISPAN_NDCHIST_QA' when IDENTITY_INSERT is set to OFF.
Cannot update identity column 'NDC_ID'.
Can someone please assist. The NDC_ID is set as the Identity in SQL. I have attached a few screen shots as well.

[Anonymous]

Hi
As the error shows, it is not allowed to insert an explicit value for identity column when INDENTITY_INSERT is set to OFF. To resolve this error, you can either remove the NDC_ID column from the schema, you don't need to insert value for this column, as it is an identify column in table and the value will be generated automatically or check the option 'Turn on Identify insert' on tMssqlOutput component to allow to insert an explicit value for identity column.
Shong

[Anonymous]

Hi,
Actually there are two method to solve your issue which are as follows:
1) No need to insert the values for IDENTITY column as it is a IDENTITY column so it will automatically insert the value for it as mention by above by Shong. So no need to map that column in our job.
2) If in case you still wanted to insert the value explicitly, there is a approach to do so as following:
a) First set the IDENTITY insert on by using following command / SQL
SQL: SET IDENTITY_INSERT TABLE_NAME ON;
b) After executing this it will allow you to insert the value in your IDENTITY column (NDC_ID)
c) Once inserting of your data is done you can again set IDENTITY insert to off by using following command /SQL
SQL: SET IDENTITY_INSERT TABLE_NAME OFF;

Best Regards,
Mayur

[Anonymous]

ok so now i can now im stuck at another part. I can not get my scd_start or scd_end dates to show a value other than null. please see pictures below.

[Anonymous]

Hi,
As per your "SCD compiler editor" snapshot which you have attached, it seems that you have put the scd_start column in the Unused section. It should be in Type0Field and it should be property mapped with scd_start.
Also check you mapping by using Edit Schema in your tLog_Row or you can also use "Sync Column" option from tLog_Row component.
Best Regards,
Mayur

[Anonymous]

It still gives me the null value and the error code of parameter #1 has not been set

[Anonymous]

Hi,
It means that you have not set the parameter properly, i.e. check you mapping by using edit schema option. Every column should be mapped properly.
It would be good if you can attach the latest snapshot of SCD compiler editor & the edit/view schema window.
Best Regards,
Mayur

[Anonymous]

here are current views.
Please let me know if you need a differnt screen shot

[Anonymous]

Hi,
It seems that you have made the End_Date as key, Remove the key from End_Date as End_Date is a fixed year value i.e a constant value hence it can not be a key.
Do this modification and let me know the outcome.
Best Regards,
Mayur

[Anonymous]

Hi
The source keys should be the primary key that will be compared whether the keys already exist. remove the scd_end field from the source keys list.
The NDC_ID is the surrogate key in your case, don't put it into the type0 list; Remove the scd_start field from the type0 list. I suggest you to read this wiki article to learn more about Slowly_changing_dimension. Also, Talend provides a demo job for tMysqlSCD in this page:
https://help.talend.com/search/all?query=tMysqlSCD&content-lang=en
Shong