MSSQL Server error with Identity Column and VIEW

Hello,

Has anyone ever had a problem inserting a record in to a VIEW with an IDENTITY COLUMN in SQLServer?

index 1, field 'pk_UserID', value = -1
index 2, field 'UserName', length: 6, value = 'keiarm'
index 3, field 'FirstName', length: 7, value = 'Keith 1'
index 4, field 'LastName', length: 9, value = 'Armstrong'
index 5, field 'Email', length: 26, value = 'Keith.Armstrong'
index 6, field 'DefaultTimeZone', length: 16, value = 'Europe/Stockholm'

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

I have a view which is just a SELECT from a table called USER. For some reason, even though I have Generation/retrieval method (table-global) set to auto-generated/identity column
and Retrieval timing (table-global) set to after row insertion, the INSERT statement is still trying to put a value in the identity column (called pk_UserID in the view).

This is the newest JDBC Driver on IDM 4.7.1.

Thanks,

KA

  • A full level 3 trace of the driver starting up and the event in question (engine RL, if using) might be helpful.
  • This was resolved by changing the Driver Parameter add-default-values-on-view-insert to No. The default setting is Dynamic. Changing this parameter did not force an insert of the IDENTITY column.

    Cheers,

    -KA

  • ,
    could you please assist on how you change driver parameter.
  • It should be in Driver Properties, Driver Configuration, look at the settings for Publisher channel.

  • Please correctmeon this,

    we have problem with BSM 9.30 APM dashboards,no data is retrieved.Upon looking logs  these are the errors .

    wde_all.log file error.

    20:48:51,639 [WDE-CnfgChngSbscr] (QueryExecutorImpl.java:212) ERROR - couldn't retrieve connection from connection pool.
    2020-09-03 20:48:52,952 [WDE-CnfgChngSbscr] (SQLExceptionTMC.java:123) ERROR - A TMC SQLException has been thrown. The message is: Error getting connection from pool
    2020-09-03 20:48:54,470 [WDE-CnfgChngSbscr] (ConfigChangeSubject.java:129) ERROR - error while observer invokation. Msg:RELOAD Observer:ConfigurationListener
    java.lang.OutOfMemoryError: GC overhead limit exceeded

     

    setting .log

    ERROR DBUtil.getConnection(44) - Failed to create DB Connection throwing SQLException...
    com.mercury.infra.utils.exceptions.MNoSuchElementException: Failed to borrow object from pool: dbType=(SQL Server); hostName=xxxxxx\xxxxx dbName=xxxxxx; userName=xxxxx; server=xxxxxx\xxxxx; sid=; port=1433 com.mercury.infra.utils.exceptions.MNoSuchElementException: Failed to create a connection for pool: dbType=(SQL Server); hostName=xxxxxx\xxxxx; dbName=xxxx; userName=xxx; server=xxxxxxxxxxxx\xxxxxx; sid=; port=1433: [Mercury][SQLServer JDBC Driver]Login has timed out.

    offline.engine.all.log

    2020-09-06 16:54:23,251 [NOA4ETLThread-141414-] (EtlApiHelper.java:2002) ERROR - No application was found for APP Tier id:660 app id:865dcf93e62a80705cb6b2b8b451fbcf
    2020-09-06 16:54:23,251 [NOA4ETLThread-141414-] (EtlApiHelper.java:2001) ERROR - Error while getting info for DIM: APP_TIER one of the DIM records failed.
    2020-09-06 16:54:23,251 [NOA4ETLThread-141414-] (EtlApiHelper.java:2002) ERROR - No application was found for APP Tier id:662 app id:865dcf93e62a80705cb6b2b8b451fbcf

     

    Could you please assist on this,application was working fine before but now we face challenge on getting data on APM dashbord and I dont know where problem is.