Data Source View does not support PK with tinyint type - by Marco Russo

Status : 

 


36
0
Sign in
to vote
ID 278682 Comments
Status Active Workarounds
Type Bug Repros 17
Opened 5/27/2007 6:51:49 AM
Access Restriction Public

Description

Designing a Data Source View, any column that is part of a primary key of type tinyint (SQL Server) is transformed into an int column. Then, when you try to define a table relatioship where a foreign key exists in another table, you get the error "The ID_<name> source column and the ID_<name> destination column have different types".
The problem is that the conversion to int is made on the PK column, but not on the FK column.
If I change the data type in the DSV (from int to unsignedByte), I get an override with the previous type at the first refresh.
The only workaround seems to be a view that exposes a SMALLINT (short) instead of a TINYINT (unsignedByte) - unfortunately, I need to do this on both sides (PK and FK tables).
Very annoying, since I have a data warehouse with many dimensions with very few items that used TINYINT just to optimize the space for the fact table.
Sign in to post a comment.
Posted by ErnestoMawan on 3/10/2014 at 5:22 AM
This bug still exists with SQL Server 2012, are you taking your customers serious ? It was reported in 2007 and 7 years later its not fixed. Seeing that Windows 8 is considered a complete failure in the enterprise world, I was at least hoping that you keep the server products on a high level, but hitting such silly issue starts to raise worries here as well.
Posted by CubeZene on 2/7/2014 at 7:11 AM
Adding a named query is time consuming work at $175 per hour for Microsoft Consulting rates. My manager is telling me that since adding named queries is not in the project plan, we allocate enough funds to do this. Development with Analysis Services 2012 has stopped until the VP allocates more funds to complete the transition to named queries with full regression testing. Please adivse.
Posted by NilsRottgardt on 4/15/2013 at 10:10 AM
Yes, it is really bad to build up and optimized relational star scheme and to have problems in consume it in SSAS.
Posted by joao.lopes on 4/9/2013 at 7:40 AM
+1 ... this bug was first reported on another connect item on 5/14/2006 (in a couple of weeks will be 7 years old)
http://connect.microsoft.com/SQLServer/feedback/details/126798/data-source-view-incorrectly-types-a-tinyint-identity-column-as-int32

Just fix it please :)
Posted by Sam Holloway on 7/20/2012 at 5:08 AM
We experience this issue. It requires us to have workaround code to build views/named queries rather than a straightforward use of the table. This is an unnecessary workaround and we would like not have to maintain this.
Posted by Jason V Campbell on 3/16/2012 at 2:35 AM
I'm getting the same issue on SQL Server 2008.
Is this issue ever going to be resolved?

The workaround seems to be the end of the discussion on this issue.
Surely I should be able to build a cube on a schema that has tinyint datatype without cluttering things up by adding views and named queries?

Posted by BetterToday on 4/30/2008 at 1:30 AM
I agree, this bug should be fixed. It renders DataSourceViews useless for many common database design scenarios. I'm glad that it's on the agenda now. Yet it should have been addressed much earlier already.
Posted by BetterToday on 4/30/2008 at 1:30 AM
I agree, this bug should be fixed. It renders DataSourceViews useless for many common database design scenarios. I'm glad that it's on the agenda now. Yet it should have been addressed much earlier already.
Posted by Microsoft on 4/29/2008 at 3:04 PM
Marco,

It is much too late in the current release to fix this but I will keep it open for the next release.

Thanks,
Rob
Posted by Marco Russo on 4/29/2008 at 2:16 AM
I reopened the item - see this discussion
http://groups.google.com/group/microsoft.public.sqlserver.integrationsvcs/browse_thread/thread/172de95b0d68882b
Posted by Marco Russo on 10/5/2007 at 4:28 PM
Ehm... in the current version of Analysis Services (9.00.3186.00), DSV are created with the right type for all fields that are TINYINT.
It seems that someone already fixed the bug in the meantime... I'm not able to say when the bug has been fixed, but it's happened in one of the last hotfixes... Can you check this?
Posted by Microsoft on 10/5/2007 at 2:46 PM
We will not be able to fix this issue for the current release but will certainly look at it again for the next release. There is a workaround available, which is to create a named query (as I'm sure you know).
Posted by Microsoft on 5/30/2007 at 10:32 AM
Thanks for reporting this issue. We'll look into it for our next major release.

- Matt Carroll