1

As I had mentioned in this post JPA SET IDENTITY_INSERT not working that I was successful in using the 

SET IDENTITY_INSERT

query for my purposes. But today after I have managed to configure connection pooling i am getting this type of error.

java.sql.SQLException: IDENTITY_INSERT is already ON for table 'mydb.dbo.[table1]'. Cannot perform SET operation for table 'dbo.[table2]'.
2015-03-11 15:52:36,126 WARN  org.hibernate.util.JDBCExceptionReporter.logExceptions:233 - SQL Error: 544, SQLState: 23000
2015-03-11 15:52:36,127 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions:234 - Cannot insert explicit value for identity column in table '[table2]' when IDENTITY_INSERT is set to OFF.
2015-03-11 15:52:36,128 ERROR   org.hibernate.event.def.AbstractFlushingEventListener.performExecutions:324 - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at     org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)

Can anyone suggest something to resolve this error?

Community
  • 1
  • 1
muasif80
  • 5,586
  • 4
  • 32
  • 45

1 Answers1

0

Using Identity Insert is a symmetrical command, after turning it on, you must turn it off. For example.

Set Identity_Insert ErrorLog On

Insert  Into ErrorLog
        ( LineNumber, ErrorMessage )
Values  ( 0, 'Test 1' ) ,
        ( 1, 'Test 2' )

Set Identity_Insert ErrorLog Off

Hope that helps.

Jim
  • 2,034
  • 1
  • 22
  • 43
  • Yes I am doing that and the two are wrapping up my insert operation. But when another transaction happens it gives such error as mentioned above. – muasif80 Mar 11 '15 at 16:36
  • Not sure what to tell you then. SQL thinks it is still on. My answer is based upon the error in your original post. You mentioned in your comment that "...when another transaction happens...", is this transaction on the same connection and is it running before you set the Identity_Insert back to off? If it is, you need to either have the other transaction on a different connection, or make sure it waits until the Identity_Insert is set back to off. – Jim Mar 11 '15 at 16:41