IDENTITY_INSERT is set to OFF.

[harithay]

Hi ,

i am getting warning error as foloows.

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

what does it mean

nsartable..Transformer_1.DSLink17: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO uci.dbo.nsar(nsar_id, total_net_assets, change_in_tot_assets, foreign_exposure, diversified_fund, open_end_fund, fund_name, borrowing_name, cik, as_of_date, created_dtm, created_by, last_upd_dtm, last_upd_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=23000, DBMS.CODE=544
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'nsar' when IDENTITY_INSERT is set to OFF.

nsar_id = 1
total_net_assets = 1248430
change_in_tot_assets = 0
foreign_exposure = "N"
diversified_fund = "Y"
open_end_fund = "N"
fund_name = "THE ADAMS EXPRESS COMPANY"
borrowing_name = "THE ADAMS EXPRESS COMPANY"
cik = 0000002230
as_of_date = "2004-06-30 00:00:00.000"
created_dtm = "2005-05-19 12:56:10.000"
created_by = "dbo"
last_upd_dtm = "2005-05-19 12:56:10.000"
last_upd_by = "dbo"


thanks

[ds_developer]

I found the following from an Oracle site (but it is refers to DB2):

AS IDENTITY clause specifies that the column is an identity
column for the table. A table can have only one identity
column. START WITH specifies the first value for the
identity column. INCREMENT BY specifies the interval
between consecutive values of the identity column. CACHE
or NO CACHE specifies whether to keep some preallocated
values in memory. MAXVALUE specifies the numeric
constant that is the maximum value that is generated for this
identity column. MINVALUE specifies the numeric constant
that is the minimum value that is generated for this identity
column.

It looks like you are trying to insert into a column that DB2 wants to handle.
Hope this helps,
John

[harithay]

thanks ds_developer

[vmcburney]

In SQL Server an identity column automatically generates sequence numbers when rows are inserted. In normal operation you leave it out of the insert column list so that SQL Server sets it. If you are doing data conversion you may want to explicitely set this value, for example you want to retain the id numbers from a legacy system or a previous version of the table, to do this you switch the IDENTITY_INSERT value to ON.

You can do this in DataStage on your Before SQL tab, not sure of the exact syntax but it's something like SET IDENTITY_INSERT TABLENAME ON. You can then write ID values directly into the identity field. Make sure you switch it off again in your After SQL tab. There are some restrictions, I think you can only switch IDENTITY_INSERT on for one table at a time. If you leave it on it could screw up any applications trying to write to that table.

[ray.wurlod]

If what you want to do is to make use of the IDENTITY property, simply leave this column out of your job design entirely. When a new row is inserted, the identity column will automatically have the next available value inserted into it.

[rdy]

In SQL Server an identity column automatically generates sequence numbers when rows are inserted. In normal operation you leave it out of the insert column list so that SQL Server sets it. If you are doing data conversion you may want to explicitely set this value, for example you want to retain the id numbers from a legacy system or a previous version of the table, to do this you switch the IDENTITY_INSERT value to ON.

You can do this in DataStage on your Before SQL tab, not sure of the exact syntax but it's something like SET IDENTITY_INSERT TABLENAME ON. You can then write ID values directly into the identity field. Make sure you switch it off again in your After SQL tab. There are some restrictions, I think you can only switch IDENTITY_INSERT on for one table at a time. If you leave it on it could screw up any applications trying to write to that table.

Word of warning on this: on SQL Server 2000, the ID executing this command will have to have the db_owner or db_ddladmin role. Make sure your organization allows the ID accessing the database to have this role in all environments.

Here's the quote from Microsoft Books Online:

Code: Select all

Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.