Roller with MS SQL 2k (Microsoft SQL Database)

At work we are using Roller
for our internal blogging server. When doing the install, I opted to
use MS SQL 2k as the database instead of HSQLDB or MySQL because that's
what we use here. In order to get Roller up and running, I had to make
some changes.

Here are the changes required to run Roller with MS SQL 2k:

  1. edit the
    1. hibernate.dialect = net.sf.hibernate.dialect.SQLServerDialect
    2. hibernate.query.substitutions TRUE=1 # use int values for boolean
    3. hibernate.query.substitutions FALSE=0 # use int values for boolean
  2. edit any prepared statements that use “limit” and change prepared statements to use 1/0 for true/false
    1. this occurs in these files
      1. org\roller\business\hibernate\
      2. org\roller\business\utils\
  3. edit the createdb.sql script
    1. use datetime instead of timestamp
    2. in the webpage table, set websiteid as a varchar(48) instead of varchar(255)
    3. remove the size values in the alter and create index statements
      1. e.g. “create index usercookie_username_index on usercookie( username );”
        instead of “create index usercookie_username_index on usercookie(
        username(40) );”

That's it (unless I forgot something, it was a while ago that I did
this), hope that it helps. I'll be posting more on Roller, blogging at
work, and reasons why Roller is cool (it rocks!) later.

update:  also see