After upgrading to ITMS 7.6 HF6, an error under 'spAgentPolicyProtKey_Save' started to show up when trying to register client machines.

Products

• IT Management Suite

Issue/Introduction

Customer recently upgraded to ITMS 7.6 HF6, either from ITMS 7.6 of from previous Hotfixes prior to HF4), the following error started to show up when trying to register client machines:

Procedure or function 'spAgentPolicyProtKey_Save' expects parameter '@ItemGuid', which was not supplied.

Error Message
Source: Altiris.NS.AgentManagement.Enrollment.EnrollmentManager.ProcessEnrollmentForResource
Description: Registration process failed for resource: a2645d58-f542-41c5-a604-170f6f5fcce5
Procedure or function 'spAgentPolicyProtKey_Save' expects parameter '@ItemGuid', which was not supplied.
[System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader

Cause

There were some updates to this stored procedure post ITMS 7.6 HF4 that removed some of the previous parameters. Sometimes these updates are not committed during the HF6 upgrade.

This was also seen with a customer who had installed 7.6 HF2 as an upgrade from 7.1 SP2 HF11

Resolution

Please run the following queries on your SQL Server Management Studio for the Symantec_CMDB adatabase:

1. DROP PROCEDURE [dbo].[spAgentPolicyProtKey_Save]

2. CREATE PROC [dbo].[spAgentPolicyProtKey_Save]
   (
       @ResourceGuid       uniqueidentifier,
       @RegistrationDate   datetime,
       @State              int,
       @Type               int,
       @PublicKey          varbinary(max)
   )
   WITH ENCRYPTION
   AS
   BEGIN
       DECLARE @tmp_ItemGuid TABLE ( [Guid] uniqueidentifier )

       INSERT INTO       SecurityResourceProtection ( [Guid], [ResourceGuid], [RegistrationDate], [State], [Type], [PublicKey] )
           OUTPUT        INSERTED.[Guid]
           INTO          @tmp_ItemGuid
           SELECT        NEWID(), sav.ResourceGuid, @RegistrationDate, @State, @Type, @PublicKey
               FROM    ( SELECT @ResourceGuid AS [ResourceGuid] ) AS sav
               LEFT JOIN SecurityResourceProtection srp ON srp.ResourceGuid = sav.ResourceGuid
               WHERE     srp.ResourceGuid IS NULL

       IF ( @@ROWCOUNT = 0 )
           UPDATE     srp
               SET    [PublicKey]        = @PublicKey,
                      [Type]             = @Type,
                      [State]            = @State,
                      [RegistrationDate] = @RegistrationDate
               OUTPUT INSERTED.[Guid]
               INTO   @tmp_ItemGuid
               FROM   SecurityResourceProtection srp
               WHERE  ResourceGuid = @ResourceGuid

       SELECT [Guid] FROM @tmp_ItemGuid
   END     

3. After getting the right version of this stored procedure, there are other 2 that also needs to be updated:

   • DROP PROCEDURE [dbo].[spGetCandidateEventsByQueue]

   • CREATE PROC [dbo].[spGetCandidateEventsByQueue]
       @queueId    int,
       @maxResults int,
       @nonPriorityOnly bit = 0
     AS
     BEGIN
       SET NOCOUNT ON
      
       ;WITH QueueCandidates( Id, ProcessingState, LastRunDate, DataInLine, [File], [Size] )
       AS
       (
           SELECT TOP (@maxResults) Id
                                   ,ProcessingState
                                   ,LastRunDate
                                   ,DataInLine
                                   ,[File]
                                   ,[Size]
               FROM     EventQueueEntry WITH (READCOMMITTEDLOCK)
               WHERE    ProcessingState = 0
               AND      QueueId = @queueId
             AND      (@nonPriorityOnly = 0 or [Priority] = 0)
               ORDER BY [Priority], Id
       )
       UPDATE     QueueCandidates
           SET    ProcessingState = 2,
                   LastRunDate     = GETDATE()
           OUTPUT INSERTED.Id, INSERTED.DataInLine, INSERTED.[File], INSERTED.[Size]
     END

   • DROP PROC dbo.spGetQueueStats

   • CREATE PROC [dbo].spGetQueueStats
           AS
           BEGIN
               SET NOCOUNT ON    
               select Id, [TotalCount], [TotalSize], [ChangeTime] FROM EventQueue
           END