asked on

SMS 2003 MMC Querie in SQL 2005

Using SMS 2003 querie dsigner, the following quiery has been created to return computer information. The query runs fine using the SMS 2003 MMC console, however, it is slow.

I would like to run the query directlry against the SMS SQL 2005 database using SQL views.

I have created a view and copied and pasted the SQL code from SMS designer, however, I am unsure what the "SMS_G_System" table names etc should be changed to so that the query will work.

Is there a simple solution?

SELECT DISTINCT 
                      dbo.SMS_R_System.Name, dbo.SMS_R_System.IPAddresses, dbo.SMS_R_System.MACAddresses, 
                      dbo.SMS_R_System.OperatingSystemNameandVersion, dbo.SMS_R_System.ResourceDomainORWorkgroup, 
                      dbo.SMS_G_System_COMPUTER_SYSTEM.Model, dbo.SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.Version, 
                      dbo.SMS_G_System_PROCESSOR.ProcessorType, dbo.SMS_G_System_WORKSTATION_STATUS.LastHardwareScan AS Expr2, 
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway AS Expr1, dbo.SMS_G_System_PROCESSOR.Manufacturer, 
                      dbo.SMS_G_System_PROCESSOR.NormSpeed
FROM         dbo.SMS_R_System INNER JOIN
                      dbo.SMS_G_System_COMPUTER_SYSTEM ON dbo.SMS_G_System_COMPUTER_SYSTEM.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_X86_PC_MEMORY ON dbo.SMS_G_System_X86_PC_MEMORY.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS ON 
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_PROCESSOR ON dbo.SMS_G_System_PROCESSOR.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_WORKSTATION_STATUS ON 
                      dbo.SMS_G_System_WORKSTATION_STATUS.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION ON 
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = dbo.SMS_R_System.ResourceId

Open in new window

reb73

Don't think you have to change any of the tablenames as such, you just need to wrap the code above within a CREATE VIEW statement like -

CREATE VIEW [dbo].[<viewname>]
AS
<Your select statement goes here>
GO

ASKER CERTIFIED SOLUTION

alliedfusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial