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!
CM12 CM07
Without thinking about it, I started testing one of the existing SQL queries from Monitor Information Reporting (MIR).
Select
MIR.ManufactureID0,
Count(*)
from
dbo.v_GS_Monitor_Information0 MIR
Group by
MIR.ManufactureID0
order by
MIR.ManufactureID0
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:
Create Synonym:
http://msdn.microsoft.com/en-us/library/ms177544.aspx
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.
[…] September I posted a blog post about SCCM 2012 SQL Views. There are a lot of cool things that I like about CM12 and using synonyms within SQL is one of […]