Error when trying to create a new crm 4.0 custom entity

All replies

• 

  

  0

  Sign in to vote

  did you load the crmdiagtool?
  Try creating the entity as system administrator priviliges
  

  ---

  Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1

  Wednesday, April 15, 2009 12:16 PM
• 

  

  0

  Sign in to vote

  Check if you have not deleted the System Customizer role.

  • Marked as answer by Donna EdwardsMVP Thursday, May 14, 2009 11:14 PM

  Wednesday, April 15, 2009 12:38 PM
• 

  

  0

  Sign in to vote

  did you load the crmdiagtool?
  Try creating the entity as system administrator priviliges
  

  ---

  Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1

  
  I'm a system administrator, error occurs after few minutes and then transaction is rollback

  Wednesday, April 15, 2009 1:35 PM
• 

  

  0

  Sign in to vote

  Check if you have not deleted the System Customizer role.

  System Customizer role exists and FilteredRole.roletemplateid=RoleTemplateBase.roletemplateid for System Customizer role

  • Marked as answer by Donna EdwardsMVP Thursday, May 14, 2009 11:14 PM

  Wednesday, April 15, 2009 1:40 PM
• 

  

  0

  Sign in to vote

  I would open a ticket with Microsoft Support.  They will be able to look at your environment and provide a solution. 

  ---

  Best Regards, Donna

  • Proposed as answer by Donna EdwardsMVP Monday, April 27, 2009 1:14 PM
  • Unproposed as answer by Donna EdwardsMVP Thursday, May 14, 2009 11:14 PM

  Tuesday, April 21, 2009 2:15 AM
• 

  

  0

  Sign in to vote

  Did you get this issue resolved. I am having similar problem.
  
  Please let me know if you find any solution to it.
  
  Many Thanks

  Monday, April 27, 2009 10:40 AM
• 

  

  0

  Sign in to vote

  I would open a ticket with Microsoft Support.  They will be able to look at your environment and provide a solution. 

  ---

  Best Regards, Donna

  
  Hi Donna,
  
  Did you manage to find any solution to the above issue.
  
  I am experiencing similar issue please find my trace below.
  
  
  ========================================================================
  Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

  Error Message: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

  Source File: Not available

  Line Number: Not available

  Request URL: http://irfansaeed.net/STAG/AppWebServices/SystemCustomization.asmx

  Stack Trace Info: [InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.]
     at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
     at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
     at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
     at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
     at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
     at Microsoft.Crm.CrmDbConnection.SetTransactionIsolationLevel(IsolationLevel il)
     at Microsoft.Crm.CrmTransaction.RollbackTransaction(Boolean raiseException)
     at Microsoft.Crm.SqlExecutionContext.RollbackTransaction()
     at Microsoft.Crm.Metadata.EntityService.Create(EntityCreateInfo entityInfo, ExecutionContext context)
     at Microsoft.Crm.Metadata.EntityService.Create(EntityCreateInfo entityInfo)
     at Microsoft.Crm.Application.WebServices.SystemCustomization.EntityCreate.Execute(IUser user, ParameterBag paramBag)
     at Microsoft.Crm.Application.WebServices.SystemCustomization.SystemCustomization.CreateEntity(XmlNode data)

   

  Monday, April 27, 2009 10:45 AM
• 

  

  0

  Sign in to vote

  Problem occured at line

  Exception when executing non-query: exec p_CascadePrivilegeChanges 'eb65d959-cfff-dc11-9cc2-0017a4770018' Exception: System.Threading.ThreadAbortException: Thread was being aborted.

   

  select [name] from FilteredRole where roleid='eb65d959-cfff-dc11-9cc2-0017a4770018' 

  result name = 'System Customizer'

  and 

  select  count(*) count_rows from RolePrivileges

  result count_rows=3808777

   

  I'm testing this problem in my test environment. (Don't try this at production environment!!!)

  Before creating new custom entity I run statement

  ALTER     procedure [dbo].[p_CascadePrivilegeChanges](@parentRoleid uniqueidentifier) as
  
  begin
  
  return 
  
  end

  
  Now I can create new entity and it takes couple seconds

  After that I execute next statement

  rollup changes to procedure p_CascadePrivilegeChanges

  ALTER     procedure [dbo].[p_CascadePrivilegeChanges](@parentRoleid uniqueidentifier) as
  
  begin
  
  SET NOCOUNT ON
  
  
  
  declare @parentBizId uniqueidentifier
  
  declare @currParentRoleId uniqueidentifier
  
  declare @prevParentRoleId uniqueidentifier
  
  declare @rowcnt int
  
  select @parentBizId = BusinessUnitId from RoleBase where RoleId = @parentRoleid
  
  create table #Roles(RoleId Uniqueidentifier primary key clustered, ParentRoleId uniqueidentifier)
  
  create index ParentRoleIdx on #Roles(ParentRoleId)
  
  create table #roleprivileges(
  
   roleid uniqueidentifier,
  
   privilegeid uniqueidentifier not null, 
  
   privilegedepthmask int not null
  
   )
  
  create index privilegedepthmaskIdx on #roleprivileges(privilegedepthmask)
  
  create table #parents(parentroleid uniqueidentifier primary key clustered)
  
  insert into #parents(parentroleid) values(@parentRoleid)
  
  SELECT @currParentRoleId = @parentRoleid
  
  while (@currParentRoleId is not null)
  
  begin
  
   insert into #Roles(RoleId, ParentRoleId)
  
   select distinct r.RoleId, r.ParentRoleId
  
   From RoleBase r
  
   where r.BusinessUnitId in (select b.SubBusinessId from BusinessUnitMap b
  
   where b.BusinessId = @parentBizId
  
   and b.SubBusinessId <> @parentBizId)
  
   and r.RoleId <> @parentRoleid
  
   and r.ParentRoleId is not null
  
   and r.ParentRoleId = @currParentRoleId
  
   Select @prevParentRoleId = @currParentRoleId
  
   select @currParentRoleId = null
  
   select top 1 @currParentRoleId = RoleId
  
   from #Roles where RoleId not in (select distinct ParentRoleId from #Roles)
  
   and exists (select top 1 r.ParentRoleId from RoleBase r where r.ParentRoleId = #Roles.RoleId)
  
   if (@prevParentRoleId = @currParentRoleId)
  
   begin
  
    --This is only to break out of the loop. Error Condn.
  
    select @currParentRoleId = null
  
   end
  
  end
  
  delete from RolePrivileges where RoleId in (select RoleId from #Roles)
  
  select @rowcnt = count(*) from #Roles
  
  -- getting the root parent
  
  select top 1 @currParentRoleId = r.ParentRoleId
  
  from #Roles r, RolePrivileges rp where
  
  r.ParentRoleId = rp.RoleId
  
  insert into #roleprivileges(roleid, privilegeid, privilegedepthmask)
  
   select r.RoleId, rp.PrivilegeId, rp.PrivilegeDepthMask
  
   from #Roles r, RolePrivileges rp
  
   where r.ParentRoleId = @currParentRoleId
  
   and rp.RoleId = @currParentRoleId
  
   delete from #Roles where ParentRoleId = @currParentRoleId
  
  -- case when all privileges for the parentrole have been removed
  
  if(@currParentRoleId is not null)
  
  begin
  
  select @rowcnt = count(*) from #Roles
  
  while (@rowcnt > 0)
  
  begin
  
   --By joining the #Roles and #roleprivileges
  
   --we are guaranteed that a child role will be processed
  
   --only after its parent.
  
   select top 1 @currParentRoleId = r.ParentRoleId
  
   from #Roles r, #roleprivileges rp where
  
   r.ParentRoleId = rp.roleid
  
   insert into #roleprivileges(roleid, privilegeid, privilegedepthmask)
  
   select r.RoleId, rp.privilegeid, rp.privilegedepthmask
  
   from #Roles r, #roleprivileges rp
  
   where r.ParentRoleId = @currParentRoleId
  
   and rp.roleid = @currParentRoleId
  
   delete from #Roles where ParentRoleId = @currParentRoleId
  
   -- Since multiple Roles can have the same ParentRoleId we subtract the number of rows deleted from #Roles table from @rowcnt
  
   select @rowcnt = @rowcnt - @@rowcount
  
   insert into #parents(parentroleid) values(@currParentRoleId)
  
  end
  
  end
  
  -- update the privilegedepthmasks
  
   exec p_UpdateRolePrivilegeMaskInTempTable
  
  -- Copy privileges from temptable in one statement
  
  insert into RolePrivileges(RoleId, PrivilegeId, PrivilegeDepthMask)
  
   select roleid, privilegeid, privilegedepthmask from #roleprivileges
  
   -- Reinitialize subscriptions for users with changing roles
  
  update Subscription Set ReInitialize = 1 
  
  where SystemUserId in 
  
   (select SystemUserId from SystemUserRoles where RoleId in (select parentroleid from #parents))
  
  drop table #parents
  
  drop table #roleprivileges
  
  drop table #Roles
  
  
  
  end

  
  and next execute statement

  exec dbo.p_CascadePrivilegeChanges @parentRoleid='775CD959-CFFF-DC11-9CC2-0017A4770018' --System Administrator (5 minutes)
  
  exec dbo.p_CascadePrivilegeChanges @parentRoleid='EB65D959-CFFF-DC11-9CC2-0017A4770018' --System Customizer (5 minutes)

  It works!!! Don't try this at production environement because it's not supported

   This is not solution. Eny idea how to resolve this problem???

  Monday, April 27, 2009 12:51 PM
• 

  

  0

  Sign in to vote

  I would open a ticket with Microsoft Support.  They will be able to look at your environment and provide a solution. 

  ---

  Best Regards, Donna

  Tuesday, April 28, 2009 3:30 PM
• 

  

  0

  Sign in to vote

  I think finally I managed to fix this issue.
  
  Please read my blog for more details from the link below:
  http://www.irfansaeed.net/2009/05/error-when-trying-to-create-new-crm-40.html
  
  

  ---

  Irfan Saeed http://www.irfansaeed.net

  Wednesday, May 6, 2009 3:52 PM