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

RESOLUTION

1. Click Start, point to Programs, click Microsoft SQL Server, and then click Query Analyzer.
2. In SQL Query Analyzer, click the database list on SQL Query Analyzer menu bar, and then click the SMS database.
3. 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

4. In SQL Query Analyzer, click Parse Query to look for errors, and then click Execute Query.
5. Quit SQL Query Analyzer.