Reverse and forward engineering databases in Visio 2010

Written by Ingmar Verheij on January 5th, 2012. Posted in Microsoft SQL

A great built-in feature in Microsoft Office Visio 2010 is the ‘Reverse Engineer’ feature. With this feature you can create a database model from an existing database or a Microsoft Excel worksheet. This makes it very easy to document the structure of a database, redistribute or publish It on Microsoft SharePoint.

Unfortunately Microsoft has removed the ability to forward engineer the database model to a database, which did exist in Microsoft Visio 2003 Enterprise Architect-edition.

Fortunately Alberto Ferrari created an Visio 2010 plugin that can forward engineer an data model to a T-SQL script. The Visio Forward Engineer project is on CodePlex and can be found here (CodePlex is DEAD, you can find alternatives here).

In this blog post I will demonstrate how to reverse engineer a database from Microsoft SQL server, and how to forward engineer a database model diagram back to a Microsoft SQL server.

Remove all tables from database

Written by Ingmar Verheij on June 10th, 2011. Posted in Microsoft SQL

SQL logo

During the setup of a LoadTest with the DeNamiK LoadGen I created loads of tables in a SQL database. The content of the tables was irrelevant, pure for testing purposes, and needed to be cleaned.

Deleting them all by hand, one by one, would be tremendous job. As an IT guy I dislike repeating jobs, so I searched for an automated solution.

Since I have limited permissions I don’t want to delete the database and recreate it. This would require more permissions than available, requires me to reconfigure the permissions (etc) and would raise questions by the DBA’s.

There is an undocumented stored procedure called ‘sp_MSforeachtable’. This stored procedure executes a command for each table in the database, for instance ‘DROP TABLE’.

The following command will delete all tables in the active database (use with caution!)

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Install SCOM 2007R2 on SQL 2008R2 (including reporting server)

Written by Ingmar Verheij on February 21st, 2011. Posted in Microsoft SQL, Operations Manager

When installing the Microsoft System Center Operations Manager (SCOM) 2007 R2 database on a Microsoft SQL Server 2008 R2 the installation failes because a prerequisite is not met:

This is caused by an outdated installer which doesn’t recoginize SQL 2008R2. Altough installing on SQL 2005 / 2008 will prevent this message there is an alternative.

SQL : Invoke or BeginInvoke cannot be called on a control until the window handle has been created..

Written by Ingmar Verheij on May 18th, 2010. Posted in Microsoft SQL

During the setup of a SQL Server 2008 (R2) I got the following error: