Leigh,
I'm very happy to help where I can even though it's not an active project for me. I delighted that you're finding the work valuable. The OpenACS data model deserves to be stolen by as many other projects (and databases) as possible.
Just so you know, I did not hand-write very much of the ms-sql code - I wrote a very ugly compiler that parsed postgres and emitted transact sql. That explains a lot of the weirdness that you'll see. I think I made all varchars varchar(8000) ... I think this was to simplify a type mismatch between postgres and mssql but I don't remember specifics (i.e. 8000 handled some corner cases that my translator didn't or couldn't).
I also didn't bothere to write a parser/tokenizer for trigger syntax, as there were so few triggers it was easier to do by hand.
Speaking of triggers, for the tree stuff, in the codebase I translated (4.1?), openacs used to use triggers to maintain a parent-child lookup table to allow easy hierarchical queries (delete all children of this object). I think that delete object was rewritten when Postgres caught up with Oracle and implemented hierarchical query support in plpgsql.
with MSSQL 2005, SQL Server now has similar native support for tree queries... is that what you're targetting? You could either use that, or try to use the old triggers to maintain the parent-child table of yore.
(here's a writeup of the technique)
http://www.sqlteam.com/item.asp?ItemID=8866
I'm curious, what's your motivation for doing this?
John
