0

I am working on the spring batch application which inserts the new entry into BATCH_JOB_INSTANCE internally. Version of spring is 2.2.1 and database is Azure. While running the job I am getting the below error. As suggested in one of the site, I enabled the SET IDENTITY_INSERT to ON even though I have not used any INSERT statements to BATCH_JOB_INSTANCE. But it is of no use. 

Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; Cannot insert explicit value for identity column in table 'BATCH_JOB_INSTANCE' when IDENTITY_INSERT is set to OFF.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'BATCH_JOB_INSTANCE' when IDENTITY_INSERT is set to OFF.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:247) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:922) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:120) ~[spring-batch-core-4.2.0.RELEASE.jar:4.2.0.RELEASE]
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:140) ~[spring-batch-core-4.2.0.RELEASE.jar:4.2.0.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_144]
Kiran Kumar
  • 133
  • 3
  • 9
  • Does this answer your question? [How to turn IDENTITY\_INSERT on and off using SQL Server 2008?](https://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008) – Mahmoud Ben Hassine May 13 '20 at 08:27
  • @mahmoud - i can not use that as i do not use any insert statements. Insert is called internally by spring batch. – Kiran Kumar May 13 '20 at 09:08
  • I'm not familiar with SQL server, but according to the docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15, "At any time, only one table in a session can have the IDENTITY_INSERT property set to ON". So apparently it is not possible to set this flag once on all Spring Batch tables.. This is really an annoying constraint, and I don't see how this problem could be fixed since Spring Batch won't be able to insert records in its meta-data tables.. – Mahmoud Ben Hassine May 13 '20 at 09:34

1 Answers1

0

It would be better if you post your Job configuration... DataIntegrityViolationException clearly states that an attempt to insert or update data results in violation of an integrity constraint. DataIntegrityViolationException Docs

While job configuration jobBuilderFactory.get(JOB_NAME).incrementer(new RunIdIncrementer())

to increment the run id and every run gets the unique id

Ganesh
  • 13
  • 4