Rever Engineer relationships from SQL DB

[Hafeez Esmail]

I've recently installed VISIO 2007 Professional.
I would like an ER model of my SQL Server 2000 database, based on a handful
of selected tables.
Using the Reverse Engineer tool, I can import the tables (along with the
correct PK, FK, index information), but there are no relationship lines.
I checked the help, and it provides instructions on how to create the lines
manually. I'd rather not do that for 250+ tables.
Any help would be greatly appreciated
Thanks in Advance

 

[Barb Way]

The Relationship information should be retrieved into the model at the same
time as the tables, assuming that you included all of the tables in a
relationship within one Reverse Engineer action.

For instance, if I have tables A, B, C and D, and there is a relationship
between B and D, I should include B and D together (along with other
desired tables) in the list of items that I reverse engineer together.

If instead, I reverse engineer once, and retrieve only A and B, then later
I retrieve C and D, then I have no relationship, because B and D are not
bring brought into the model together.

Another thing that you should know is that these must be true relationships
in the SQL database. Triggers and other forms of code / stored procedures
which perform database maintenance to enforce integrity rules are NOT
relationships, and will not be presented as such.

I hope that helps!


Barb Way
Product Support - Visio
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hafeez Esmail]

Thanks for replying Barb.

I always select all of the tables as part of the Reverse Engineering
process, so it's not the first scenario as you've described.
As for the second scenario you've mentioned, how could I tell if the
relationships are true relationships in the SQL Server DB?

 

[Barb Way]

In SQL, each table which is part of a Relationship will have entries under
'Keys', which will describe each Foreign Key or Primary Key.

If the primary key and foreign key items are displaying properly in the
tables then they should be in the model. During your Reverse Engineer
action, there is a log created on the Output tab, which includes a final
summary. In that summary will be the total PK and FK items reverse
engineered:
Tables reverse engineered : 71; Time taken (in secs) : 3.82
...
Primary Keys reverse engineered : 71; Time taken (in secs) : 0.44
Foreign Keys reverse engineered : 92; Time taken (in secs) : 5.68
...
This will confirm that you are getting all of the data into the Model.

You mentioned adding Relationship lines manually, but I want to ensure that
we're talking about the same function. Try a test with a couple of the
tables in your drawing : right-click on one or two tables that have
relationships to other tables (not necessarily to each other). From the
context menu, choose the option "Show Related Tables". This should expose
the Relationships in the model as lines in the drawing between the
appropriate tables.

Finally - have you checked the Database menu under Options > Document.
There are three tabs - click on the third one (Relationship). Make sure
that there is a check-mark under Show for 'Relationships'.



Barb Way
Product Support - Visio
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hafeez Esmail]

Hi Barb,

I've verified that there's a check mark under 'Relationships' in the Options
--> Document Screen.
After running the reverse engineering function, the output says 0 Primary
Keys and 0 Foreign keys were reversed engineered. Also, I added two tables
to the drawing and nothing happened when I selected "Show Related Tables"
from the right-click menu. However, when I double clicked on one of the
tables and looked that the Database Properties, I can see that the Primary
Key was correctly identified (i.e. the actual primary key field had a check
mark in both the required box and the PK box, and no other field has a check
mark in their PK box).

So the output tells me it couldn't reverse engineer PKs....but the DB
properties seem to indicate that it has correctly identified at least two PKs.

Is there any hope for me?
Thanks,

 

[Barb Way]

Hafeez -
If the model doesn't see the relationships during the Reverse Engineering
phase, that is the core problem to resolve. You found some tables with
PK's in them. Do you see any which show "FK" next to the column name? A
relationship is a definition between two tables - one having a Primary key
(comprised of one or more columns) and the other having the Foreign Key
(comprise of one or more columns).

Barb Way
Product Support - Visio
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.