Invalid SQL Statement

[svrolanski]

Using Redmine 3.4.2 with jruby

I'm trying to migrate from MySQL to MSSQL, i used database migration tool from Microsoft. The migrated data seems fine, but I get this invalid SQL statments sometimes. The MSSQL is version 2016, but I set the compatibility level to 110 (SQLSERVER 2012).

ActiveRecord::JDBCError: Sintaxe incorreta próxima à palavra-chave 'with'. Se esta instrução for uma expressão de tabela comum, uma cláusula xmlnamespaces ou uma cláusula de contexto de controle de alterações, a instrução anterior deverá terminar com ponto-e-vírgula.: SELECT [issues].[id] FROM [issues] WITH(UPDLOCK) WHERE (root_id IN (SELECT root_id FROM issues WHERE id IN (N'767'))) ORDER BY [issues].[id] ASC WITH(UPDLOCK)

In this case there is an extra 'WITH' at the end.

Also when I try to create a new database and get the same error:

@== 105 BuildProjectsTree: migrating =========================================== rake aborted! StandardError: An error has occurred, this and all later migrations canceled:

ActiveRecord::JDBCError: Sintaxe incorreta próxima à palavra-chave 'with'. Se esta instrução for uma expressão de tabela comum, uma cláusula xmlnamespaces ou uma cláusula de contexto de controle de alterações, a instrução anterior deverá terminar com ponto-e-vírgula.: SELECT [projects].[id] FROM [projects] WITH ORDER BY [projects].[id] ASC WITH arjdbc/jdbc/RubyJdbcConnection.java:888:in execute_query' /home/tomcat/redmine-3.4.2/lib/redmine/nested_set/project_nested_set.rb:139:in rebuild_tree!' /home/tomcat/redmine-3.4.2/lib/redmine/nested_set/project_nested_set.rb:138:in rebuild_tree!' /home/tomcat/redmine-3.4.2/db/migrate/105_build_projects_tree.rb:3:in up' /opt/jruby/bin/bundle:23:in `(root)' Tasks: TOP => db:migrate (See full trace by running task with --trace) @

bundle install output: Using rake 12.2.1 Using i18n 0.7.0 Using minitest 5.10.3 Using thread_safe 0.3.6 (java) Using tzinfo 1.2.4 Using activesupport 4.2.8 Using builder 3.2.3 Using erubis 2.7.0 Using nokogiri 1.6.8.1 (java) Using rails-deprecated_sanitizer 1.0.3 Using rails-dom-testing 1.0.9 Using crass 1.0.3 Using loofah 2.1.1 Using rails-html-sanitizer 1.0.3 Using actionview 4.2.8 Using rack 1.6.8 Using rack-test 0.6.3 Using actionpack 4.2.8 Using globalid 0.4.1 Using activejob 4.2.8 Using mini_mime 1.0.0 Using mail 2.7.0 Using actionmailer 4.2.8 Using actionpack-xml_parser 1.0.2 Using activemodel 4.2.8 Using arel 6.0.4 Using activerecord 4.2.8 Using activerecord-jdbc-adapter 1.3.24 Using jdbc-jtds 1.3.1 Using activerecord-jdbcmssql-adapter 1.3.24 Using addressable 2.4.0 Using bundler 1.16.1 Using coderay 1.1.2 Using concurrent-ruby 1.0.5 (java) Using css_parser 1.6.0 Using htmlentities 4.3.4 Using thor 0.20.0 Using railties 4.2.8 Using jquery-rails 3.1.4 Using jruby-jars 1.7.27 Using jruby-rack 1.1.21 Using mime-types 2.99.3 Using mimemagic 0.3.2 Using net-ldap 0.12.1 Using protected_attributes 1.1.4 Using ruby-openid 2.3.0 Using rack-openid 1.4.2 Using sprockets 3.7.1 Using sprockets-rails 3.2.1 Using rails 4.2.8 Using rails-html-sanitizer-jruby 1.0.0 from git://github.com/jkraemer/rails-html-sanitizer-jruby.git (at master@341880b) Using rbpdf-font 1.19.1 Using rbpdf 1.19.3 Using request_store 1.0.5 Using roadie 3.2.2 Using roadie-rails 1.1.1 Using rubyzip 1.2.1 Using warbler 1.4.10

[kares]

thanks, could you please try isolating the issue in a simple test case as this is too broad of a report.