Categories
Contact Doug!
Learn About Doug!
View Doug Boude's online resume
updated 11/18/2009

View Doug Boude's profile on LinkedIn
Link to me!

Follow Doug Boude on Twitter
Follow me!

Be Doug's friend on Facebook
Befriend me!
(I promise not to follow you home)
OO Lexicon
Chat with Doug!
Recent Entries
You may also be interested in...
Web Hosting

<< July, 2014 >>
SMTWTFS
12345
6789101112
13141516171819
20212223242526
2728293031
Search Blog

Recent Comments
Re: Equivalent of SQL "TOP X" in Oracle (by Mark Foster at 7/07 4:04 PM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Thomas at 6/26 4:41 AM)
Re: One Shot Query to Recalculate Orderby Field - MySQL (by gary at 6/17 6:46 PM)
Re: DON'T GET SICK IN ARKANSAS! (by r. wood at 5/25 12:00 AM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Andrew at 4/30 6:14 AM)
Re: Basic Ajax Select List Filter in PHP (by good at 2/04 5:26 AM)
Re: Family Law: The Weapon of Choice for Woman Scorned (by swalker at 2/03 2:15 AM)
Re: Approaches to Building Strings: The Imploding Array (by bantal silikon at 2/01 9:44 PM)
Re: Disappearing IE Popup Window During Save/Open Dialog (by AddisonDean at 1/15 9:59 AM)
Re: My Top 20 Life Lessons for Boys and Young Men (by Alex at 1/13 8:45 PM)
Re: Array Loop Modifications in CFSCRIPT (by Alex at 11/25 11:18 AM)
Re: Array Loop Modifications in CFSCRIPT (by Abram at 11/14 11:32 PM)
Re: Recursive Functions in ColdFusion (by Dwayne at 10/25 3:47 PM)
Re: Porting Coldfusion Code to Mura (by dh at 10/16 10:14 AM)
Re: Viewing Option Text (in IE7) that's Wider than the Select List (by Devil May Cry at 9/26 1:38 AM)
Re: Why I Hate ORMs (a solicited rant) (by guideX at 9/12 11:38 PM)
Re: Recursive Functions in ColdFusion (by KP at 8/08 7:13 PM)
Re: American Airlines, YOU SUCK! (by Alison at 7/23 4:48 PM)
Re: SQL Forward Engineering with Visio 2003 Professional (by Harshad at 7/11 9:17 AM)
Re: Disappearing IE Popup Window During Save/Open Dialog (by LZ at 4/20 7:58 AM)
Archives
Photo Albums
Funnies (5)
Family (3)
RSS

Powered by
BlogCFM v1.11

07 November 2008
SQL Forward Engineering with Visio 2003 Professional
made a little simpler

Finding the shortest route from diagram to tangible product can be tricky depending on the tool you use to create your Entity Relationship (database) diagrams. I'm using Visio 2003 Professional for mine, and so I went on the hunt for a way to transform diagrams into SQL 2005 Scripts (Visio Professional doesn't include any export features for ER diagrams). The final solution isn't the absolutely most elegant, but I'd call it live-able at least. The basic steps are:


1. Export your diagram to XML;
2. Apply an XSL stylesheet to the XML that generates SQL 2005-compatible scripts.

Exporting XML from VISIO 2003 Pro

Visio Professional doesn't include the luxury of exporting to XML, so I discovered a very sweet third party tool aptly called "Toolbox" made by a generous company named Orthogonal (the tool is free!). Installing Toolbox creates a floating toolbar in Visio that allows you to export the currently open ER diagram. To install simply download the Orthogonal "Toolbox" product, run the setup, restart Visio (if it was open during install), and open an ER diagram. The toolbox should be floating there in your window. If you don't see it, you can go to View,Toolbars and make sure it is checked as being visible.

Creating the SQL Scripts

In my scenario I opted to just go the route of using Internet Explorer to view the finished XML code since it will automatically apply any referenced stylesheets. Because Toolbox let us choose a stylesheet to apply at the time of export, a reference was added to the XML file so when we view it in IE we see a nicely formed SQL 2005 script to create our tables and their relationships. Here's a snapshot of the Toolbox dialog box:

The finished product:

xml created by appling xslt to Toolbox XML file

On the subject of the XSLT...Orthagonal provides an XSLT file that creats the script, but without any linebreaks or anything to make it readable. I found a modification to this XSLT on this guy's site , and I modified it a little further in order to account for autoincrementing identity fields, table names that might also be reserved words (who ever does that?), and default values for bit type fields. My version is at the end of this post.

Final Notes
The XML exported by Toolbox doesn't seem to capture anything you put into the "default value" for entity columns. Because of this, I hard-code a rule into my XSLT that says if the field type is bit and NULL is NOT allowed, make the default value true. Also, although Visio does allow you to say that an column is an identity column, it does NOT give you a way to specify that it should autoincrement. I added one more rule to my XSLT that says if the column is INT and is IDENTITY, script out the autoincrement functionality as well.

The XSLT file has some HTML embedded in it in order to allow for an eye appealing output when viewed in IE. If you are going to be applying the stylesheet via another mechanism, you'll probably want to go through the XSLT and strip out the generated HTML first.

TIP: If you weren't aware of it, you can have multiple pages within one Visio ERD document (as in the screenshot at the beginning of this post). The Toolbox export will export one xml file for every diagram on every tab, so if you want to get things done in one shot just consolidate your Visio documents into one!

That should be it. Here are the relevant links for you to get started creating SQL 2005 scripts from your Visio 2003 ER diagrams:
my version of the XSLT file

link to get Toolbox
sample of the XML produced by Toolbox (without xslt applied)

sample of the XML produced by Toolbox (WITH xslt applied!)




Posted by dougboude at 6:04 PM | PRINT THIS POST! |Link | 27 comments
Subscription Options

You are not logged in, so your subscription status for this entry is unknown. You can login or register here.

Re: SQL Forward Engineering with Visio 2003 Professional
Doug,
This is great. Do you know if this will work with the Office 2007 Version?
Posted by gerald guido on November 15, 2008 at 11:46 AM

Re: SQL Forward Engineering with Visio 2003 Professional
@Geraldo: I'm not sure about the 2007 version...I don't own it and haven't had an opportunity to try it. But it can't hurt to install the export tool and see! :)
Posted by dougboude on November 15, 2008 at 1:40 PM

Re: SQL Forward Engineering with Visio 2003 Professional
This is a most welcome thing to stumble across as I do prefer to do my database layout outside the db itself.

One change to the XSLT though. The table names are escaped in table creation but not in the ALTER statements. Yes, I have a table named user, which is a reserved word.

So I had to add brackets around the xslt selects for the ALTER TABLE commands and the REFERENCES command.

Cheers.
Posted by Judah McAuley on December 1, 2008 at 2:00 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Thank you so much for the information. We just spent 2 days finding out Microsoft has removed forward engineering form the Visio platform.

By the way it seams to install and run fine in Visio 2007.

Anyone know how to get .Net 1.1 to allow access to the registry under Vista? This tool works fine on my developers XP machine but seams to hate my Vista box.
Posted by Peter J. Smith on December 11, 2008 at 5:30 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Awesome ! Worked just fine on Visio 2007 Professional. Thanks a lot - this saved me oodles of time. Didn't spot that I didn't have a generate SQL feature before I began my model...
Posted by Andre Du Plessis on March 23, 2009 at 6:27 AM

Re: SQL Forward Engineering with Visio 2003 Professional
for more info and useful stuff regarding this topic, i found a blog post referencing THIS post that has an XSLT for producing postgreSQL, plus some more tips on things to do within your Visio ERD diagram to make the results cleaner: http://richard.gluga.com/2009/03/no-erd-to-sql-code-generation-in-visio.html
Posted by dougboude on April 8, 2009 at 10:24 AM

Re: SQL Forward Engineering with Visio 2003 Professional
and one more link to another blog post that has a couple of other versions of the XSLT... http://blog.donnfelker.com/post/Simplifying-Database-Modeling-in-Visio-20022003-through-SQL-Generation.aspx
Posted by dougboude on April 8, 2009 at 10:34 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Has anyone had any luck getting it to generate SQL for triggers or for column default constraints?
Posted by Matthew on June 17, 2009 at 2:33 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Many thanks for this. I was so frustrated after spending ages creating the ERD in Visio under the assumption that I could just generate the DDL. Worked fine for me with Visio 2007.
Posted by Gareth Hill on August 5, 2009 at 4:19 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Thanks tons! I hadn't used Visio in a while and was shocked that the Generate option was no longer there (thanks Microsoft). You saved me a boatload of time!
Posted by Chris Rasmussen on January 29, 2010 at 10:05 AM

Re: SQL Forward Engineering with Visio 2003 Professional
@chris - you're welcome, man! That's what I was hoping to do...save someone a little time ;)
Posted by dougboude on January 29, 2010 at 10:40 AM

Re: SQL Forward Engineering with Visio 2003 Professional
This information is really useful. I got it to install on my windows 7 laptop, but I think I am getting the same problem that Peter got under Vista. It says something like “Application attempted to perform an operation not allowed by the security policy. Any ideas?
Posted by justin on February 2, 2010 at 11:03 AM

Re: SQL Forward Engineering with Visio 2003 Professional
@justin - my thought would be to disable the user account control. That thing has given me more trouble than its worth and sometimes interferes silently so your process fails but gives you no clue as to why. Here's one place that explains how to do it: http://www.howtogeek.com/howto/windows-vista/disable-user-account-control-uac-the-easy-way-on-windows-vista/
Posted by dougboude on February 2, 2010 at 11:53 AM

Re: SQL Forward Engineering with Visio 2003 Professional
@dougboude - Thanks. I have found something that works for me. I just run the app in XP mode, and everything works. I am so glad that I have found this blog entry.
Posted by justin on February 2, 2010 at 3:21 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Doug, this is incredibly useful. Been at a client site without any normal modelling tools and only visio (2007) to create a data model for them. Now they're getting ER Studio and was getting worried I'd have to type it all back in again, this saves me the trouble. Cheers
Posted by gjones1199 on March 2, 2010 at 4:11 AM

Re: SQL Forward Engineering with Visio 2003 Professional
what about generation of indexes and check constraints?
I tried this and it seems it doesn't generate it...
Posted by Busi on May 9, 2010 at 11:21 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Hi dude,
you have done good work!

Regards
Posted by Munish Singla on July 2, 2010 at 12:10 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Thanks a lot, Doug. It is really helpful.
Posted by fxiong416 on October 10, 2010 at 9:17 PM

Re: SQL Forward Engineering with Visio 2003 Professional
That's great Doug, thanks for that; I may use it if I can find the time to adapt it to MySQL. There's only one very basic flaw when trying your example: you should NOT name it with a .txt extension because that way IE (or Firefox, etc. for that matter) won't process it as an XML and therefore the transformation is not applied... which gives the wrong impression it doesn't work (but it does). Bye now. A.R.
Posted by alainr345 on December 5, 2010 at 3:46 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Thank you so much man, It works like a charm
Posted by Noel on May 6, 2011 at 4:43 PM

Re: SQL Forward Engineering with Visio 2003 Professional
WOW kool. Thank you so much man, It works fine!
Posted by freddy villamil on November 9, 2011 at 2:49 PM

Re: SQL Forward Engineering with Visio 2003 Professional
Hi guys, since stumbling on this article recently, I have been scouring the internet to try to find a copy of toolbox to no avail - the orthogonal site is permanently down and all links that I've found point back to the site. I've even tried sending a note to Scott Becker, the developer, buthave had not response. Anyone here know where I can download it from a mirror, or archive, or even send me a copy?
Cheers
Posted by Rama on January 10, 2012 at 11:05 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Can you post link to Orthogonal Toolbox Visio plugin? Their website is permanantly down. Thanks
Posted by Leo on February 13, 2012 at 10:55 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Posted by Gyromite7 on July 11, 2012 at 11:02 AM

Re: SQL Forward Engineering with Visio 2003 Professional
We are half way through in 2013 and your solution still is useful.
Thanks
Harshad
Posted by Harshad on July 11, 2013 at 9:17 AM

Re: SQL Forward Engineering with Visio 2003 Professional
What about the .net framework? I have an installed version of 4.5, but the installer requires a very old version.
Who can is use this tool in this environment?
Posted by Andrew on April 30, 2014 at 6:14 AM

Re: SQL Forward Engineering with Visio 2003 Professional
Hi

I have installed Orthogonal Toolbox for Visio 2003. My Problem is, i can't find the Toolbar entry. When I try to manually add a new Toolbar Entry for Orthogonal Toolbox it says that this entry allready exists...

Any help?
Thanks,
Thomas
Posted by Thomas on June 26, 2014 at 4:41 AM

Name:   Required
Email:   Required your email address will not be publicly displayed.

Want to receive notifications when new comments are added? Login/Register for an account.

Time to take the Turing Test!!!

Eighteen plus Zero equals
Type in the answer to the question you see above:

Your comment:

Sorry, no HTML allowed!