DOCUMENT:Q259708 27-JAN-2002 [sms] TITLE :SMS: New SQL Views Are Available in SMS 2.0 PRODUCT :Microsoft Systems Management Server PROD/VER::2.0 SP2 OPER/SYS: KEYWORDS:kbDatabase kbsms200 kbsms200sp2fix ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Systems Management Server version 2.0 SP2 ------------------------------------------------------------------------------- SUMMARY ======= This article describes new SQL views that have been added to Systems Management Server (SMS) 2.0 Service Pack 2 (SP2). MORE INFORMATION ================ New SQL views have been added to the SMS 2.0 SP2 database to enable high performance data access for reporting programs. The purpose of these views is to mirror the Windows Management Instrumentation (WMI) class schema of the SMS database as documented in the SMS 2.0 Resource Kit and the SMS 2.0 Software Development Kit (SDK). Please note that WMI is the recommended data-access method for accessing the SMS 2.0 database. For more information about SQL views and how to use them, see the SQL Books Online or the SQL Resource Kit. For more information about WMI as it relates to SMS, please see the SMS 2.0 Administrators Guide, the SMS 2.0 Resource Kit, or the SMS 2.0 SDK. According to the SMS SDK, the majority of the SMS class names in WMI begin with "SMS_". In the SQL views, the SMS_ prefix has been removed and replaced with "v_". Most of the property names have been kept the same, except for inventory classes where the inventory class names are slightly different because of TSQL string manipulation limitations. The following views have been added to the schema. - v_SchemaViews: Lists the schema views - v_GroupMap: Lists inventory classes and their associated history classes - v_GroupAttributeMap: Lists the attributes of the inventory classes - v_ResourceMap: Lists resource classes - v_ResourceAttributeMap: Lists attributes, including array attributes of the resource classes - v_AgentDiscoveries: Former agent array properties of SMS_R_System, and lists discovered resources and the discovery agent - v_AuditRootSummarizer: Summarizes the number of actions performed each day, for each user, for each action - v_AuditSummarizer: Summarizes the number of actions performed each day, for each user, for each action The following modifications have been made to the WMI schema: - v_OS_Details: Lists the supported platforms for a program (this was formerly an embedded object of SMS_Program) - v_CollectionRuleDirect: Lists the resources that are direct members of a collection (this was formerly an embedded object of SMS_Collection) - v_CollectionRuleQuery: Lists the queries that are used to populate a collection (this was formerly an embedded object of SMS_Collection) The following stored procedures have been added to enable regeneration of these views: - sp_RenewSMSSchemaViews: Regenerates all views - sp_RenewCollectionViews: Regenerates collection class views - sp_RenewResourceViews: Regenerates resource class views - sp_RenewInvGroupViews: Regenerates inventory group class views Additional query words: prodsms ====================================================================== Keywords : kbDatabase kbsms200 kbsms200sp2fix Technology : kbSMSSearch kbSMS200SP2 Version : :2.0 SP2 Issue type : kbinfo ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.