MS SQL Exception: Incorrect syntax near '@P0'

Question

SQL Server requires you to place parenthesis around the argument to top if you pass in a variable:

SELECT TOP (?)

In our application we have extended a depraceted SQLServerDialect. After change to SQLServer2008Dialect the problem disappeared.

Upgraded hibernate to version 5.x and came across this issue. Had to update "hibernate.dialect" configuration from org.hibernate.dialect.SQLServerDialect to org.hibernate.dialect.SQLServer2012Dialect. Fixed the issue!

Hibernate Doc Reference: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/session-configuration.html#configuration-programmatic

Hibernate Jira issue: https://hibernate.atlassian.net/browse/HHH-10032

It can also be caused by a syntax error in your SQL as was the case for me

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05'

gave the message

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P5'

the problem was actually the balancing ')' missing at the end, namely, correct version is

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05')

If you are using Hibernate within a spring-boot app, you may set hibernate.dialect with following configuration property:

spring.jpa.database-platform=org.hibernate.dialect.SQLServer2008Dialect