Reverse Engineering for Database Diagramming in Visio with SQL Server 2008

Question

I need to reverse engineer a Microsoft SQL Server 2008 in order to create a Microsoft Visio 2007 Database Model Diagram. So I choose Reverse Engineer from the Database menu to connect to the DB.

I configured the Microsoft SQL Server Visio driver so that is uses SQL Server Native Client 10.0 as the ODBC driver. Afterwards I created a User DSN which connects to my DB. This DSN works (at least the provided test is successful). After clicking next in the Reverse Engineer Wizard, Visio kindly asks for my credentials which I properly provide, but after clicking OK I receive the following message:

The currently selected Visio driver is not compatible with the data source.

I tried using the old SQL Server ODBC driver, by also reconfiguring the Visio driver of course. It does not work too. Any help would be cool!

Kind regards Matthias

Answer 1

From Microsoft support via the Microsoft forums:

Further investigation reveals that this is expected behavior for Visio 2007. When Visio opens a connection using the Visio SQL Server Driver it checks the server version and since SQL Server 2008 shipped after Visio 2007 it doesn't recognise SQL Server 2008 as a supported version and closes the connection. You can wait for a future version of Visio to ship which does recognise SQL Server 2008 or use the Visio Generic ODBC driver which can successfully open connections to SQL Server 2008. A third option is to use a copy of SQL Server 2005 for initial reverse engineering. The Visio team is aware of this issue.

Answer 2

I have this problem too. It occurs to me that one might hack the registry

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\
MSSQLServer\CurrentVersion

but as soon as I change this to 9.00.3042.00 SQL Server won't start. Changing it back to 10.0.1600.22 allows it to start.

Does anyone know a way to get SQL Server to report an earlier version?

Answer 3

You could create a User DSN in the ODBC Data Source Administrator utility and then connect to your instance of MSSQL 2008 through Visio 2007 by using the selecting the ODBC Generic Driver instead of the Microsoft SQL Server driver.

You could also try the SQL Server 2008 Data Mining Addins for Office 2007.

Grab them here: http://www.microsoft.com/downloads/details.aspx?FamilyId=896A493A-2502-4795-94AE-E00632BA6DE7&displaylang=en

I hope this helps!

Cheers

Answer 4

To connect Visio 2007 to a SQL Server 2008 database run the Reverse Engineer Wizard (Database/Reverse Engineer. . . ) in Visio 2007 select the ODBC Generic driver from the "Installed Visio drivers" drop-down. Then create a new data source using the SQL Native Client (2005.90.4035, 2005 SP3). You'll get a warning stating that some information retrieved may be incomplete. Click OK and continue. It's not the most intuitive solution (but not difficult), but at least this will allow you to use Visio 2007 to connect to SQL 2008.

Chip Lambert, Slalom Consulting

Answer 5

Chip,
The problem with your solution is that you lost actual physical data types. For example my column in SQL server is NVARCHA(50) converted into ODBC generic LONGVARBINARY. When I changed driver to SQL server it converted to type IMAGE.
It's not a big problem for 2-3 tables database. But the idea is to simplify documentation for big databases.
Looks like your only free solution for now it to create database diagram inside of SQL server.

Answer 6

You're right, Dmitri. Data types sometimes don't display as they really are in SQL Server 2008. My varchar's display as varchar's, not longvarbinary's as you've experienced (at least with nvarchar's) - possibly related to your SQL Native Client version??? However, you can override the data types the generic driver returns in the database properties' column settings and these will "stick" if you refresh your model. That could be a lot of work if you have a large number of columns. An SSMS 2008 database diagram might be your better "free" option if that's the case.

Hope that helps, Chip

Answer 7

Well, that's a good resolve. But I can use view. How do i do?

Answer 8

I ended up using the Generic OLE Db Provider instead of the ODBC Generic driver to connect to SQL Server 2008 - datatypes seemed to come through OK.

Answer 9

Marlon's ODBC User DSN workaround also works for Visio 2003.