I tried to execute insert stored procedure for table and hibernate mapping file with Identity generator field.

<hibernate-mapping>
<class name="...Tabletest" table="Test" schema="dbo" catalog="TestData">
<id name="id" type="int">
<column name="ID" />
<generator class="identity" />
</id>
<property name="Test" type="string">
<column name="Test" />
</property>
<sql-insert callable="true" check ="none">{call dbo.insert_Test (@data=?)}</sql-insert>
</class>
</hibernate-mapping>

Unfortunately hibernate does not handle identity with stored procedures well. My guess it is because it is trying to retrieve new identify data, executes “select SCOPE_IDENTITY() AS GENERATED_KEYS” statement which is getting out of scope from stored procedure execution. What is the best practice to implement simple key generator using MSSQL server and stored procedures in hibenate? Do i have to use uid keys or composite keys?

Exception with identity field:
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not insert: [Tabletest]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
….
com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:403)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)