Microsoft KB Archive/153534

= SMS: Retrieving SMSVIEW Data Using Microsoft Access =

Article ID: 153534

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 1.0 Standard Edition
 * Microsoft Systems Management Server 1.1 Standard Edition
 * Microsoft Systems Management Server 1.2 Standard Edition

-



This article was previously published under Q153534



SUMMARY
There are problems associated with Querying within SMS - SMS Query Results Display Only First Data Record. You must use another program to retrieve the correct data. For instance, Access can be used to retrieve drive information from the Systems Management Server Database Views.



MORE INFORMATION
To retrieve drive information from the Systems Management Server Database Views using Microsoft Access, perform the following steps:

 Run SMSVIEW to create the views. Confirm SQL connectivity using ODBC Manager. Start Access and create a new database. On the File menu, point to Get External Data, and click Import. In Files of Type, select ODBC Databases. Select your Systems Management Server data source and log in to SQL. Select all the tables that are preceded by 'dbo.v' - the views.</li> Open the Tools / Relationships and Added dbo.vDisk and dbo_vIdentification.</li> In the first table, select dwMachineID. Drag and drop dwMachineID into the second table.</li> Open the Relationships window and select Create.</li> In the Database window, select Queries, and then select New.</li> Use the Simple Query Wizard.</li> At the first screen, select dbo.vIdentification and choose SMSID0 as the selected field. Select dbo.vDisk and choose Disk_Index0, Storage_Size_MByte_0, Storage_Used_MByte_0, Free_Storage_MByte_0, and __Disk_Full0 as selected fields.</li> At the next screen select Detail. At the Final screen give it a title and select Finish. At this point you should see all your SMSIDs and associated drives.</li>  Select View and select SQL. You should see a query similar to the following: SELECT DISTINCTROW [dbo.vIdentification].[SMSID0], [dbo.vDisk].[Disk_Index0], [dbo.vDisk].[Storage_Size__MByte_0], [dbo.vDisk].[Storage_Used__MByte_0], [dbo.vDisk].[Free_Storage__MByte_0], [dbo.vDisk].[__Disk_Full0] FROM dbo.vDisk INNER JOIN dbo.vIdentification ON     dbo.vDisk].[dwMachineID]=[dbo.vIdentification].[dwMachineID]; </li>  Modify the query you found in step 15 to include the following WHERE statement so that it is the final entry. Note that the query ends with a semicolon. Remove the semicolon from the original query before you add the final line: WHERE (((dbo.vDisk.Disk_Index0)>"B")); The following table is an example of the output you may receive: <pre class="fixed_text">                         Storage_   Storage_   Free_ Disk_   Size__     Used__     Storage__   __Disk_ SMSID0    Index0   MByte_0    MByte_0    MByte_0     Full0 - --         -         UUU01000   C        324        284        40          88 UUU01000  D        324        267        57          83 UUU02000  C        502        398        104         80 UUU02000  D        1545       1426       119         93 UUU02001  C        514        347        167         68 UUU01001  C        514        347        167         68 UUU01002  C        202        171        31          85 </li></ol>

Additional query words: prodsms views smsview.exe report

Keywords: kbdatabase kbfaq kbinfo KB153534

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.