Customer experience | Sales, Customer Insights,...

Cannot delete custom field even there are no any dependencies

(0) Share

Report

Posted on by Community Member

The environment is on premise, version is 1612 (9.0.12.4) (DB 9.0.12.4).

On one custom entity one field makes problem when trying to delete it. There were dependencies (on several views and one business process). All of them were successfully removed.

I tried to delete this field using Attribute Manager via XRM ToolBox.

This is error:

Beginning Step: Remove Existing Attribute
Deleting Field
System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: Generic SQL error. (Fault Detail is equal to Exception details:
ErrorCode: 0x80044150
Message: Generic SQL error.
--
Exception details:
ErrorCode: 0x80044150
Message: Generic SQL error.
--
Exception details:
ErrorCode: 0x80040216
Message: Exception output:: System.Data.SqlClient.SqlException (0x80131904): The index 'ndx_QF_cus_FieldName' is dependent on column 'cus_FieldName'.
ALTER TABLE DROP COLUMN cus_fieldname failed because one or more objects access this column.

Could we fix this error somehow and delete this field in Dynamics, not going directly through the database using SSMS?

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Miguel Lourenco on at

Like (0)

Report

Hi,

Is this field being used in any alternate key or so?

Please check.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello Miguel,

Thank you for your quick response.

Currently looking from the CRM side there aren't any keys created at all. Even if at some point there was some key now it is deleted. The field type is simple Single line of text.

On two more custom entities there is a field with identical schema name cus_FieldName, but since field name also contains entity name as prefix (cus_entity1.cus_FileldName, cus_entity2.cust_FieldName) I suppose that's not causing the error in this particular case.

Was this reply helpful? Yes No
Miguel Lourenco on at

Like (0)

Report
Hi!

If you execute the below, what is the result?

select distinct si.name as IndexName, st.name as TableName from sys.indexes si inner join sys.tables st on st.object_id = si.object_id inner join sys.index_columns sic on si.index_id = sic.index_id and sic.object_id = si.object_id inner join sys.columns sc on sc.object_id = sic.object_id and sc.column_id = sic.column_id inner join MetadataSchema.Entity mse on mse.BaseTableName = st.name inner join MetadataSchema.Attribute msa on msa.EntityId = mse.EntityId and msa.TableColumnName = sc.name --and msa.SolutionId <> mse.SolutionId left outer join MetadataSchema.EntityIndex msei on msei.EntityId = mse.EntityId and msei.name = si.name where msa.SolutionId = 'FD140AAE-4DF4-11DD-BD17-0019B9312238' and si.name like '%_QF_%'

Was this reply helpful? Yes No
Suggested answer

Vijay Tailor 431 on at

Like (0)

Report

Hi Smith,

If you deleting the field using SQL Query in that scenario you first need to delete Index "ndx_QF_cus_FieldName" after that you can easily able to remove the field. (As Per Error mentioned - The index 'ndx_QF_cus_FieldName' is dependent on column 'cus_FieldName'.)

Another Option is - Create New Solution and add the Entity and removed/Delete the Field and Publish.

Hope this will help you

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Vijay

I have tried this second approach. Even after creating completely new solution and adding custom entity and custom field the same error is present.

@Miguel

Currently I don't have access to the database itself, but I can run some queries using XRM Toolbox add-on called SQL 4 CDS. markcarrington.dev/.../

I tried to make some test queries here and I am getting the results. I can see that system entities are also present here and available for querying, but it seems that table with indexes is not present, it must be a limitation.

I installed another XRM Toolbox solution called Futurez Entity Alternate Key. In this solution I can load and delete all indexes.

When I select all the entities and press Load Keys there aren't any.

I created new key with exact name on the field that is problematic and I deleted that key after that, but still it was not possible to delete the field.

Would this above query that you suggested maybe return some other hidden / system indexes?

Was this reply helpful? Yes No
Suggested answer

Vijay Tailor 431 on at

Like (1)

Report

Hi Smith,

Are you deleting the field from "Managed Solution"?

So if we are deleing the field from Managed Solution in that scenario go to Unmanaged solution in DEV env. and Clone the Solution , after that you can removed the feild.

this is also the another solution .

Please check and try.

Hope this will Help you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Vijay

Thanks for suggestion.

For now there is only one development instance. Development is still in progress, various changes to the components are often made, so it is not so unusual that a collision has occurred. The custom entities are created within one separate Unmanaged solution and all the changes are made inside that solution.

Was this reply helpful? Yes No
Suggested answer

Vijay Tailor 431 on at

Like (0)

Report

In that scenario, i would suggest you to try with " Attribute Manager" Using XRM Toolbox.

This tool is help full for -

rename/delete/or change the type of an attribute.

For reference - https://www.xrmtoolbox.com/plugins/DLaB.Xrm.AttributeManager/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, Attribute Manager helped in determining the true cause of the generic SQL error, but since the error is still present it's not possible to delete field.

I tried changing field type from SLT to Whole Number but after creating new field the previous could not be deleted again, same problem.

Was this reply helpful? Yes No