Microsoft KB Archive/322476

= Blank lines appear in the Hardware History node of a Systems Management Server 2003 client in Resource Explorer =

Article ID: 322476

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003

-



This article was previously published under Q322476





SYMPTOMS
When perform a hardware inventory cycle on a Systems Management Server (SMS) 2003 client computer, blank lines appear when you browse the Hardware History node of the SMS 2003 computer in Resource Explorer.



CAUSE
This problem occurs when you extend the hardware inventory with data that is contained in a NOIDMIF file, and then you change a value in the NOIDMIF file. In this case, the data is changed and a blank line appears in the Hardware History node in SMS 2003 Resource Explorer.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



RESOLUTION
To resolve this problem, modify the store producer by using SQL Query Analyzer. To do this, follow these steps:  Click Start, point to Programs, click Microsoft SQL Server, and then click Query Analyzer. In SQL Query Analyzer, click the database list on SQL Query Analyzer menu bar, and then click the SMS database.  Copy the following code, and then paste the code in SQL Query Analyzer. CREATE PROCEDURE sp_GetInvHistory      @ArchKey int,    @GroupKey int,    @ResourceID int,    @TimeKey varchar(25) = '',  @Trace int = 0  AS    BEGIN declare @TableName varchar(30), @HistTableName varchar(30) select @TableName = SpecificTableName, @HistTableName = HistoryTableName from GroupMap where ArchitectureKey = @ArchKey and GroupKey = @GroupKey IF isnull(@TableName,) =  BEGIN raiserror 99903 'This is not a valid group.' return END IF isnull(@HistTableName,) =  BEGIN raiserror 99903 'This is not a history group.' return END declare @SQL varchar(255), @SQL1 varchar(255), @SQL2 varchar(255) declare @DateIndex datetime IF datalength(@TimeKey) > 0 select @DateIndex = @TimeKey create table #Instances (InstanceKey int,   TimeKey datetime  ) create table #ReturnInstances (  MachineID int,   InstanceKey int,    TimeKey datetime,   IsDeleted int  ) /* Current instances */ select @SQL = ' insert into #Instances select InstanceKey, TimeKey from ' + @TableName   select @SQL = @SQL + ' where MachineID = ' + convert(varchar(20), @ResourceID) exec (@SQL) /* All historical instances */ IF (@Trace = 1) BEGIN select @SQL Current_Query print 'Current instances' select * from #Instances END select @SQL = ' insert into #Instances select InstanceKey, TimeKey from ' + @HistTableName select @SQL = @SQL + ' where MachineID = ' + convert(varchar(20), @ResourceID) exec (@SQL) IF (@Trace = 1) BEGIN select @SQL History_Query print 'Current and historical instances' select * from #Instances END /* Populate the return table */ IF (@TimeKey <> '') BEGIN /* Add the exact date matches */ insert into #ReturnInstances select distinct @ResourceID, InstanceKey, TimeKey, 0 from #Instances where TimeKey between dateadd(minute, -1, @TimeKey) and dateadd(minute, 1, @TimeKey) IF (@Trace = 1) BEGIN print 'Exact date matches ' select * from #ReturnInstances END /* Add the 'missing groups' */ insert into #ReturnInstances select @ResourceID, InstanceKey, max(TimeKey), 0 from #Instances where TimeKey < @DateIndex and InstanceKey not in (select InstanceKey from #ReturnInstances) group by InstanceKey IF (@Trace = 1) BEGIN print 'Adding deleted groups' select * from #ReturnInstances END END ELSE BEGIN insert into #ReturnInstances select distinct @ResourceID, InstanceKey, TimeKey, 0 from #Instances END /* Finally, was this instance deleted? */  select @SQL = ' update r set IsDeleted = 1 ' select @SQL = @SQL + ' from #ReturnInstances as r LEFT OUTER JOIN ' + @TableName + ' as t1 on ' select @SQL = @SQL + ' t1.MachineID = r.MachineID and t1.InstanceKey = r.InstanceKey ' select @SQL = @SQL + ' where t1.InstanceKey is null' exec (@SQL) IF (@Trace = 1) BEGIN print 'Updating IsDeleted flag' select * from #ReturnInstances END/* Return all data desc by TimeKey */ select @SQL = 'select t1.*, r.IsDeleted from ' + @HistTableName select @SQL = @SQL + ' as t1 INNER JOIN #ReturnInstances as r ' select @SQL = @SQL + ' on r.MachineID = t1.MachineID and r.InstanceKey = t1.InstanceKey ' select @SQL = @SQL + ' where r.TimeKey = t1.TimeKey and AgentId IS NOT NULL UNION ' select @SQL1 = ' select t1.*, r.IsDeleted from ' + @TableName select @SQL1 = @SQL1 + ' as t1 INNER JOIN #ReturnInstances as r ' select @SQL1 = @SQL1 + ' on r.MachineID = t1.MachineID and r.InstanceKey = t1.InstanceKey ' select @SQL1 = @SQL1 + ' where r.TimeKey = t1.TimeKey ' if (@TimeKey = '') select @SQL2 = @SQL2 + ' order by t1.TimeKey desc' else select @SQL2 = @SQL2 + ' order by t1.InstanceKey asc ' exec (@SQL+@SQL1+@SQL2 ) IF (@Trace = 1) BEGIN print 'Final Query' select @SQL, @SQL1, @SQL2 END END GO  In SQL Query Analyzer, click Parse Query to look for errors, and then click Execute Query. Quit SQL Query Analyzer.</li></ol>

Additional query words: prodsms sms2003 mof mif

Keywords: kbprb KB322476

-

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

© Microsoft Corporation. All rights reserved.