Getting error: Could not find stored procedure 'spResolveResourceTargetFullDelayLoad'

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

After installing one of the Rollups for SMP 7.1 SP2 (in this case the customer just installed Rollup v4 (See HOWTO64413)), the following message started to appear:

Unhandled exception.  Type=System.Data.SqlClient.SqlException Msg=Could not find stored procedure 'spResolveResourceTargetFullDelayLoad'.

<event date="Sep 24 21:51:13 +00:00" severity="1" hostName="AUYNPX75" source="global.asax" module="w3wp.exe" process="w3wp" pid="9012" thread="6" tickCount="579877977"><![CDATA[Unhandled exception.  Type=System.Data.SqlClient.SqlException Msg=Could not find stored procedure 'spResolveResourceTargetFullDelayLoad'.

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.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()   at Altiris.NS.DataAccessLayer.Implementation.Altiris_NS_StandardItems_Targeting_DataAccessLayerTargetingDataAccessLayer.spResolveResourceTargetFullEvaluateFromTempTablesWithDelayLoad(String Culture)
   at Altiris.NS.StandardItems.Targeting.ResourceTarget.CalculateRows(String trusteeList, Boolean bWithDelayLoad)
   at Altiris.NS.StandardItems.Targeting.ResourceTarget.Evaluate2()
   at Altiris.NS.UI.DataProviders.ResourceTargetBuilderGridProvider.GetGridData(String id)
   at Altiris.WebControls.GridControl.GetDataSource()
   at Altiris.WebControls.GridControl.Grid_BeforeCallback(Object sender, EventArgs e)
   at ComponentArt.Web.UI.Grid.OnLoad(EventArgs oArgs)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

Cause

During the installation of the Rollup v3 or v4, it runs item.config, which contains this new stored procedure. In some instances this process may time out or simply fails.

This is the section that calls this installation process:

<sqlObject name="spResolveResourceTargetFullDelayLoad">
<sqlStatement exists="true">
  <![CDATA[
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id( N'[dbo].[spResolveResourceTargetFullDelayLoad]' ) AND OBJECTPROPERTY( id, N'IsProcedure' ) = 1 )
DROP PROCEDURE [dbo].[spResolveResourceTargetFullDelayLoad]
  ]]>
</sqlStatement>
<sqlStatement exists="both">
  <![CDATA[
 CREATE PROCEDURE [dbo].[spResolveResourceTargetFullDelayLoad]
    @Culture varchar(10),
    @resourceTargetGuid uniqueidentifier = 0x0,
    @runSilent bit = 0,
    @rowsAdded int = NULL OUTPUT,
    @rowsRemoved int = NULL OUTPUT,
    @simulateOnly bit = 0,
    @tempTablesAlreadyExist bit = 0

AS

SET NOCOUNT ON

SET @rowsAdded = 0
SET @rowsRemoved = 0

CREATE TABLE #workingSetFull
(
    ResourceGuid uniqueidentifier NOT NULL PRIMARY KEY
)

EXEC spResolveResourceTargetFullPrepare @resourceTargetGuid, @runSilent, @rowsAdded OUTPUT, @rowsRemoved OUTPUT, @simulateOnly, @tempTablesAlreadyExist

IF ( @simulateOnly = 0 )
BEGIN

    IF ( @runSilent = 0 )
    BEGIN
        SELECT DISTINCT
            vri.Guid,
            CASE
                WHEN rta.IsLocalizable = 1 THEN dbo.fnLocalizeStringByGuid('item.name', vri.Guid, @Culture)
                else vri.[Name]
            END AS [Name],
            vri.[Name],
            vri.ModifiedDate,
            vri.ModifiedBy,
            vri.CreatedDate,
            vri.CreatedBy,
            vrt.Name
        FROM
            vResourceItem [vri]
            JOIN vResourceType vrt ON vrt.Guid = vri.ResourceTypeGuid
            JOIN ResourceTargetMembershipCache [ig]
                ON [vri].Guid = [ig].ResourceGuid AND [ig].ResourceTargetGuid = @resourceTargetGuid
            JOIN ResourceTypeAttribute rta ON rta.ResourceTypeGuid = vri.ResourceTypeGuid
    END

END
ELSE
BEGIN

    SELECT DISTINCT
        vri.Guid,
        CASE
            WHEN rta.IsLocalizable = 1 THEN dbo.fnLocalizeStringByGuid('item.name', vri.Guid, @Culture)
            ELSE vri.[Name]
        END AS [Name],
        vri.ModifiedDate,
        vri.ModifiedBy,
        vri.CreatedDate,
        vri.CreatedBy,
        vrt.Name
    FROM
        vResourceItem [vri]
        JOIN vResourceType vrt ON vrt.Guid = vri.ResourceTypeGuid
        JOIN #workingSetFull [ig] ON [vri].Guid = [ig].ResourceGuid
        JOIN ResourceTypeAttribute rta ON rta.ResourceTypeGuid = vri.ResourceTypeGuid

END

DROP TABLE #workingSetFull
  ]]>
</sqlStatement>
</sqlObject>

Resolution

Rerun the install.cmd for the Rollup that you are intending to install.

As well you could just run the following query to create the missing stored procedure: See attached spResolveResourceTargetFullDelayLoad.sql

Note: in some cases also spResolveResourceTargetFullPrepare can be missing. Attached is a copy of this stored procedure: spResolveResourceTargetFullPrepare.sql

 

Applies To

Symantec Management Platform 7.1 SP2 with Rollup v3 or v4