SSAS DSV COM error from SSDT SSAS design Data Source View
Recently I worked on a case regarding a COM error when opening the data source view (DSV) of an Analysis Services multidimensional project.
Error HRESULT E_FAIL has been returned from a call to a COM component. (msddsp)
Below is the call stack of the exception
at MSDDS.IDdsDiagram.CreateConnector(String ProgID, Boolean Visible, IDdsDiagramObject Src, IDdsDiagramObject Dest)
at Microsoft.DataWarehouse.Controls.Interop.AxMSDDS.CreateConnector(String progID, Boolean visible, IDdsDiagramObject src, IDdsDiagramObject dest)
at Microsoft.DataWarehouse.Design.ComponentDiagram.OnCreateConnector(IDdsDiagramShape startShape, IDdsDiagramShape endShape, Object logicalConnector, UInt32 color, String connectorProgID)
at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateConnector(IDdsDiagramShape startShape, IDdsDiagramShape endShape, Object logicalConnector)
at Microsoft.AnalysisServices.Design.DataSourceDiagram.CreateRelationshipConnector(DataRelation dr)
at Microsoft.AnalysisServices.Design.DataSourceDiagram.ShowOtherConnectorsOnRelatedTables(DataTable dataTable)
at Microsoft.AnalysisServices.Design.DataSourceDiagram.ShowTable(DataTable dataTable, Int32 prefX, Int32 prefY, Boolean delayArrangeTables)
at Microsoft.AnalysisServices.Design.DataSourceDiagram.AfterDeserialize()
at Microsoft.DataWarehouse.Design.ComponentDiagram.SetLayoutInfo(String xml)
at Microsoft.DataWarehouse.Design.ComponentDiagram.OnDiagramControlCreated(DdsDiagram dds)
at Microsoft.AnalysisServices.Design.DataSourceDiagram.OnDiagramControlCreated(DdsDiagram dds)
at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateDdsView(Control parentControl)
at Microsoft.AnalysisServices.Design.DataSourceDesignerView..ctor(DataSourceDesigner designer, DataSourceDiagram diagram, IComponent diagramOwnerComponent)
at Microsoft.AnalysisServices.Design.DataSourceDesignerView..ctor(DataSourceDesigner designer)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.CreateDataSourceDesignerView(VsStyleToolBar pageViewToolBar)
at Microsoft.DataWarehouse.Design.EditorWindow.EnsureViewIsLoaded(EditorView view)
Let me explained what happens. If you have a machine installed SQL Server 2012 version of the SQL Server Data Tools, you should have this DLL file C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Bin\DDSShapes.dll
When you design the table relationship in a DSV, Visual Studio loads this DLL to write binary object data into the DSV metadata. The binary data for the table relationship shape object begins with "0008" in the <ddscontrol> element. Please see the screen shot below
If you have another machine that installs SQL Server 2014 version of the SQL Server Data Tools, it loads this DLL file C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin\DDSShapes.dll. If you use SQL Server 2014 version of the SQL Server Data Tools to modify the DSV, the binary data starts with "000a".
If you try to use SQL Server 2012 version of the SQL Server Data Tool to open the DSV modified by the SQL Server 2014 version of the SQL Server Data Tool, you gets this COM exception. The reason is, this leading 4 characters of binary data is the version of ATL used to compile the DDSShapes.dll. When this DLL reads the binary data, it check if the version of the ATL that was used to save the data, making sure it is below the version recognized by the DDSShapes.dll. "000a" is a higher value than "0008", so the SQL Server 2012 version of the SQL Server Data Tool throws an exception.
The solution is to install the SQL Server 2014 version of the SQL Server Data Tool from https://msdn.microsoft.com/en-us/data/hh297027 . You should get the new version of the DDSShapes.dll in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin.
After explicitly registering the DDSShapes.dll from C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin (using regsvr32), the problem should be resolved. Please see the command below
This means, if there are multiple SSAS developers working on the same SSAS database or project, and one of the developers starts to use SQL Server 2014 version of the SQL Server Data Tools, all the developers should install the SQL Server 2014 version of the SQL Server Data Tools.
Comments
Anonymous
June 18, 2015
Great article! - you saved my life :)Anonymous
June 18, 2015
Thanks for this article. Very useful :)Anonymous
July 07, 2015
The comment has been removedAnonymous
July 17, 2015
Thank you so much....This helped me. It Solved my Problem. I was not able to see the design of Data Source View. This command [The command Above] solved my issue.Anonymous
July 22, 2015
Very good ! Clearly explained. In my case the 2014 DLL (in /120/..) appeared to be correctly installed but not used by Visual Studio until I registered it. Why this didn't happen when uprading SQL Server to 2014, I know not. Maybe something to do with the sequence of installation/updating of SQL Server and Visual Studio ?Anonymous
August 23, 2015
This error came about for me when I installed VS2012 after VS2013. The Solution worked perfectly, many thanks.Anonymous
September 01, 2015
I don't have the luxury of installing software on the system I am trying to run this on. The code came from my local computer, where VS2012/2013 is installed, but got moved over to a different workspace. Can I edit the "000a" back to "0008" in all my DSVs?Anonymous
September 02, 2015
Replying to Kevin N Yes, you can edit an ALTER XMLA command to change the DSV elements back to "0008"Anonymous
September 10, 2015
Wow. Excellent, thanks!Anonymous
November 10, 2015
I've spent 3 days trying to resolve "an error prevented the view from loading"... thank you so much for this post!Anonymous
April 14, 2016
Great, thanks. Sorted out the problem for me.Anonymous
August 27, 2016
Incredible insight. Saved my sanity.Anonymous
September 30, 2016
Hi,I experienced a related issue with the difference that it was the Data Source View part of a dimension .dim which was empty and that I had to replace 000c with 0008.This bug is very annoying. Thanks a lot for this article.a+,=)-=Clement=-Configuration:SQL Server Analysis Services v11.0.5058.0SQL Server Data Tools 10.3.31009.2Visual Studio 2010 Shell (Integrated) - ENU Service Pack 1 (KB983509)Anonymous
November 22, 2016
Done !thank u for this helpful article :)Anonymous
April 27, 2017
Changing from 000a to 0008 saved me. Thanks aallllllllott :) :)Anonymous
May 09, 2017
Thank you so much!! Fixed the problem.Anonymous
June 01, 2017
Life Saver !!!!Anonymous
November 05, 2018
Registering DLL helped. Thank you!Anonymous
November 14, 2018
fixed my problem: an error prevented the view from loading in SSAS when trying to edit dsv