Configuration Manager 2012: Inventory Customizations when you use Distributed Views

I suspect few ConfigMgr 2012 environments will encounter this potential issue.  You have to have 3 very unique circumstances.  a) you are a big enough environment that you have a CAS and Primaries to begin with.  b) You are a big enough environment to leverage Distributed Views.  c) You've previously customized inventory, and then you've decided to ADD to that customization instead of creating a new one (very few environments do that, even if they have a CAS and they use distributed views).  So maybe I'm the only one that would ever encounter this issue.  But just in case I'm not... putting this out there in a blog for others to find in case they are just as strange as I am. 

The Issue: you see something like this in your dataldr.log:

*** exec dbo.spRenewChangedInvViews

*** [42S22][207][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Supported00'. : v_GS_MoreInfo0

You have a CAS and a Primary, and you have enabled Distributed Views for Hardware Inventory (you can check by going to Administration, Hierarchy Configuration, Database Replication, for each Parent Site to Child Site replication link, right-click, and go to "Link Properties" If you have a checkbox next to "Enable the following types of site data for distributed views" for Hardware Inventory, then this applies.

How to resolve:

One way, I guess… would be to turn off distributed views for hardware inventory, wait a day or so, then turn it back on. (but who wants to do that).

These instructions aren't exactly um… supported. But it seemed to work for me.

The reason for the error is that the local table which was changed as part of the process of adding an attribute to a pre-existing custom import isn't what is actually being referenced by the new view. There's a View, which uses Union All; to grab info from the child site and the CAS site database, so that it looks like just 1 view (when you do reports)

So… the fix is to update that Distributed View. For Each of the 4 views that are there for that custom inventory.

For whatever reason, you can't see the Distributed Views from a remote SQL Management Studio connection.  So you have to RDP into your Server which houses the Database for your CAS.  Launch SQL Management Studio from there, then go to your CAS site, database, views, and go find the dbo.<TableName0> ; In my case it was dbo.MoreInfo_data. Right-click Design on that.

If you're a deep down sql geek, you'll see that it's just 2 select statements with a union all in there--and guess what's missing? Yep, that new attribute you just added. So in both select statements, add in the missing attribute (in my example,   ,Supported00, exactly what the error message is whining about), and hit save.

Go back and watch dataldr.log. I'll bet it continues past that error (for v_gs_moreInfo0) and now is whining about v_hs_moreinfo0. I'm Right, aren't I? Ok, now you have to right-click design on the _HIST view; same thing, 2 select statements with a Union All; and you add in the missing attribute and hit save.

Continue doing that until dataldr.log stops whining.