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.

Janae

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.

Blackshear

I really don't like Microsoft's answer on this one, especially since we're already half way towards 2010... but I see the answer you listed above is the same one on Microsoft's tracking page for this bug... which was closed as being "By Design". Here's the URL for those who want to add their own 2 cents on Microsoft's site: connect.microsoft.com/SQLServer/feedback/… – Flap

Don't despair you can use Chip Lambert's method below, it actually works! – Wiltz

@Flap The thing is, I have waited. And now they remove Reverse Engineering from Visio 2013. What a joke. tinyurl.com/ceqk8w7 – Cultch

Answer 2

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

Positron

Valid for Visio 2010 + SQL Server 2012 issues as well!!! On a separate note why is Visio so LAME??? – Puissant

Answer 3

An old thread but still a current problem ... I found that although using the ODBC Generic Driver worked, the reverse engineering tool then misses out Triggers, Check Clauses, Views and Stored Procedures. By specifying the Access Visio Driver instead, at least we recover the Check Clauses and Views.

In general, though, I have to say I think this shows an appalling lack of regard for their customers on behalf of the relevant teams at Microsoft. I had a very similar experience last year when upgrading to Visual Studio 2010 only to discover that my SSIS projects no longer opened ... as can be seen from this thread, MS could not care less.

Apraxia

Woah! Super Tip! Upvoted Chip's response before I came to this one! Indeed Access Driver grabs the Views for SQL SERVER 2012 as well. Kudos!! – Puissant

Answer 4

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

Selfknowledge

Answer 5

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.

Aryl

Answer 6

I also had this problem as above what i found worked

1. was using the Reverse engineer wizard
2. using the Generic OLE Db provider in the first step
3. then setting the connection provider in the next step to the highest SQL native driver shown ( I am using SQL2016 with SQL native 11.0 on a windows 10 surface pro 4 for reference )

4. then entering the correct destination and credentials in the connection tab ( testing the connection if you aren`t sure)

   and that seemed to work for me,( I then had the ability to bring through tables indexes views primary and foreign keys and stored procedures).

I also found that visio kept locking up on me ... apparently this is common ( and there I was feeling special) after finally getting sick of it i looked at these links

https://dhondiyals.wordpress.com/2011/07/29/microsoft-visio-2010-crashes-very-frequently-resolved/

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_visio-mso_windows8/visio-2010-frozen-on-surface-pro/df1df27a-6585-4b0c-8442-a4363c541e08

I found my problem to be in the later, ( the touchscreen and handwriting running application) .So ended it, and now I have the experience I was expecting

Antlia