We help IT Professionals succeed at work.

Problems creating Data Model in SQL Server Reporting Services

I'm attempting to generate a model for our manufacturing database in Report Server.  I can create the Data Source, but when I attempt to generate the model, I get several errors in the following format ...

More than one item in the Entity 'CUST ADDRESS' has the name 'COUNTRY'. Item names must be unique among immediate siblings. (DuplicateItemName) Get Online Help
More than one Field in the Entity 'CUST ADDRESS' has the name 'COUNTRY'. Field names must be unique within an Entity. (DuplicateFieldName) Get Online Help

 There are several pairs of similar error messages (DuplicateItemName and DuplicateFieldName) listed.  

The tables (i.e. CUST_ADDRESS) do contain the column name (i.e. COUNTRY) but are otherwise unremarkable.
Clicking the "Get Online Help" link leads to a page with an apology and no useful information.


The database is at Compatibility Level 80, but I am able to generate models for other databases at that level.
I've run DBCC CHECKDB against the database and it returns without errors.  

This is on SQL Server 2005, Standard Edition x64 with SP2.

I'm at a loss for what to do next, there doesn't seem to be any documentation for this error available.

Thanks for any insight into this matter.

Comment
Watch Question

David ToddSenior Database Administrator
BRONZE EXPERT

Commented:
Hi,

this sort of thing generally happens with joined tables, and those tables have different columns with the same name.

If for instance you have something like this
Customer
Postal Address
Physical Address

and both addresses have a column named 'Country'

you would need to alias that column like this:

select
  ...
  , PostalAddress.Country as PostalCountry
  , PhysicalAddress.Country as PhysicalCountry
  ...
from ...

HTH
  David

Author

Commented:
I don't think the problem has to do with joined tables.

I've done some additional investigating since the original post, and discovered that in each table that produced an error, there was a column named "COUNTRY" and another named "COUNTRY_ID".  I restored a test version of the database and renamed the "COUNTRY_ID" columns to "COUNTRYXX_ID".  I was then able to create the data model without any errors.  

The database is part of a commercial package, so I cannot make these changes to the production system.  

My question now becomes, how to get the Model Generation function in Report Server to not rename the columns.

David ToddSenior Database Administrator
BRONZE EXPERT

Commented:
Hi,

Are you able to add a view to the database, where the view effectively renames the column for you?

Cheers
  David
I think I've found my own solution.  I was trying to use the Report Manager (Web Interface) to create my data models.  I tried using the Business Intelligence Development Studio.  
The web interface has no facilities to create data views.   I created a data view in BIDS and then generated my model from there.  That seems to resolve the issue, at least enough for me to get what I needed.

David ToddSenior Database Administrator
BRONZE EXPERT

Commented:
Hi,

Cool that you've solved it.

Cheers
  David

Commented:
This problem is specific to Visual Manufacturing databases in SQL Server. It appears that something is duplicating names when dealing with a column, and foriegn key in the following tables. To fix, open the report model (*.smdl) in Visual Studio, or BIDS. Select the each table below and notice that in the attributes view that you will find two attributes named COUNTRY (or RATE), change the name of the attribute whose type is 'Role' to something else (COUNTRY_FK). Save, and move to the next table. Once complete things should work fine.

For RATE effected tables are:
RATE_DETAIL

For COUNTRY effected tables are:
CUST_ADDRESS
CUSTOMER
CUSTOMER_BANK
SHIPTO_ADDRESS
V_ACCOUNT
V_ADDRESS
VENDOR
VENDOR_BANK
WAREHOUSE

vmmodel.png

Author

Commented:
almander-

What experience do you have with VMFG?  I would like to network with you a bit if possible.  I am not the one who posted this question, but I am the IS manager here at Frasca.

Scott Quinlan
squinlan AT frasca DOT com

Explore More ContentExplore courses, solutions, and other research materials related to this topic.