Toad World® Forums

Reverse Engineering SQL Server 2008 misses columns

Hi - just downloaded TDM (4.3.3.6) and to try it out I reverse engineered the Production schema on AdventureWorks DB from SQL Server 2008r2
It appeared to worked fine until it created the indexes at which point there were a number of errors of the form "attribute x (in entity y) has not been found'.
It turns out that every attribute/column called 'Name' (or which has the string 'Name' in the name has not been imported with the tables e.g ProductModel.Name, Culture.Name, ScrapReason.Name, ProductReview.ReviewerName, ... are all missing from the table definitions

Anyone have any ideas what the issue is?

Thanks

Hi Nick,

I tried a reverse engineer using AdventureWorks on SQL Server 2008 and it worked fine using TDM 4.3.3.6. Is it possible that a filter may have been applied during the object selection process that excluded those tables?

Thanks!

Kevin

Hi,

thanks for responding. Just to be clear, it is skipping columns on tables, not tables themselves - and it only seems to be columns that have the string ‘name’ in their name.

I’m using the reverse engineering wizard and connecting to the DB - I’m not filtering anything, that I know of.

I tried it with the a single table (production.productmodel) and got the same issue.

I then exported the DDL for this table to a file and ran the RE Wizard against the file and it created all the columns correctly - so it seems to be an issue with how TDM is extracting the metadata from the source DB rather than with how it is creating the objects in TDM.

Any thoughts?

Thanks,

Nick

I think I know what the underlying issue is but not how to resolve it. The columns that are being missed have user-defined data types and I’m guessing that the driver I use to connect to the DB cannot handle it. For example, the Production.Product.Name column is defined as:

Name (Name(nvarchar(50)))

whereas the Production.Product.ProductNumber column is defined as:

ProductNumber (nvarchar(25))

I guess the RE is picking up the user type id rather than the system type id (or both) and failing to handle them so is just dropping the columns.

BTW I also tried this import using Oracle Data Modeler and while that did import the columns it did so with a datatype of ‘Unknown’ so this seems to be a general failing for modelling tools import processes

I took a look at mine and it’s configured the same way. When you are looking at the database connection does Data Provider say Native Connection?

It does say ‘Native Connection’ but on the next page of the wizard under ‘OLE DB Provider’ it was using Auto. When I changed this to Native it errors as I don’t
have the SQL Server driver installed on my PC, it only works if I use Auto or SQL here. I guess this is the issue: the basic OLE DB Provider that comes with Windows can’t handle this type of detail.

**Nick WHITE
**Senior Consultant

Sopra ** * group*****.**

8th Floor

55 Old Broad Street

London. EC2M 1RX

Phone :

Mob : 07917648966

Nick.White@uk.sopragroup.com
www.sopragroup.co.uk

P Before printing, think about the environment

From: Kevin Stern [mailto:bounce-KevinStern@toadworld.com]

Sent: 07 June 2013 15:47

To: tdm@toadworld.com

Subject: RE: [Toad Data Modeler - Discussion Forum] Reverse Engineering SQL Server 2008 misses columns

I took a look at mine and it’s configured the same way. When you are looking at the database connection does Data Provider say Native Connection?

Hi, we will do additional tests. Please expect another message from me on Monday. Thank you,

Hi,

it seems to be a problem of the driver that was used, unfortunately we are not able to reproduce it.

Please try to use ADO connection instead of Native Connection and use "Microsoft OLE DB Provider for SQL Server".

Regards,

Vaclav