x

Creating Data Source Views with a Database Lacking Primary Keys

I'm attempting to create data source views on a terribly designed database which lacks primary and foreign keys. I want to be able to use Data Source Views to work with SSRS Report Models. I cannot create them with the wizard because "Add Related Tables" doesn't work and BIDS cannot determine the link. I've read that these relationships are based upon PK and FKs.

When I re-open the DSV and attempt to right click the correct field in a table to assign a logical primary key, the option is grayed out (disabled).

Here's the problem, there is a key symbol next to an identity column field which is a unique clustered index, but is not a Primary Key.

When I deleted the clustered index on a test database it allowed me to set a logical primary key. However, I cannot modify the production database that way. Almost every single table has a field called Identity_Column which is a unique clustered index.

So, it would appear that the DSVs use clustered indexes rather than primary keys as those are not always the same thing. Can anyone else confirm this?

When I create a DSV from a named query, there are other problems. If I use a query that includes 30 fields, but the user only pulls back 5 of them with Report Builder or something similar, every field seems to get pulled back anyway. Profiler shows that it runs the entire DSV (with every field) and then selects the few fields the user requested from that data set.

This is really inefficent. Any suggestions as to how to handle this?
more ▼

asked Sep 08, 2010 at 11:33 AM in Default

DavidStein gravatar image

DavidStein
62 5 5 7

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x547

asked: Sep 08, 2010 at 11:33 AM

Seen: 1948 times

Last Updated: Sep 08, 2010 at 11:33 AM