For some reason I attract all kinds of misery when working with Master Data Services. Today I was trying to install the add-in for SQL Server 2012 on a machine that has Office 2016 64-bit installed. I clicked on the link in the MDS webpage which send me to the download page of the MDS 2012 SP1 add-in. However, I was greeted with the following error message when running the installer:
These prerequisites are not installed: 64-bit version of Microsoft Excel 2010
I had all the prerequisites installed (.NET framework 4.6.2 and the Visual Studio 2010 Tools for Office Runtime) and the correct bitness. A quick search led me to this KB article:
The article says you can get rid of the error message by using the 2012SP1 installer. Which I was using. Dead end. I also found this post at Katie&Emil: MDS Excel 2013 and 2010 Add-in Installation. Emil had the same issue and succeeded in installing the add-in by running the installer as admin through the command line. However, I still got the same error. That add-in must really hate Office 2016.
So I installed the SQL Server 2016 add-in which did install successfully, but apparently isn’t able to connect to SQL Server 2012. No backwards compatibility. I turned to Twitter and someone suggested me to install the 2014 add-in. Luckily, this one does install and fortunately has backwards compatibility with SQL Server 2012.
To wrap everything up:
- SQL Server 2012 with Office 2010 – MDS add-in for SQL Server 2012
- SQL Server 2012 with Office 2013 – MDS add-in for SQL Server 2012 SP1
- SQL Server 2012 with Office 2016 – MDS add-in for SQL Server 2014
- SQL Server 2014 – MDS add-in for SQL Server 2014
- SQL Server 2016 – MDS add-in for SQL Server 2016 (no backwards compatibility)
- SQL Server 2017 – MDS add-in for SQL Server 2017
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Thank you for this information, it’s been a great help. I have SQL Server 2012 and Excel 2016, tried everything I could think of and couldn’t get it to work. I too, feel your misery with MDS.
Ohhhooo,Thanq very much.
It helped me a lot i got installed of 2014 add in worked in excel 2016
I appreciate the work you did, but if you got the error message you show at the start, it’s because you’re attempting to install the 64-bit MDS 2012 Excel add-in when you have 32-bit Office installed.
I can confirm that I am using 32-bit Excel 2016, and the 32-bit MDS 2012 Service Pack 4 add-in. You can find this on the 2012 SP4 Feature Pack page – I have no idea why they bundled it this way vs. a separate page as was done before.
Microsoft® SQL Server® 2012 SP4 Feature Pack
https://www.microsoft.com/en-us/download/details.aspx?id=56041
MDS is a pickier product than most, I do not recommend crossing versions like you suggest above.
Hi Chris,
it’s been a while since I wrote this blog post, but I’m pretty sure I got the bitness correct (64-bit installer for 64-bit Excel). I always have 64-bit Office installed.
I believe I got the recommendation of using the MDS 2014 add-in from someone on the MDS team itself, so I guess it’s pretty safe 🙂
Hey Koen,
Thanks for the reply. I re-read after I “posted” my comment, and missed that you had checked 64-bit. I find that odd though, and think it’s more likely that the add-in at the time just wasn’t compatible.
There’s an SP4 version (as I listed in the first comment) that’s compatible with MDS 2012 and Office 2016 for example. That was even out a month before you wrote the post, so it seems to me someone on the MDS team didn’t direct you to the latest installer. 🙂
Hi Chris,
next time I have a client with SQL Server 2012 and Office 2016 I’ll definitely check it out!
MDS is one of Microsoft’s most poorly supported products. I ran into all the same dead ends that you described.
Thank you for your post. It helped me so much.
Hi Koen,
Our company is upgrading to O365, I noticed that we are no longer able to use and install the MDS add in. Before the upgrade we used Office 2016, with MDS add in version 2014 for an SQL server 2012 installation.
Are we ‘forced’ to upgrade MDS to SQL Server 2017 (apparently a web-based interface, HTML5). Or are we going to have to install an ‘old’ desktop version of 2016 on the computers of our end users? Any other ideas?
Thanks,
Axel
Hi Axel,
I haven’t tried installing the add-in myself in a O365 version of Excel. I’ll have to try it out.
The new HTML5 web interface doesn’t affect the add-in however. Upgrading MDS does give you a newer version of the add-in, which might work (but I’m not sure).
Raising a support ticket with Microsoft might be a good idea.
Koen
ps: say hi to the BI team at Isabel for me
After some searching it turns out that it was due to a 32/64 bit mismatch. Office 365 is installed as 64 bit, in contrast to Office 2016 which was 32 bits.
Axel
ps: the bi team says hello back 🙂
Hi Koen. This is regarding – Transaction Log History in MDS 2016
I have upgraded recently from MDS 2012 to MDS 2016. Once the database are upgraded all the entities are set to transaction log – Attribute type . The changes made to the entities were being logged into tbl_**_TR table as expected but the ‘view history’ feature on the UI was disabled.
I have been actively using the feature ‘View History’ thus we have updated the entities to have transaction log type – Member , which enabled the feature as expected but post this, the changes on entity data are not being logged onto tbl_**_TR table.
I have built reports on tbl_**_TR table that looks for all the changes for the entities made for particular duration for specific attributes.
I have already used Leaf member history/ SCD Type subscription views for few entities, but I need to have transactions log for all entities on the application ( 400+) at attribute level, i.e. I want to view the changes done on attribute on entity within specific period.
Please advise to get the transactions logged properly.
Another observation on the data held in history/SCD2 type subscription views is that whenever a new version of the model is created, the enterdatetime for all the records changes to the new version creation date time and lastchangedatetime changes to ‘9999-12-31 23:59:59.998’. I refer to these datetime columns to check updates/inserts done to the entity and now it is not possible to fetch changes for any datetime range. I am only able to fetch data for the current version that is active.
Any workaround / fix for this ?
Hi,
I’ve not worked with MDS for a few years now, so I don’t really know the answer.
You might have better luck in the forums.
Regards,
Koen
Any forums which you could recommend. MSDN and stackoverflow have no responses as well
Unfortunately, only those two. Maybe MS Support is an option?