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