SQL Server 2016 - Invalid object name 'hibernate_sequence'

Question

I have an image backup that I restore to the MS SQL server 2016. I have an entity that declares its id like that:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@XmlID
@XmlElement
@XmlJavaTypeAdapter(IntToStringXmlAdapter.class)
private Integer id;

when I save the entity I receive:

Hibernate: select next_val as id_val from hibernate_sequence with (updlock, rowlock) 2018-02-28 22:05:41.935 
ERROR 18152 --- [nio-8080-exec-6] o.hibernate.id.enhanced.TableStructure   : could not read a hi value com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'hibernate_sequence'. 

...... 
2018-02-28 22:05:41.942  WARN 18152 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 208, SQLState: S0002 

2018-02-28 22:05:41.942 ERROR 18152 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid object name 'hibernate_sequence'.

I have created by hand the sequence to the SQL server and I make sure that it exist through the SSMS.

CREATE SEQUENCE hibernate_sequence
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 99
 NO CYCLE; 

Despite of this I continue to the receive the previous error.

Any ideas what I am doing wrong?

Thank you in advance

Answer 1

Following points to check:

• What dialect you are using?
• What hibernate version you are using? Version 5 changed the GenerationType.AUTO behavior
• Set "hibernate.hbm2ddl.auto" to update and see what it creates in the database
• Avoid GenerationType.AUTO. Set it explicit to GenerationType.IDENTITY or GenerationType.SEQUENCE depending on what you want or your DB supports.
• Check if you have the latest SQL Server JDBC driver. I had issues with it migrating from hibertnate 4.3 to 5.0
• In hibernate 5 set hibernate.id.new_generator_mappings to false

Answer 2

It seems you are upgrading SqlServer version. I faced it when i was upgrading SqlServer 2012 to SqlSever 2017.

Caused by : talk between driver (jtds or sqlserver ) and SqlServer 2017+ no more consider table "dbo.hibernate_sequence" as work-around. It needs specifically sequence with name hibernate_sequence.

solution : delete table named as dbo.hibernate_sequence and create sequence

Example :

USE [your_database_name]
GO
CREATE SEQUENCE [dbo].[hibernate_sequence] 
 AS [bigint]
 START WITH 10000000
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE 
GO

SqlServer Studio screenshot