MS CRM 2011 Report error (rsProcessingError) – Invalid column name “DeletionStateCode”

In MS CRM 4.0, “soft-delete” method was used, i.e., when a record is deleted in CRM 4.0, the record will not be actually deleted from the database. Instead the record would be internally marked with Is Deleted = true and there will be a CRM Deletion Service which will run an Asynchronous System Job once a day. This would actually pick up the records marked with Is Deleted = true and actually delete them from the database.

‘DeletionStateCode’ has been dropped in MS CRM 2011 and auditing functionality has replaced this one. There are no more soft deletes in CRM; the record is actually deleted from the database right away (hard delete).

Error:

Once we upgrade CRM 4.0 to CRM 2011, few custom reports may not render correctly instead may throw,

Invalid column name ‘DeletionStateCode’

Reason:

The reason for this error is the custom stored procedures and triggers used in the database which might be referring to DeletionStateCode column. This column has been removed in CRM 2011 and hence this error is thrown.

Solution:

Since the ‘DeletionStateCode’ column no longer exists in CRM 2011, any SQL objects that referring it will no longer be valid. Columns like owneriddsc, territoryiddsc’, etc. holds/refers the value of the ‘DeletionStateCode’. If these columns have been used in the Query, Store Procedure or table the below error will occur,

Invalid column name ‘owneriddsc’

Invalid column name ‘territoryiddsc’

Invalid column name ‘defaultpriceleveliddsc’

……

In order to resolve the stated errors, find the SQL objects that referring the DeletionStateCode column and drop the SQL object. i.e., Modify the query by commenting or removing code which uses the lookup fields/columns ‘..dsc’ along with the ‘DeletionStateCode’ column and alter the table. This will drop the SQL objects that referring to these columns and above stated error will get resolved.

Hope this post might be helpful for you!!!