Defect #29819

3.4.6, SQLSERVER 2014. Incorrect syntax near 'OFFSET'

Added by Nathan Biggs about 3 years ago. Updated over 1 year ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution: Affected version:3.4.6

Description

I'm trying to setup a new Redmine instance of version 3.4.6 using SQLSERVER 2014. When I run rake db:migrate I get the following error.

62 InsertBuiltinRoles: migrating =========================================
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

TinyTds::Error: Incorrect syntax near 'OFFSET'.: EXEC sp_executesql N'SELECT 1 AS one FROM [roles] WHERE [roles].[name] = N''Non member'' COLLATE Latin1_General_CS_AS_WS ORDER BY [roles].[id] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY'
C:/Sites/redmine346/db/migrate/062_insert_builtin_roles.rb:6:in `up'
C:in `migrate'

Caused by:
ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near 'OFFSET'.: EXEC sp_executesql N'SELECT 1 AS one FROM [roles] WHERE [roles].[name] = N''Non member'' COLLATE Latin1_General_CS_AS_WS ORDER BY [roles].[id] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY'
C:/Sites/redmine346/db/migrate/062_insert_builtin_roles.rb:6:in `up'
C:in `migrate'

Caused by:
TinyTds::Error: Incorrect syntax near 'OFFSET'.
C:/Sites/redmine346/db/migrate/062_insert_builtin_roles.rb:6:in `up'
C:in `migrate'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Any suggestions would be greatly appreciated

History

#1 Updated by Go MAEDA about 3 years ago

  • Status changed from New to Needs feedback

Does using the latest tiny_tds solve the problem? Could you try the steps below?

1. Update the version of tiny_tds from 1.0.5 to 2.1.2.

diff --git a/Gemfile b/Gemfile
index b9a176439..34300e052 100644
--- a/Gemfile
+++ b/Gemfile
@@ -66,7 +66,7 @@ if File.exist?(database_file)
       when /sqlite3/
         gem "sqlite3", "~>1.3.12", :platforms => [:mri, :mingw, :x64_mingw]
       when /sqlserver/
-        gem "tiny_tds", "~> 1.0.5", :platforms => [:mri, :mingw, :x64_mingw]
+        gem "tiny_tds", "~> 2.1.2", :platforms => [:mri, :mingw, :x64_mingw]
         gem "activerecord-sqlserver-adapter", :platforms => [:mri, :mingw, :x64_mingw]
       else
         warn("Unknown database adapter `#{adapter}` found in config/database.yml, use Gemfile.local to load your own database gems")

2. Run bundle update

3. Run bundle exec rake db:migrate again.

#2 Updated by Go MAEDA almost 3 years ago

  • Category changed from Issues to Database

#3 Updated by Go MAEDA over 1 year ago

  • Status changed from Needs feedback to Closed
  • Resolution set to Fixed

#4 Updated by Go MAEDA over 1 year ago

  • Status changed from Closed to New
  • Resolution deleted (Fixed)

Also available in: Atom PDF