Tuesday, July 28, 2009

Forward Engineering from Visio 2007 without Enterprise Architect edition

To me, one of the most boggling, counter-intuitive and short-sighted decisions was to remove forward-engineering of databases from the base editions Microsoft Visio 2007.

One of the best third party add-ons to Visio therefore, is the Orthogonal Toolbox, a XML-based export utility that, with the right XSLT file, can be used to forward engineer your Visio database diagram.

It isn't perfect, but here's how:
  1. Close Visio, download and install the Orthogonal Toolbox: http://www.orthogonalsoftware.com/toolboxaddons.html
  2. Download this guy's XSLT file: http://www.dougboude.com/blog/1/2008/11/SQL-Forward-Engineering-with-Visio-2003-Professional.cfm
  3. Open your visio diagram, click on the new toolbox button.
  4. Select the XSLT file in the first box, select a target in the second.
  5. Done. Spits out a nice SQL file.
I had a problem with this tool however - including the attributes (the columns) caused the tool to cause a windows error. I was using Visio 2007 and Vista. Curiously enough, copying my entire diagram (control-A) and pasting it into a new Visio diagram solved that problem.

Also, I learned today that VS 2008 can import a SQL file, like the one generated here, into a database solution. Check constraints and defaults don't get put in, but its a big timesaver for PK's, FK's and tables.

Links:
http://www.orthogonalsoftware.com/toolboxaddons.html (dead)
http://www.dougboude.com/blog/1/2008/11/SQL-Forward-Engineering-with-Visio-2003-Professional.cfm
http://richard.gluga.com/2009/03/no-erd-to-sql-code-generation-in-visio.html (dead)

UPDATE: see comments for more information on generating DDL from a Visio doc

UPDATE: in Vista and Windows 7, try running Visio in XP SP2 compatibility mode.  This cleared up an error in Visio 2007: "Requested Registry Access Is Not Allowed"

UPDATE: If you want the orthogonal software download, try this link here.  Otherwise, try this: http://forwardengineer.codeplex.com/


"Avarice, the spur of industry" - David Hume

7 comments:

w said...

UPDATE:

Cannot get this to run on Windows 7, even with the copy-paste trick. Windows XP has no issues. Clearly, something with with the security "features" of Vista and more so with Win7 prevents this from running. The error I get in Win7 is Requested Registry access denied...

w said...

UPDATE:

More information on a way to do this:

http://sqlblog.com/blogs/alberto_ferrari/archive/2010/04/16/visio-forward-engineer-addin-for-office-2010.aspx

Roger James said...

Would you (or anyone else) happen to still have a copy of the Orthogonal Toolbox they could send me? The Orthogonal site has gone and the owner isn't answering emails.
I've had a look at the Alberto Ferrari one but that means updating the whole Office suite to 2010 version. I did try to test it on a borrowed pc that has 2010 (+ net4) but I couldn't get it to install.
So if anyone still has the Orthogonal Toolbox, I would very much appreciate it. Any reasonable costs would be defrayed.

w said...

Here's the download:

https://docs.google.com/open?id=0B34dqsXqkyf1NmEzMTJmNDEtNTkwNS00MTFhLTg5ZDMtMGI5N2JhNDczNDM0

RB said...

I am running visio 2010 I donwloaded the tool from
https://docs.google.com/open?id=0B34dqsXqkyf1NmEzMTJmNDEtNTkwNS00MTFhLTg5ZDMtMGI5N2JhNDczNDM0
Then I installed the dotnetfix
Then I had the Requested Registry access denied error
But I got around it by running the Visio 2010 as administrator. It complained about some other plugin, so I said yes to disabled it.
After that it works just fine.
Thank yoyu

Anonymous said...

Orthogonal Toolbox website is gone. A new option is available though.
http://forwardengineer.codeplex.com/

KM said...

Bless you. Link you provided above works - https://docs.google.com/open?id=0B34dqsXqkyf1NmEzMTJmNDEtNTkwNS00MTFhLTg5ZDMtMGI5N2JhNDczNDM0

I had lost the setup and I have a new machine and this orthogonal toolbox is indispensable for me.