FOLLOWING THE DIRECTIONS BELOW AND REPLACING THE USER DEFINED FUNCTION YOU ARE COMPLETING AN UNSUPPORTED MODIFICATION.
This was a very difficult problem to find... I had two deployments (one development) that would not allow any user to change ownership on any entity. After using the CRM Trace tool and spending a lot of time finding the error message I was able to find this article that provided the proper code to replace a the user defined function fn_CollectForCascadeAssign with the code, provided below and it worked.
REASON:
According the the Neil Benson, CRM Addict and Jim Glass MSFT Microsoft on the Microsoft Dynamics Forums CRM the issue was introduced by the regression error in Update Rollup 7.
SUPPORTED FIX:
The Supported Method is the reinstall Update Rollup 8 or 9 after creating or importing a new organization. Update Rollup 10 Fixes the issue.
SYMPTOMS
Messages in Trace File:
MessageProcessor fail to process message 'Assign' for 'account' - or any other entity
CRM Trace File Message: Crm Exception: Message: , ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException: Invalid column name 'q'.
Invalid column name 's'.
Invalid column name 'y'.
UNSUPPORTED FIX:
Replaced User Defined Function with the code below
USE [database_name]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[fn_CollectForCascadeAssign]
(
@root_id uniqueidentifier,
@root_otc int,
@isoffline int,
@old_owner uniqueidentifier
)
returns @t table
(
o uniqueidentifier,
t int,
p int default 0,
u uniqueidentifier,
q uniqueidentifier,
s int,
y bit default 0
)
as
begin
insert into @t values(@root_id,@root_otc,0,@old_owner,N'00000000-0000-0000-0000-000000000000',0,0)
if(exists(select * from @t where t=9100))begin insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0
while(@@rowcount <> 0)if(exists(select * from @t where t=9100))insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0 and o.ReportId not in(select o from @t where o=o.ReportId and t=9100) end
if(exists(select * from @t where t=4400))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4402,0,o.OwningUser,c.o,c.t,1 from CampaignActivity o,@t c where o.RegardingObjectId=c.o and c.t=4400 and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(4400,4406)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4401,0,o.OwningUser,c.o,c.t,1 from CampaignResponse o,@t c where o.RegardingObjectId=c.o and c.t in(4400,4406) and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0
while(@@rowcount <> 0)if(exists(select * from @t where t=1))insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0 and o.AccountId not in(select o from @t where o=o.AccountId and t=1) end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0
while(@@rowcount <> 0)if(exists(select * from @t where t=2))insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0 and o.ContactId not in(select o from @t where o=o.ContactId and t=2) end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1088))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4209,0,o.OwningUser,c.o,c.t,1 from OrderClose o,@t c where o.SalesOrderId=c.o and c.t=1088 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1084))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4211,0,o.OwningUser,c.o,c.t,1 from QuoteClose o,@t c where o.QuoteId=c.o and c.t=1084 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=112))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4206,0,o.OwningUser,c.o,c.t,1 from IncidentResolution o,@t c where o.IncidentId=c.o and c.t=112 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.CustomerOpportunityRoleId,4503,0,o.OwningUser,c.o,c.t,1 from CustomerOpportunityRole o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(1088,2,3,1010,1090,4,1,112,1084)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4214,0,o.OwningUser,c.o,c.t,1 from ServiceAppointment o,@t c where o.RegardingObjectId=c.o and c.t in(1088,2,3,1010,1090,4,1,112,1084) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(1084,112,1,4400,4,1090,1010,3,2,1088)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4212,0,o.OwningUser,c.o,c.t,1 from Task o,@t c where o.RegardingObjectId=c.o and c.t in(1084,112,1,4400,4,1090,1010,3,2,1088) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(1088,2,3,4406,1010,1090,4402,4,1,1084,112)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4210,0,o.OwningUser,c.o,c.t,1 from PhoneCall o,@t c where o.RegardingObjectId=c.o and c.t in(1088,2,3,4406,1010,1090,4402,4,1,1084,112) and o.DeletionStateCode=0 end
if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4208,0,o.OwningUser,c.o,c.t,1 from OpportunityClose o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(112,1084,1,4402,4,1010,4406,1090,3,1088,2)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4207,0,o.OwningUser,c.o,c.t,1 from Letter o,@t c where o.RegardingObjectId=c.o and c.t in(112,1084,1,4402,4,1010,4406,1090,3,1088,2) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(1088,2,3,4406,1090,1010,1,112,1084,4,4402)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4204,0,o.OwningUser,c.o,c.t,1 from Fax o,@t c where o.RegardingObjectId=c.o and c.t in(1088,2,3,4406,1090,1010,1,112,1084,4,4402) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(4402,4,1084,112,1,4700,1090,4406,1010,3,1088,2)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4202,0,o.OwningUser,c.o,c.t,1 from Email o,@t c where o.RegardingObjectId=c.o and c.t in(4402,4,1084,112,1,4700,1090,4406,1010,3,1088,2) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(2,1088,3,4406,1010,1090,112,1,1084,4402,4)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4201,0,o.OwningUser,c.o,c.t,1 from Appointment o,@t c where o.RegardingObjectId=c.o and c.t in(2,1088,3,4406,1010,1090,112,1,1084,4402,4) and o.DeletionStateCode=0 end
if(exists(select * from @t where t in(4400,4414,4211,4212,4703,4401,4207,4209,4201,1088,4208,2,1090,4204,4202,4206,1010,4300,3,1,1084,112,4402,4214,4,4210)))begin insert into @t(o,t,p,u,q,s,y) select o.AnnotationId,5,0,o.OwningUser,c.o,c.t,1 from Annotation o,@t c where o.ObjectId=c.o and c.t in(4400,4414,4211,4212,4703,4401,4207,4209,4201,1088,4208,2,1090,4204,4202,4206,1010,4300,3,1,1084,112,4402,4214,4,4210) and o.DeletionStateCode=0 end
return
end
/
No comments:
Post a Comment