search cancel

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

book

Article ID: 162725

calendar_today

Updated On:

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