Error using sql server sequence with Hibernate 5.0.3

Question

This is the error. The 'SQ_USERS' is the sequence created in a sql server database.

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.SQ_USERS'.

This is the entity that is used to get the sequence...

@Entity
@Table(name="USERS")
public class User {

    @Id
    @Column(name="id")
    @SequenceGenerator(name = "USER", schema="dbo", sequenceName = "SQ_USERS", allocationSize = 20)
    @GeneratedValue(generator = "USER", strategy = GenerationType.SEQUENCE)
    private int id;
}

The sql that is generated is "select next_val as id_val from dbo.SQ_USERS with (updlock, rowlock)", but if i try this in sql server management studio, i get the error "Invalid object name 'dbo.SQ_USERS'."

Can anyone help?

Thanks.

Jason · Accepted Answer · 2017-03-29 15:32:33Z

3

I found the issue to the problem. I was using the wrong hibernate dialect. When I changed the dialect to "SQLServer2008Dialect" it worked correctly.

answered Mar 29, 2017 at 15:32

Jason

712 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gweltaz Niquel Over a year ago

I was using : spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect It works with : spring.jpa.database-platform=org.hibernate.dialect.SQLServer2008Dialect

Raman Sahasi · Accepted Answer · 2017-03-20 04:12:18Z

0

Try adding schema and catalog to your @Table annotation and see if the error is fixed:

Something like this:

@Table(name="USERS", catalog="<your database name>", schema="dbo")

answered Mar 20, 2017 at 4:12

Raman Sahasi

32k9 gold badges63 silver badges74 bronze badges

Comments

Zak · Accepted Answer · 2021-01-23 11:46:02Z

0

If Hibernate dialect is specified as org.hibernate.dialect.SQLServerDialect, then the SQL generated to obtain a sequence's next value is:

select next_val from my_sql_server_sequence with (updlock, rowlock);

which fails with an error: [S0002][208] Invalid object name 'my_sql_server_sequence'.

If Hibernate dialect is specified as org.hibernate.dialect.SQLServer2012Dialect, then the SQL generated to obtain a sequence's next value is:

select next value for my_sql_server_sequence;

This works with Microsoft SQL Server 2017 and Hibernate 5.4.23.

answered Jan 23, 2021 at 11:46

Zak

7,0766 gold badges39 silver badges54 bronze badges

Collectives™ on Stack Overflow

Error using sql server sequence with Hibernate 5.0.3

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related