After SP2 update the software console don't show the computers associates to a software resource

[Migration User]

After SP2 update the software console doesn't show the computers associates to a software resource. Everytime that I select a software resource, in Altiris Log Viewer I see this message:

Process: w3wp (1784)
Thread ID: 669
Module: w3wp.exe
Source: Altiris.NS.Services.CoreServices.ResourceService.GetComputersBySoftware
Description: Unable to get computers with <name and GUID of software> software installed: Invalid column name 'FileResourceGuid'.

StackTrace:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at Altiris.NS.Services.CoreServices.ResourceService.GetComputersBySoftware(String softwareGuids, String computerName, Boolean selectTop)

 

[Migration User]

Hello, we have migrated to two of our customers and associates to work properly with the resource software .. We're just having problems with pcAnywhere and we have an open case with Symantec.

[Migration User]

Check the steps in this article
 

http://www.symantec.com/docs/TECH177513

[Migration User]

I don't know why. Previously I have upgraded my development environement and I hadn't problems. It seems that the database was corrupted. The error message talk about a colum that is missing 'FileResourceGuid'

I have opened a ticket with support and I'm waiting for a response...

[Migration User]

I have looked into database and I've found the stored procedure "spAC_GetComputerBySoftwareProduct" and "spAC_GetComputersBySoftwareComponent" that return the same error that I see in Altiris Log Viewer:

Msg 207, Level 16, State 1, Procedure spAC_GetComputersBySoftwareProduct, Line 40
Invalid column name 'FileResourceGuid'.
The statement has been terminated.

I have compared this stored procedure with the same that I have in my development environment and I have see that aren't equals.

For example this is the SP that not work:

USE [Symantec_CMDB]
GO
/****** Object:  StoredProcedure [dbo].[spAC_GetComputersBySoftwareProduct]    Script Date: 12/27/2011 15:28:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spAC_GetComputersBySoftwareProduct]             
          (             
             @SoftwareComponentGuids varchar(max),
             @ComputerName nvarchar(250),
             @SelectTop bit,                 
             @Trustee varchar(max)        
          )             
            As             
            Set NoCount On 
                               
            If Exists (SELECT * FROM sysobjects WHERE name = 'Inv_Software_Execution') --make sure inventory is installed     
            Begin    
                                   
              if(@SelectTop = 1)
                Begin
                  SELECT TOP 500 vc.[Guid] AS [ResourceGuid] 
                       , vc.[Name] 
                       , vc.[OS Name] 
                       , ISNULL(usage.RunCount,0) AS UsedCount 
                       , '' As DeliveryStatus 
                       , prod.Guid As SoftwareComponentGuid 
                       , prod.[Name] As SoftwareName 
                       , usage.LastStart 
                       , usage.InstallDate            
                  FROM [fnAC_SWProductUsed](@SoftwareComponentGuids) usage
                  JOIN vRM_Software_Product_Item prod 
                     ON prod.Guid = usage.SoftwareProductGuid
                  JOIN vComputer vc
                     ON vc.Guid = usage._ResourceGuid
                  WHERE vc.Guid IN (Select ResourceGuid From [fnGetTrusteeScopedResources](@Trustee))
                    AND vc.IsManaged = 1
                    AND (@ComputerName = '%%' OR LOWER(vc.Name) LIKE @ComputerName)
                    ORDER BY vc.Name ASC 
                End
              Else
                Begin
                  SELECT  vc.[Guid] AS [ResourceGuid] 
                     , vc.[Name] 
                     , vc.[OS Name]
                     , ISNULL(usage.RunCount,0) AS UsedCount 
                     , '' As DeliveryStatus 
                     , prod.Guid As SoftwareComponentGuid 
                     , prod.[Name] As SoftwareName 
                     , usage.LastStart 
                     , usage.InstallDate            
                    FROM [fnAC_SWProductUsed](@SoftwareComponentGuids) usage
                    JOIN vRM_Software_Product_Item prod 
                       ON prod.Guid = usage.SoftwareProductGuid
                    JOIN vComputer vc
                     ON vc.Guid = usage._ResourceGuid
                    WHERE vc.Guid IN (Select ResourceGuid From [fnGetTrusteeScopedResources](@Trustee))
                      AND vc.IsManaged = 1
                      AND (@ComputerName = '%%' OR LOWER(vc.Name) LIKE @ComputerName)
                      ORDER BY vc.Name ASC 
                End
           End

 

and this is the SP that work:

SE [Symantec_CMDB]
GO
/****** Object:  StoredProcedure [dbo].[spAC_GetComputersBySoftwareProduct]    Script Date: 12/27/2011 15:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spAC_GetComputersBySoftwareProduct]             
          (             
             @SoftwareComponentGuids varchar(max),
             @ComputerName nvarchar(250),
    @SelectTop bit,                 
             @Trustee varchar(max)        
          )             
            As             
            Set NoCount On 
                               
            If Exists (SELECT * FROM sysobjects WHERE name = 'Inv_Software_Execution') --make sure inventory is installed     
            Begin    
                                   
              if(@SelectTop = 1)
               Begin
                  SELECT TOP 500 vc.[Guid] AS [ResourceGuid] 
                       , vc.[Name] 
                       , ident.[OS Name] 
                       , ISNULL(usage.RunCount,0) AS UsedCount 
                       , '' As DeliveryStatus 
                       , prod.Guid As SoftwareComponentGuid 
                       , prod.[Name] As SoftwareName 
                       , usage.LastStart 
                       , usage.InstallDate            
                  FROM [fnAC_SWProductUsed](@SoftwareComponentGuids) usage
                  JOIN vRM_Software_Product_Item prod 
                     ON prod.Guid = usage.SoftwareProductGuid
                  JOIN vRM_Computer_Item vc
                     ON vc.Guid = usage._ResourceGuid
                   JOIN Inv_AeX_AC_Identification ident
                     ON ident._ResourceGuid = vc.Guid

                  WHERE vc.Guid IN (Select ResourceGuid From [fnGetTrusteeScopedResources](@Trustee))
             AND vc.IsManaged = 1
                    AND LOWER(vc.Name) LIKE @ComputerName
                    ORDER BY vc.Name ASC 
               End
             Else
               Begin
               SELECT  vc.[Guid] AS [ResourceGuid] 
                     , vc.[Name] 
                     , ident.[OS Name] 
                     , ISNULL(usage.RunCount,0) AS UsedCount 
                     , '' As DeliveryStatus 
                     , prod.Guid As SoftwareComponentGuid 
                     , prod.[Name] As SoftwareName 
                     , usage.LastStart 
                     , usage.InstallDate            
                    FROM [fnAC_SWProductUsed](@SoftwareComponentGuids) usage
                    JOIN vRM_Software_Product_Item prod 
                       ON prod.Guid = usage.SoftwareProductGuid
                    JOIN vRM_Computer_Item vc
                       ON vc.Guid = usage._ResourceGuid
                     JOIN Inv_AeX_AC_Identification ident
                       ON ident._ResourceGuid = vc.Guid

                    WHERE vc.Guid IN (Select ResourceGuid From [fnGetTrusteeScopedResources](@Trustee))
               AND vc.IsManaged = 1
                      AND LOWER(vc.Name) LIKE @ComputerName
                      ORDER BY vc.Name ASC 
               End
            End

I don't understand why are different.... and now my dubt is: are these the only differents?

[Migration User]

With a tool I have compared the CMDB in production environment with the one in my development environment and I have seen that were some differences.

With the support we have seen that for inexplicable reason the SP2 is not correctly applied on all my product. We have re-applied the SP2 for the products not upgraded and now the issue is gone.