hibernate4直接迁移mysql到sql server2008中时出现分页错误java.sql.SQLException: 'OFFSET' 附近有语法错

微瞰技术
博客专家认证
2019-03-05 01:29:38
Caused by: java.sql.SQLException: 'OFFSET' 附近有语法错误。
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:777)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2714)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:145)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
... 120 more

求大神解答
...全文
448 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 13 楼 stacksoverflow 的回复:
[quote=引用 12 楼 奋斗的镇屌 的回复:]
[quote=引用 10 楼 stacksoverflow 的回复:]
Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available.

View or Change the Compatibility Level of a Database

In T-SQL you can check it like this:

USE AdventureWorks2012;
GO

SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks2012';
GO
Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?:

65 - SQL Server 6.5
70 - SQL Server 7.0
80 - SQL Server 2000
90 - SQL Server 2005
100 - SQL Server 2008/R2
110 - SQL Server 2012
120 - SQL Server 2014
130 - SQL Server 2016
140 - SQL Server 2017


不对,查出来的数据是100,与所列的数据是对应的[/quote]

参考
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017

关键部分贴出来如下:
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
[/quote]

设置了也没有作用
stacksoverflow 2019-03-05
  • 打赏
  • 举报
回复
引用 12 楼 奋斗的镇屌 的回复:
[quote=引用 10 楼 stacksoverflow 的回复:] Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available. View or Change the Compatibility Level of a Database In T-SQL you can check it like this: USE AdventureWorks2012; GO SELECT compatibility_level FROM sys.databases WHERE name = 'AdventureWorks2012'; GO Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?: 65 - SQL Server 6.5 70 - SQL Server 7.0 80 - SQL Server 2000 90 - SQL Server 2005 100 - SQL Server 2008/R2 110 - SQL Server 2012 120 - SQL Server 2014 130 - SQL Server 2016 140 - SQL Server 2017
不对,查出来的数据是100,与所列的数据是对应的[/quote] 参考 https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017 关键部分贴出来如下: ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 10 楼 stacksoverflow 的回复:
Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available.

View or Change the Compatibility Level of a Database

In T-SQL you can check it like this:

USE AdventureWorks2012;
GO

SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks2012';
GO
Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?:

65 - SQL Server 6.5
70 - SQL Server 7.0
80 - SQL Server 2000
90 - SQL Server 2005
100 - SQL Server 2008/R2
110 - SQL Server 2012
120 - SQL Server 2014
130 - SQL Server 2016
140 - SQL Server 2017


不对,查出来的数据是100,与所列的数据是对应的
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 10 楼 stacksoverflow 的回复:
Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available.

View or Change the Compatibility Level of a Database

In T-SQL you can check it like this:

USE AdventureWorks2012;
GO

SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks2012';
GO
Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?:

65 - SQL Server 6.5
70 - SQL Server 7.0
80 - SQL Server 2000
90 - SQL Server 2005
100 - SQL Server 2008/R2
110 - SQL Server 2012
120 - SQL Server 2014
130 - SQL Server 2016
140 - SQL Server 2017



查出来的数据是空的,然后呢?怎么设置?
stacksoverflow 2019-03-05
  • 打赏
  • 举报
回复
Check the database compatibility level. OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available. View or Change the Compatibility Level of a Database In T-SQL you can check it like this: USE AdventureWorks2012; GO SELECT compatibility_level FROM sys.databases WHERE name = 'AdventureWorks2012'; GO Here is a list of compatibility levels taken from How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?: 65 - SQL Server 6.5 70 - SQL Server 7.0 80 - SQL Server 2000 90 - SQL Server 2005 100 - SQL Server 2008/R2 110 - SQL Server 2012 120 - SQL Server 2014 130 - SQL Server 2016 140 - SQL Server 2017
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 8 楼 stacksoverflow 的回复:
这个应该能解决你的问题
https://stackoverflow.com/questions/42263984/incorrect-syntax-near-offset-command

打不开,,能不能贴一下解决方案
stacksoverflow 2019-03-05
  • 打赏
  • 举报
回复
这个应该能解决你的问题 https://stackoverflow.com/questions/42263984/incorrect-syntax-near-offset-command
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 3 楼 stacksoverflow 的回复:
引用 2 楼 奋斗的镇屌 的回复:
[quote=引用 1 楼 stacksoverflow 的回复:]
mysql和sqlserver的OFFSET语法不一样,检查一下sql语句。
使用的是hql自动生成的。与sql无关吧,方言是org.hibernate.dialect.SQLServer2008Dialect

把生成的sql打出来,在sqlserver上跑一下,看看有没有问题,或者贴上来让大家分析一下。[/quote]
SELECT
terminalin0_.OID AS OID1_65_,
terminalin0_.IS_ABLE AS IS_ABLE2_65_,
terminalin0_.DISTRICT_CODE AS DISTRICT3_65_,
terminalin0_.TERMINAL_ADD AS TERMINAL4_65_,
terminalin0_.TERMINAL_CODE AS TERMINAL5_65_,
terminalin0_.TERMINAL_CPU_CODE AS TERMINAL6_65_,
terminalin0_.TERMINAL_DISK_CODE AS TERMINAL7_65_,
terminalin0_.TERMINAL_INSTALLATION_TIME AS TERMINAL8_65_,
terminalin0_.TERMINAL_IP AS TERMINAL9_65_,
terminalin0_.TERMINAL_MAC AS TERMINA10_65_,
terminalin0_.TERMINAL_NAME AS TERMINA11_65_,
terminalin0_.TERMINAL_POSITION AS TERMINA12_65_,
terminalin0_.TERMINAL_REGISTER_STATUS AS TERMINA13_65_,
terminalin0_.TERMINAL_REGISTER_TIME AS TERMINA14_65_
FROM
T_TERMINAL_INFO terminalin0_
WHERE
1 = 1
AND terminalin0_.TERMINAL_REGISTER_STATUS =? OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;生成的sql
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 5 楼 奋斗的镇屌 的回复:
看了一下,hibernate的方言包里面没有org.hibernate.dialect.SQLServer2008Dialect
看错了。。。还是有
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
看了一下,hibernate的方言包里面没有org.hibernate.dialect.SQLServer2008Dialect
bcsflilong 2019-03-05
  • 打赏
  • 举报
回复
把数据库方言改一下 是不是写原生SQL了
stacksoverflow 2019-03-05
  • 打赏
  • 举报
回复
引用 2 楼 奋斗的镇屌 的回复:
引用 1 楼 stacksoverflow 的回复:
mysql和sqlserver的OFFSET语法不一样,检查一下sql语句。
使用的是hql自动生成的。与sql无关吧,方言是org.hibernate.dialect.SQLServer2008Dialect
把生成的sql打出来,在sqlserver上跑一下,看看有没有问题,或者贴上来让大家分析一下。
微瞰技术 2019-03-05
  • 打赏
  • 举报
回复
引用 1 楼 stacksoverflow 的回复:
mysql和sqlserver的OFFSET语法不一样,检查一下sql语句。
使用的是hql自动生成的。与sql无关吧,方言是org.hibernate.dialect.SQLServer2008Dialect
stacksoverflow 2019-03-05
  • 打赏
  • 举报
回复 1
mysql和sqlserver的OFFSET语法不一样,检查一下sql语句。

67,549

社区成员

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器,保护我们的业务核心组件(中间件),以延续它的生命力,而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧