Reverse Engineering

[Derek Johnson]

I am using Visio 2000 Enterprise Edition. I have reversed-
engineered a SQL Server 2000 database into a database
model diagram. I have several questions:

1) When I tried to use the Database Update Wizard, Visio
returned the following errors:

C:\Documents and Settings\Administrator\Desktop\drawing5 :
error P1030: 'nchar' : The user defined type name is a
reserved word in the target DBMS.
C:\Documents and Settings\Administrator\Desktop\drawing5 :
error P1030: 'bit' : The user defined type name is a
reserved word in the target DBMS.
C:\Documents and Settings\Administrator\Desktop\drawing5 :
error P1030: 'timestamp' : The user defined type name is a
reserved word in the target DBMS.

and so on...

It looks like Visio created user defined types in the
model and is trying to recreate them in the database but
they are already defined. How do I get around this. Did
I miss something when reverse-engineered the database?

2) During the reverse-engineering process, I was able to
add the stored procedures to the model. How do I include
them in reports?

3) I have columns in tables that include varchar types.
Visio displays them as varchar but how do I display the
length of the varchar? How do I do this in a report?

4) Are there plans to include the description fields for
the columns and tables in SQL Server 2000 in the reverse-
engineering process?

Thanks,
Derek

 

[Ross Grayum[MSFT]]

1. The error message (P1030) indicates that the named user defined type
collides with a reserved word. But why does the model include these? I
can't be sure. Perhaps these are actually defined as user defined types in
your DB Schema, which would be strange, in which case they should probably
be renamed. Or perhaps the software encountered a problem, and incorrectly
read these as user defined types (although I searched our bug database and
could not find any known bug reports to this effect.) Do you get this
error when reverse engineering a sample database (like Pubs?) You might
try bringing up the User Defined Types dialog (Database | User Defined
Types...) and removing these from your model. However if you get a message
indicating that they are in use, I guess you would need to determine which
columns are defined to be of these types and redefine them to use the
predefined types of the same name. I don't believe that you "missed"
anything during the reverse-engineering process, the problem must have
existed prior to reading the db schema.

2. You can view/edit your stored procedures via the code window (Database
| View | Code...), and you can get a listing by generating DDL to a file
(Database | Generate... check Generate a text file of the DDL Script, and
uncheck Generate new database). The stored procedures do not show up in
any of the pre-canned Reports (Database | Report...)

3. To display the datatypes of the columns in your diagram, go to the
Table tab of the Database Document Options dialog (Database | Options |
Diagram...) and select the "Show physical" data types radio button.
To print a report showing the column info, bring up the New Report Wizard
(Database | Report...), choose Table Report and press Finish. Then in the
Report dialog, choose "Physical" names radio button, then press the
"Default To: General Report" button and choose "Detailed Column Report".

4. I'm not able to comment on future plans.

Ross Grayum[MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm