While trying to repair a previous installation, getting: View or function 'String' is not updatable because the modification affects multiple base tables.
search cancel

While trying to repair a previous installation, getting: View or function 'String' is not updatable because the modification affects multiple base tables.

book

Article ID: 184823

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

You are trying to run a repair on an SMP server that had an issue while trying to upgrade to a recent version of ITMS.

While trying to repair the previous ITMS installation, the following error occurred while trying to configure PluggableProtocols.config:

Failed to install the product from the file: F:\PluggableProtocols\Config\PluggableProtocols.config
View or function 'String' is not updatable because the modification affects multiple base tables.

Failed to install the product from the file: F:\PluggableProtocols\Config\PluggableProtocols.config

View or function 'String' is not updatable because the modification affects multiple base tables.
   [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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.DataAccessLayer.Implementation.Altiris_PluggableProtocols_DataAccessLayerConnectionProfileDataAccessLayer.spRemoveExtraStrings()
   at Altiris.PluggableProtocols.PluggableProtocolsInstallation.OnInstallProductPostDatabase(XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct_Database(XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.OnInstallProduct(XmlNode installationNode)
   at Altiris.PluggableProtocols.PluggableProtocolsInstallation.OnInstallProduct(XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct()
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal(String configFile, Boolean ownsProgressContext, SerializationMode serializationMode)

SQL Exception details: code=4405, line=3, procedure=spRemoveExtraStrings

Exception logged from: 
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal(String, Boolean, Altiris.NS.Serialization.SerializationMode)
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatchInternal()
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatch()
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object)
   at System.Threading.ThreadHelper.ThreadStart()

-----------------------------------------------------------------------------------------------------

Date: 26/12/2019 2:05:00 p. m., Tick Count: 185412734 (2.03:30:12.7340000), Size: 3,77 KB
Process: AeXSvc (6092), Thread ID: 54, Module: Altiris.NS.dll
Priority: 1, Source: Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal

 

Similar behavior may happen with:

Failed to install product.

View or function 'dbo.TaskOutputPropertyValue' is not updatable because the modification affects multiple base tables.
   [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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass9`1.<PerformTransactedDeadlockRetry>b__8(IDatabaseContext ctx)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry, DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformTransactedDeadlockRetry(Int32 retries, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlNodes(XmlNodeList nodes, Boolean installing, Boolean resetConnection)
   at Altiris.NS.Installation.Bulk.Installers.Core.CoreInstaller.InstallSqlNodes(XmlNodeList nodes, Boolean useBulkInstaller)
   at Altiris.NS.Installation.Bulk.Installers.Core.CoreInstaller.InstallSqlNodes(XmlNodeList sqlNodes)
   at Altiris.NS.StandardItems.Product.ProductInstallation.Execute(String taskKey, String description, XmlNode node, String xPath, Action`1 actus)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct_Database(XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.OnInstallProduct(XmlNode installationNode)
   at Altiris.TaskManagement.TaskManagementInstallation.OnInstallProduct(XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct()

SQL Exception details: code=4405, line=13, procedure=CtsInsertOutputProperty

Environment

ITMS 8.x

Cause

In this particular instance, due to the original upgrade failure to a newer ITMS version, the "String" table got updated to be a "View". 

This caused "spRemoveExtraStrings" called during the PluggableProtocols configuration process to fail. This "pRemoveExtraStrings" calls:

Delete From String Where 
      StringRef = 'item.name' AND 
      BaseGuid in (select Guid from CMCredentialType union select Guid from CMCredentialScalar) AND
      Culture Not In ('','de','es','fr','it','ja','ko','pt','ru','zh-Hans','zh-Hant')
 
and since "String" is a "View" rather than a "Table" type as originally intended, it causes the process to fail. In ITMS 8.5 and later some tables were changed from tables to views and since this customer had some of those changed during their initial attempt to upgrade, the repair is unable to actually use those changes.

Resolution

Since this is an edge case due to a faulty upgrade, you are better off restoring your database to a point right before the upgrade attempt (you may need to restore your SMP as well to the same version as the database). 

In case you can't restore, one approach to address this issue is to adjust "String" from a View to a Table type:

  1. Validate that "String" (or any other one that shows the same error" is a Table or a View type:
    select type from sysobjects where name = 'string'
  2. If it returned a "V", it means that it is a "View" instead of a Table type
  3. Make a backup of your database.

  4. Drop this view:

    drop view String

    Note:
    If you wish, make a copy of this "String" view as "String2" in case it is needed later on:

    ​CREATE VIEW [dbo].[String2]
    AS
        SELECT    CAST( 0x0 AS uniqueidentifier ) AS [Guid],
                  sd.[BaseGuid],
                  sd.[StringRef],
                  cc.[Culture],
                  sd.[String],
                  CAST ( '2011-11-11' AS datetime ) AS [CreatedDate],
                  CAST ( '2012-12-12' AS datetime ) AS [ModifiedDate]
            FROM  (
                    SELECT      ItemGuid AS [BaseGuid], CultureId, String, StringRef
                        FROM (
                                SELECT *
                                    FROM (
                                           SELECT  ItemGuid, CultureId, CAST(s.[ItemName] AS nvarchar(1000)) AS [item.name], s.[ItemDesc] AS [item.description]
                                              FROM dbo.ItemString                     s
                                         ) AS aa
                             ) AS p
                        UNPIVOT
                             (
                                String FOR StringRef IN ( [item.name], [item.description] )
                             ) AS pvt
                 ) sd
            JOIN  dbo.CultureChain cc ON cc.CultureId = sd.CultureId AND cc.Culture = cc.BaseCulture
        UNION ALL
        SELECT    CAST( 0x0 AS uniqueidentifier ) AS [Guid],
                  sd.[BaseGuid],
                  sr.StrRef AS [StringRef],
                  cc.[Culture],
                  sd.[String],
                  CAST ( '2011-11-11' AS datetime ) AS [CreatedDate],
                  CAST ( '2012-12-12' AS datetime ) AS [ModifiedDate]
            FROM  dbo.StringData   sd
            JOIN  dbo.StringRef    sr ON sr.StrRefId = sd.StringRefId
            JOIN  dbo.CultureChain cc ON cc.CultureId = sd.CultureId AND cc.Culture = cc.BaseCulture
    GO

  5. Create "String" as a table:
    /****** Object:  Table [dbo].[String]    Script Date: 26/12/2019 02:31:48 p.m. ******/
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO
     
    SET ANSI_PADDING ON
    GO
     
    CREATE TABLE [dbo].[String](
    [Guid] [uniqueidentifier] NOT NULL,
    [BaseGuid] [uniqueidentifier] NOT NULL,
    [StringRef] [nvarchar](255) NOT NULL,
    [Culture] [varchar](10) NOT NULL,
    [String] [nvarchar](1024) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_String] PRIMARY KEY CLUSTERED 
    (
    [BaseGuid] ASC,
    [StringRef] ASC,
    [Culture] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
     
    GO
     
    SET ANSI_PADDING OFF
    GO
     
    ALTER TABLE [dbo].[String]  WITH CHECK ADD CHECK  (([StringRef]=lower([StringRef])))
    GO

  6. Since the original "String" table has few fewer columns like the "String" view, you can manually add those (if you created a "String2" view) in case those are needed by another stored procedure:
     
    insert into string 
    select * from string2

  7. Run again the reconfiguration process using SIM (Symantec Installation Manager).
Powered by Wolken Software