I have been looking for a good few hours this Easter holiday for an effective way of designing an entity relationship diagram (ERD) in Visio and transforming it into a real SQL Server CE (Compact Edition – e.g. a database embedded in a .NET application executable, executing with the application at runtime) database shell. Visio is an excellent diagramming tool for this purpose, but I was hunting for hours for an export button to export the shape details to a database or DDL.
Now I have found an effective solution, I thought I would share what I found in the hope that someone else find’s this useful – feel free to post a comment to let me know!
You will need:
- Microsoft Visio 2010 (any edition)
- Forward Engineer for Visio 2010
- Microsoft SQL Server Management Studio Express 2008
The setup version for the latest version (1.0.6) of Forward Engineer (http://forwardengineer.codeplex.com/) is 64bit, despite containing a 32bit/x86 add-on to Visio. If you run the setup.exe on 32bit/x86 Windows you will get the error message “The version of this file is not compatible with the version of Windows you’re running. Check your computer’s system information to see whether you need an x86 (32-bit) or x64 (64-bit) version of the program, and then contact the software publisher.” To get around this issue you will need to first extract the contents (e.g. using WinRAR) before running the enclosed 32bit installer.
If you already have SQL Server 2008 Express installed, you may have problems installing Microsoft SQL Server 2008 Management Studio Express. You will need to perform a new installation (as opposed to adding features to an existing one) and (re)install the service pack 2 update (available through Windows Update) afterwards.
You can then design your database in Visio, use the Forward Engineer ribbon add-in to check and output your database as SQL DDL and execute this SQL in SQL Server Management Studio Express.
If you are using SQL CE then you will need to read on…
Note that Forward Engineer outputs code designed for SQL Server Express, not SQL Server CE. You will therefore need to manually alter the DDL to remove the syntax which SQL CE doesn’t support (don’t worry, this isn’t much work).
You will need to remove any occurrences of the following syntax:
- IF EXISTS…DROP TABLE
- CHAR (change this to NVARCHAR)
- WITH CHECK
- WITHOUT CHECK
You will also need to remove the table prefix e.g. [dbo]. before each table name. You may also need to add additional GO commands between ALTER TABLE commands.
Hopefully with these changes your script should execute, let me know if you have any problems or need further guidance