Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shacky
Starting Member
16 Posts |
 Posted - 2011-01-23 : 23:47:36
|
| Hi Team,I am having this problem while trying to transfer data using SSIS on BDIS. I will also get this error, "cannot update identity column 'id'". When i checked the tables and did some self testing, i found out that the error is caused by foreign keys. I was able to transfer data when there is only 1 foreign key involved with the table. [Table A referenced by Table B]However, when there are 2 foreign keys involved with the table, i get the error mentioned above. [Table A referenced by Table B,C]Below are the settings that i have created to simulate the error.1) Created 2 database, with 3 tables2) The 3 tables are namely, Name, Email and User2) 1 database on 1 server, both database have identical tablesThe tests i conducted are as below:1st test:1) Name's NameID is referenced by User's NameID.2) Added data to the database accordingly3) Tested on BDIS and no problem encountered.2nd test:1) Name's NameID is referenced by User's NameID and Email's NameID.2) Added data to the database accordingly3) Tested on BDIS and "Cannot Update Identity Column 'id'" error occurred.Can someone advise me for possible solutions (other than changing the table format) as i do not have the rights to change the database tables structure. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-24 : 11:28:55
|
| the error message suggests you're trying to overwrite values of identity column. can you tell what are identity columns of each of tables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
shacky
Starting Member
16 Posts |
Posted - 2011-01-24 : 20:31:44
|
| Hi Visakh,the identity columns of the tables are as follows:Table 'Name' - NameIDTable 'User' - UserIDTable 'Email' - EmailIDboth user and email table have a column named NameID which takes reference from Name table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-26 : 11:53:37
|
| you're trying to transfer data of all these tables from one database to other? if yes, you need to set proprty identity insert on else it will throw the same error------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
shacky
Starting Member
16 Posts |
Posted - 2011-01-26 : 20:28:15
|
| How do i go about doing that? I am currently using Transfer SQL Server Objects to do the transferring of data and tables. |
|
|
|
|
|
|
|
|
Unable to update tables using SSIS package