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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:

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
Postal Address
Physical Address

and both addresses have a column named 'Country'

you would need to alias that column like this:

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

iscontactAuthor 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 DBACommented:

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

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

iscontactAuthor Commented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:

Cool that you've solved it.

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:

For COUNTRY effected tables are:

iscontactAuthor Commented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.