SCCM 2012 SQL Views
A while ago, I was looking at MOF edits and importing them into CM12. After some time I noticed that the SQL view name changed between CM07 and CM12.
Perfect, I thought, this is going to be a pain because it will mean that I have to change thousands of CM07 SQL queries to work with CM12!
Without thinking about it, I started testing one of the existing SQL queries from Monitor Information Reporting (MIR).
I think that things are great as I am starting to get results! Maybe I made a mistake when I first looked at this! Then I go looking for the SQL view and I don’t see v_GS_Monitor_Information0 SQL view.
Now this is where I’m banging my head!
I run the query again… and I get results.. but where are the results coming from??? I must be overlooking something obvious, but what???
Note that v_GS_Monitor_Information0 SQL view is not present in the left-hand pane. Also notice that I’m getting results from the query!
Suddenly it hits me like a ton of bricks! I know exactly what the Microsoft product team did, but now I have to prove it!
I run the following query:
SELECT name, xType FROM sysobjects WHERE name like ‘v_GS_Monitor%’
This is the result that I get:
This tells me that they are using an alias for the SQL view or in SQL terms a “Synonym!” How cool is that!!!?!?!?!?! I did some quick checking and the best information on synonyms that I could find were these blogs:
Benefits and limitations of using synonyms in SQL Server 2005:
What does this mean for SQL queries and best practices?
Well that is for another blog post after I talk to a few people at Microsoft to confirm my suspicions. My *GUESS* is that you should use the CM12 SQL view names wherever possible, and use the new synonyms if you need to be compatible with CM07.