Corrupted sql query to Microsoft SQL server is generated by Hibernate on Jboss as 7.1.1 Final
I've just upgraded from Jboss 5.1 to Jboss 7.1.1. I have the following ejb named query:
SELECT g FROM GlobalSetting g WHERE g.company=:co
It works fine with mysql. However, when I tried running against Microsoft SQL server I got the following error:
10:27:18,765 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8087-6) SQL Error: 156, SQLState: S0001
10:27:18,766 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8087-6) Incorrect syntax near the keyword 'as'.
I then turned on the hibernate sql trace and the following query is recorded where the ROW_NUMBER() function is inserted right in the middle of the column name of email_from_address.
11:47:31,639 INFO [stdout] (http--0.0.0.0-8087-6) Hibernate:
11:47:31,641 INFO [stdout] (http--0.0.0.0-8087-6) WITH query AS (select
11:47:31,642 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.global_setting_id as global1_471_,
11:47:31,642 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.created_by as created2_471_,
11:47:31,643 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.created_date as created3_471_,
11:47:31,644 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.modified_by as modified4_471_,
11:47:31,644 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.modified_date as modified5_471_,
11:47:31,645 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.status as status471_,
11:47:31,646 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.ad_auto_activate as ad7_471_,
11:47:31,646 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.auto_renew as auto8_471_,
11:47:31,647 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.command_optimization as command9_471_,
11:47:31,648 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.company_id as company25_471_,
11:47:31,649 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.date_format as date10_471_,
11:47:31,649 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.default_page_size as default11_471_,
11:47:31,650 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.dm_auto_activate as dm12_471_,
11:47:31,651 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email_auth_user as email13_471_,
11:47:31,651 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email_auth_user_pswd as email14_471_,
11:47:31,653 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email,
11:47:31,654 INFO [stdout] (http--0.0.0.0-8087-6) ROW_NUMBER() OVER (
11:47:31,654 INFO [stdout] (http--0.0.0.0-8087-6) ORDER BY
11:47:31,655 INFO [stdout] (http--0.0.0.0-8087-6) CURRENT_TIMESTAMP) as __hibernate_row_nr___from_address as email15_471_,
11:47:31,656 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email_port as email16_471_,
11:47:31,656 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email_server as email17_471_,
11:47:31,657 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.email_to_address as email18_471_,
11:47:31,658 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.jmx_ip as jmx19_471_,
11:47:31,658 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.jmx_port as jmx20_471_,
11:47:31,659 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.lifetime_license as lifetime21_471_,
11:47:31,660 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.map_license_client_id as map22_471_,
11:47:31,661 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.map_license_signature as map23_471_,
11:47:31,661 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.max_report_age as max24_471_
11:47:31,662 INFO [stdout] (http--0.0.0.0-8087-6) from
11:47:31,663 INFO [stdout] (http--0.0.0.0-8087-6) n4a_global_setting globalsett0_
11:47:31,663 INFO [stdout] (http--0.0.0.0-8087-6) where
11:47:31,664 INFO [stdout] (http--0.0.0.0-8087-6) globalsett0_.company_id=?) SELECT
11:47:31,665 INFO [stdout] (http--0.0.0.0-8087-6) *
11:47:31,666 INFO [stdout] (http--0.0.0.0-8087-6) FROM
11:47:31,666 INFO [stdout] (http--0.0.0.0-8087-6) query
11:47:31,667 INFO [stdout] (http--0.0.0.0-8087-6) WHERE
11:47:31,668 INFO [stdout] (http--0.0.0.0-8087-6) __hibernate_row_nr__ >= ?
11:47:31,668 INFO [stdout] (http--0.0.0.0-8087-6) AND __hibernate_row_nr__ < ?
Please help.