CRM 2016 organization update failed version 8.0.0.1088 to 8.1.0.359

Question Status

Verified
Shrikant Karpur asked a question on 5 Mar 2017 8:20 PM

The Orgainzation update fails with below error

06:07:50| Info| Applying latest dbUpdates to organization. Id=6c0bd087-8512-438b-89a3-0d1d450d7cb4, UniqueName=CRM.
06:08:20| Info| CrmAction execution time; InstallDatabaseUpdatesAction; 00:00:30.7941678
06:08:20| Error| Installer Complete: OrganizationDatabaseHotFixer - Error encountered
06:08:20| Error| Exception occured during Microsoft.Crm.Tools.Admin.OrganizationDatabaseHotFixer: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction
InnerException:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.Crm.CrmException: Error installing sp with name=t_ComponentSetTable
Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type. ---> System.Data.SqlClient.SqlException: Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type.
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 asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
at Microsoft.Crm.Metadata.SqlHelper.ExecuteBatches(IDbCommand sqlCommand, String sqlScript)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
--- End of inner exception stack trace ---
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresInternal(String targetType, String databaseName, String catalogXmlFile, Boolean dropExistingProceduresFirst, Boolean startTransaction, InstallationSequences installationSequenceNumber)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresUpdate(String targetType, String databaseName, String catalogXmlFile)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.Crm.Setup.Database.DllMethodAction.Execute(Guid organizationId, Nullable`1 operationType)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.ExecuteReleases(ReleaseInfo releaseInfo, Boolean isInstall)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.Install(Int32 languageCode, String configurationFilePath, Boolean upgradeDatabase, Boolean isInstall)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.InstallUpdate(String configurationFilePath, Boolean upgradeDatabase)
at Microsoft.Crm.Setup.Common.Update.DBUpdateDatabaseInstaller.ApplyDBUpdates(Guid orgId, Nullable`1 operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.ApplyDBUpdatesInternal(Guid orgId, String orgUniqueName, String releaseFilePath, Boolean ignoreErrors, OrganizationOperationType operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.ApplyDBUpdates(Guid orgId, String orgUniqueName, OrganizationOperationType operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.Do(IDictionary parameters)
at Microsoft.Crm.Setup.Shared.CrmAction.ExecuteAction(CrmAction action, IDictionary parameters, Boolean undo)
InnerException:
Microsoft.Crm.CrmException: Error installing sp with name=t_ComponentSetTable
Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type. ---> System.Data.SqlClient.SqlException: Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type.
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 asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
at Microsoft.Crm.Metadata.SqlHelper.ExecuteBatches(IDbCommand sqlCommand, String sqlScript)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
--- End of inner exception stack trace ---
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresInternal(String targetType, String databaseName, String catalogXmlFile, Boolean dropExistingProceduresFirst, Boolean startTransaction, InstallationSequences installationSequenceNumber)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresUpdate(String targetType, String databaseName, String catalogXmlFile)
InnerException:
System.Data.SqlClient.SqlException (0x80131904): Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type.
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 asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
at Microsoft.Crm.Metadata.SqlHelper.ExecuteBatches(IDbCommand sqlCommand, String sqlScript)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
ClientConnectionId:42aef1b7-802e-4436-a4f4-13ff917ca1d2
Error Number:3732,State:1,Class:16

06:08:20| Info| Setting organization state. New state = Failed
06:08:21| Error| Update Organization with Id=6c0bd087-8512-438b-89a3-0d1d450d7cb4 failed with Exception:
System.Exception: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.Crm.CrmException: Error installing sp with name=t_ComponentSetTable
Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type. ---> System.Data.SqlClient.SqlException: Cannot drop type 'dbo.t_ComponentSetTable' because it is being referenced by object 'p_RetrieveMultipleDirectAncestors'. There may be other objects that reference this type.
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 asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
at Microsoft.Crm.Metadata.SqlHelper.ExecuteBatches(IDbCommand sqlCommand, String sqlScript)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
--- End of inner exception stack trace ---
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProcedureInternal(StoredProcedureData spData, SqlExecutionContext sqlContext)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresInternal(String targetType, String databaseName, String catalogXmlFile, Boolean dropExistingProceduresFirst, Boolean startTransaction, InstallationSequences installationSequenceNumber)
at Microsoft.Crm.Setup.StoredProcedureService.InstallStoredProceduresUpdate(String targetType, String databaseName, String catalogXmlFile)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.Crm.Setup.Database.DllMethodAction.Execute(Guid organizationId, Nullable`1 operationType)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.ExecuteReleases(ReleaseInfo releaseInfo, Boolean isInstall)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.Install(Int32 languageCode, String configurationFilePath, Boolean upgradeDatabase, Boolean isInstall)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.InstallUpdate(String configurationFilePath, Boolean upgradeDatabase)
at Microsoft.Crm.Setup.Common.Update.DBUpdateDatabaseInstaller.ApplyDBUpdates(Guid orgId, Nullable`1 operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.ApplyDBUpdatesInternal(Guid orgId, String orgUniqueName, String releaseFilePath, Boolean ignoreErrors, OrganizationOperationType operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.ApplyDBUpdates(Guid orgId, String orgUniqueName, OrganizationOperationType operationType)
at Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction.Do(IDictionary parameters)
at Microsoft.Crm.Setup.Shared.CrmAction.ExecuteAction(CrmAction action, IDictionary parameters, Boolean undo)
--- End of inner exception stack trace ---
at Microsoft.Crm.Setup.Shared.CrmAction.ExecuteAction(CrmAction action, IDictionary parameters, Boolean undo)
at Microsoft.Crm.Setup.Shared.Installer.Install(IDictionary stateSaver)
at Microsoft.Crm.Tools.Admin.OrganizationOperation.Install(IDictionary stateSaver)
at Microsoft.Crm.Tools.Admin.OrganizationOperation.Execute()
at Microsoft.Crm.Tools.Admin.OrganizationDatabaseHotFixer.Execute()
at Microsoft.Crm.Tools.Admin.ApplyDatabaseHotFixInstaller.ApplyDatabaseHotFix(IApplyDatabaseHotFixInfo applyDatabaseHotFixInfo)
06:08:21| Info| Found the older table DBUpdateInstallInfo_V5, updating new table and deleting
06:08:21| Info| GetDBUpdateRevisionThresholdForServer(): Threshold = 8.1.0000.0359.

Reply
Suggested Answer
Nadeeja Bomiriya responded on 5 Mar 2017 8:43 PM

Hi Shrikant,

Please check out below threads which provides a workaround for the issue.

community.dynamics.com/.../184508

community.dynamics.com/.../201943

Reply
Shrikant Karpur responded on 6 Mar 2017 11:04 PM

Thanks Nadeeja,

I tried the solution from the link community.dynamics.com/.../201943

and i am stuck at a stage as how do i manually remove tables/views from the Catalog.

it would be gr8 if you could guide me thru this stage as i can try it further.

regards

Reply
Shrikant Karpur responded on 6 Mar 2017 11:22 PM

Sorry i got the solution to remove the tables / views from the catalog and i m trying it further will update you on the final outcome.

Reply
Verified Answer
Shrikant Karpur responded on 6 Mar 2017 11:49 PM

The issue was resolved by the below solution.

Drop the stored procedure [dbo].[p_RetrieveMultipleDirectAncestors]  and run the update again.

Note:Please test this on a test database....first and have backup before you execute this step as it has worked for me.

Reply
Suggested Answer
Kesh Patel responded on 5 Sep 2017 8:56 PM

I have the same upgrade error. I drop [dbo].[p_RetrieveMultipleDirectAncestors] store procedure and then upgraded to sp1 and this worked for me as well.

Reply
shuey79 responded on 7 Feb 2018 10:19 AM

This worked for me as well. However, once the update completes do you add back the stored procedure?

Reply
Kesh Patel responded on 7 Feb 2018 4:27 PM

I think upgrade procedure take care of recreating stored procedure. I do not remember recreating droped procedure. Check your self and confirm that updrade has recreated or not?

Reply
Verified Answer
Shrikant Karpur responded on 6 Mar 2017 11:49 PM

The issue was resolved by the below solution.

Drop the stored procedure [dbo].[p_RetrieveMultipleDirectAncestors]  and run the update again.

Note:Please test this on a test database....first and have backup before you execute this step as it has worked for me.

Reply
Suggested Answer
Nadeeja Bomiriya responded on 5 Mar 2017 8:43 PM

Hi Shrikant,

Please check out below threads which provides a workaround for the issue.

community.dynamics.com/.../184508

community.dynamics.com/.../201943

Reply
Suggested Answer
Kesh Patel responded on 5 Sep 2017 8:56 PM

I have the same upgrade error. I drop [dbo].[p_RetrieveMultipleDirectAncestors] store procedure and then upgraded to sp1 and this worked for me as well.

Reply