pointers, solutions and scripts for the SQL DBA
Not intended to replace common sense

7/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


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:

  1. 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...

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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.

    ReplyDelete
  4. Here's the download:

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

    ReplyDelete
  5. 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

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

    ReplyDelete
  7. 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.

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.