karmst Frequent Contributor.
Frequent Contributor.
67 views

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

Labels (1)
0 Likes
2 Replies
Highlighted
Knowledge Partner
Knowledge Partner

Re: MSSQL Server error with Identity Column and VIEW

A full level 3 trace of the driver starting up and the event in question (engine + RL, if using) might be helpful.
______________________________________________
https://www.is4it.de/identity-access-management
0 Likes
karmst Frequent Contributor.
Frequent Contributor.

Re: MSSQL Server error with Identity Column and VIEW

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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.