RedMine and Sql Server 2005

Added by Chris Taylor over 13 years ago

I've not used Ruby, Rails or RedMine before, but liked the look of it, so I've been trying to get the latest SVN version running against a Sql Server 2005 database using a Subversion repository.

I've run into a few problems, but have now got something working. For anyone else wanting to try this, this is what I've done:

  • installed sql server support using http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer
  • installed the database using the RedMine install instructions (renamed migrations 91 and 92 and applied the changes for these manually! See defect #1652)
  • use the sqlserver_adapter.rb file in patch #223
  • apply http://dev.rubyonrails.org/ticket/6254 to sqlserver_adapter.rb (Sql Server 2005 doesn't have limit and offset - the approach in #223 caused errors like "Incorrect syntax near '\.'" when viewing pages with paged lists.
  • changed all of the 'text' and 'image' fields in the main RedMine database to varchar(MAX) fields. This avoids the errors that otherwise crop up from comparing varchars with text fields.
  • removed a couple of specified 'order-by' clauses when working with Subversion as it looks like they were also getting applied elsewhere, causing Sql Server to error.
  • applied a nasty time-to-date conversion hack to get the calendar and Gantt chart working. This was otherwise displaying nothing (calendar) or throwing up date-time conversion errors (Gantt chart). I get the feeling it should be possible to apply this to the sqlserver_adapter.rb file, but I'm not yet sure how!

Anyway, I've attached a patch for my working version, and my sqlserver_adapter.rb file, if anyone else wants to give this a go. Bear in mind that this is the first time I've done anything in Ruby! If there's an easier way of setting this up, please let me know!

sqlserver_adapter.rb Magnifier - Sqlserver_adapter file for use with RedMine (24.2 KB)

redminePatch.patch Magnifier - Patch against rev1684 for Sql Server 2005. (6.08 KB)

Replies (4)

RE: RedMine and Sql Server 2005 - Added by Chris Taylor over 13 years ago

Please ignore the post above - I've now got a more streamlined method for doing this.
This works with Sql Server 2005 - I've only tested it with mixed mode authentication though.

(If someone could look at the patches, I'd be grateful. They resolve tiny bugs in the code that don't
show up when running against the official databases.)

a) Install Sql Server support for Rails using http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer
b) Replace the sqlserver_adapter.rb file with the one attached.
(This includes the patches from #223, http://dev.rubyonrails.org/ticket/6254, and some further changes)
c) Follow steps 1-3 in http://www.redmine.org/wiki/redmine/RedmineInstall (creating a Sql Server 2005 database in step 2!)
d) In the database you created, run the following:

CREATE TYPE [dbo].[date] FROM [datetime] NULL
CREATE TYPE [dbo].[time] FROM [datetime] NULL

e) Apply the attached Migrations patch (fixes migrations 91 and 92 for Sql Server)
f) Apply the attached Orderby patch (fixes instances where the same column is used multiple times in an orderby clause)
g) Follow the remaining installation steps.

You should now have a version of Redmine running against Sql Server.

sqlserver_adapter.rb Magnifier (22.4 KB)

sqlserver_adapter.rb.diff Magnifier - Diff from official version (19.4 KB)

OrderbyPatch1709.patch Magnifier (1.75 KB)

MigrationPatchr1709.patch Magnifier (1.53 KB)

RE: RedMine and Sql Server 2005 - Added by Kirill Ponomarev over 12 years ago

  • How you setup Redmine for using nvarchar type and UTF-8?
  • How did you decide to problem #1683?

RE: RedMine and Sql Server 2005 - Added by Chris Taylor over 12 years ago

It was some time ago - I decided to use MySql in the long run rather than worry about patching Redmine to get it working with Sql Server 2005.

I only needed to use varchar, rather than nvarchar. However, I'd start by looking in sqlserver_adapter.rb - try changing lines 199 and 200 to nvarchar instead of varchar. You may need to do something with the 'select' functions around line 516. As I say though, I've not tried this so no idea what would be involved.

As far as I can remember, #1683 was resolved by introducing the 'date' type and the 'time' type mentioned in step d) into the Sql Server database, and then modifying sqlserver_adapter.rb - it wasn't necessary to modify Redmine itself.
(This fixed a lot of other issues too - I think a date in Redmine (01/01/2009) was being converted into a Sql Server 2005 datetime (01/01/2009 00:00:00) - when the data came back from the database there was an additional time element that Redmine wasn't expecting).

RE: RedMine and Sql Server 2005 - Added by Marcos Vinícius Lourenço de Deus over 10 years ago

Hello Chris Taylor.

I was hoping that you could help me, or at least answer this post, to see if what I want still possible.
Is it possible to run redmine 1.2.0 on windows, using MSSQL 2005 as database?
(actually, i've already made redmine run on windows, but I didn't have the same lucky when I tried to change the database from MYSQL to SQL)
I've tried everything, and tried to find it on internet, but I wasn't sucessfull.
If you know how to make it happen, could you give a little help?

thanks.

(1-4/4)