Tuesday, May 1, 2012

Report Server Model generation error: Table has a non-primary data source

A lil bit unorthodox solution!!

I faced this error (Report Server Model generation error: Table has a non-primary data source) yesterday while working on SSRS. I was trying to create a Report Model (SMDL) on SQL Server Buisness Development Studio and was getting this error repeatedly in final step of “Report Model Wizard”.

Query which I was using in Data Source View was somewhat like below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1

I googled this error a bit but found no solution and hence I adopted this lil bit unorthodox option.

My solution was to add an artificial column to my resultset and then using it as logical primary key. So I changed my query as below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3 ', NEWID()AS Id
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1

And then I used this new column as logical primary key, as shown in below image. 



This is working like charm for me but I am yet to test this fully so use this solution for you after through testing. Hope this helps.

No comments:

Post a Comment

About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!