Answered by:
Error when trying to create a new crm 4.0 custom entity

Question
-
Hello, i get an error every time i try creating a new custom entity
Error Message:
An error has occured.
Please contact your system administrator or go for online help. (In short)
View trace log detail and than see this logs:
>Exception when executing non-query: exec p_CascadePrivilegeChanges 'eb65d959-cfff-dc11-9cc2-0017a4770018' Exception: System.Threading.ThreadAbortException: Thread was being aborted.
at SNINativeMethodWrapper.SNIPacketGetConnection(IntPtr packet)
at System.Data.SqlClient.TdsParserStateObject.ProcessSniPacket(IntPtr packet, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
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.ExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteNonQuery(IDbCommand command, ISqlExecutionContext context)
[2009-04-14 21:47:51.7] Process: w3wp |Organization:51d1d4bf-9c35-4089-9eea-57f7119ba0be |Thread: 8 |Category: Platform.Metadata |User: 00000000-0000-0000-0000-000000000000 |Level: Error | EntityService.Create
at EntityService.Create(EntityCreateInfo entityInfo, ExecutionContext context)
at EntityService.Create(EntityCreateInfo entityInfo)
at EntityCreate.Execute(IUser user, ParameterBag paramBag)
at SystemCustomization.CreateEntity(XmlNode data)
at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at LogicalMethodInfo.Invoke(Object target, Object[] values)
at WebServiceHandler.Invoke()
at WebServiceHandler.CoreProcessRequest()
at SyncSessionlessHandler.ProcessRequest(HttpContext context)
at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at ApplicationStepManager.ResumeSteps(Exception error)
at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
>EntityService.Create caught exception: System.Threading.ThreadAbortException: Thread was being aborted.
at SNINativeMethodWrapper.SNIPacketGetConnection(IntPtr packet)
at System.Data.SqlClient.TdsParserStateObject.ProcessSniPacket(IntPtr packet, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
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.ExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteNonQuery(IDbCommand command, ISqlExecutionContext context)
at Microsoft.Crm.ObjectModel.RoleServiceInternal`1.CascadePrivilegeChanges(Guid roleId, ExecutionContext context)
at Microsoft.Crm.ObjectModel.RoleServiceInternal`1.AddPrivilegesInternal(Guid roleId, RolePrivilege[] privileges, Boolean replace, ExecutionContext context)
at Microsoft.Crm.ObjectModel.RoleServiceInternal`1.AddPrivileges(Guid roleId, RolePrivilege[] privileges, ExecutionContext context)
at Microsoft.Crm.Metadata.SecurityHelper.AddPrivilegesToUserRole(RolePrivilege[] privileges, Guid[] roleTemplates, ExecutionContext context)
at Microsoft.Crm.Metadata.SecurityHelper.CreateEntityHelper(IEntityDescription entityDescription, OwnershipTypes ownershipTypeMask, ExecutionContext context)
at Microsoft.Crm.Metadata.EntityService.CreateInternal(EntityCreateInfo entityInfo, ExecutionContext context)
at Microsoft.Crm.Metadata.EntityService.Create(EntityCreateInfo entityInfo, ExecutionContext context)
[2009-04-14 21:47:51.7] Process: w3wp |Organization:00000000-0000-0000-0000-000000000000 |Thread: 8 |Category: Platform.Sql |User: 00000000-0000-0000-0000-000000000000 |Level: Info | CrmDbConnection.InternalExecuteNonQuery
at CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
at CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
at CrmDbConnection.SetTransactionIsolationLevel(IsolationLevel il)
at CrmTransaction.RollbackTransaction(Boolean raiseException)
at SqlExecutionContext.RollbackTransaction()
at EntityService.Create(EntityCreateInfo entityInfo, ExecutionContext context)
at EntityService.Create(EntityCreateInfo entityInfo)
at EntityCreate.Execute(IUser user, ParameterBag paramBag)
at SystemCustomization.CreateEntity(XmlNode data)
at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at LogicalMethodInfo.Invoke(Object target, Object[] values)
at WebServiceHandler.Invoke()
at WebServiceHandler.CoreProcessRequest()
at SyncSessionlessHandler.ProcessRequest(HttpContext context)
at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at ApplicationStepManager.ResumeSteps(Exception error)
at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
>SET TRANSACTION ISOLATION LEVEL READ COMMITTED
[2009-04-14 21:47:51.8] Process: w3wp |Organization:51d1d4bf-9c35-4089-9eea-57f7119ba0be |Thread: 8 |Category: Platform |User: 00000000-0000-0000-0000-000000000000 |Level: Verbose | ExecutionContext.OnErrorRequest
at ExecutionContext.OnErrorRequest()
at EntityService.Create(EntityCreateInfo entityInfo)
at EntityCreate.Execute(IUser user, ParameterBag paramBag)
at SystemCustomization.CreateEntity(XmlNode data)
at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at LogicalMethodInfo.Invoke(Object target, Object[] values)
at WebServiceHandler.Invoke()
at WebServiceHandler.CoreProcessRequest()
at SyncSessionlessHandler.ProcessRequest(HttpContext context)
at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at ApplicationStepManager.ResumeSteps(Exception error)
at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
>ExecutionContext not in use (OnErrorRequest) for organization {51D1D4BF-9C35-4089-9EEA-57F7119BA0BE}
[2009-04-14 21:47:51.8] Process: w3wp |Organization:51d1d4bf-9c35-4089-9eea-57f7119ba0be |Thread: 8 |Category: Application |User: 00000000-0000-0000-0000-000000000000 |Level: Info | AppWebService.CreateSoapException
at AppWebService.CreateSoapException(Exception innerException)
at SystemCustomization.CreateEntity(XmlNode data)
at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at LogicalMethodInfo.Invoke(Object target, Object[] values)
at WebServiceHandler.Invoke()
at WebServiceHandler.CoreProcessRequest()
at SyncSessionlessHandler.ProcessRequest(HttpContext context)
at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at ApplicationStepManager.ResumeSteps(Exception error)
at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
>Exception in Application WebServices. See the following trace error for more information.
[2009-04-14 21:47:51.9] Process: w3wp |Organization:51d1d4bf-9c35-4089-9eea-57f7119ba0be |Thread: 8 |Category: Application |User: 00000000-0000-0000-0000-000000000000 |Level: Error | ErrorInformation.LogError
at ErrorInformation.LogError()
at ErrorInformation..ctor(Exception exception, Uri requestUrl, Boolean logError)
at ErrorInformation..ctor(Exception exception, Uri requestUrl)
at AppWebService.CreateSoapException(Exception innerException)
at SystemCustomization.CreateEntity(XmlNode data)
at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at LogicalMethodInfo.Invoke(Object target, Object[] values)
at WebServiceHandler.Invoke()
at WebServiceHandler.CoreProcessRequest()
at SyncSessionlessHandler.ProcessRequest(HttpContext context)
at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
at ApplicationStepManager.ResumeSteps(Exception error)
at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
>MSCRM Error Report:
--------------------------------------------------------------------------------------------------------
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://crmserver/XXX/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)
where
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'name = 'System Customizer'
and select count(*) count_rows from RolePrivileges
count_rows=3808777
and
OleDbTimeout = 8000 (decimal)
ExtendedTimeout = 1000000 (decimal)Wednesday, April 15, 2009 11:29 AM
Answers
-
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 -
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 1:40 PM
All replies
-
did you load the crmdiagtool?
Try creating the entity as system administrator priviliges
Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1Wednesday, April 15, 2009 12:16 PM -
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 -
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 rollbackWednesday, April 15, 2009 1:35 PM -
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 1:40 PM -
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 -
Did you get this issue resolved. I am having similar problem.
Please let me know if you find any solution to it.
Many ThanksMonday, April 27, 2009 10:40 AM -
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 -
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 secondsAfter 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 statementexec 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 -
I would open a ticket with Microsoft Support. They will be able to look at your environment and provide a solution.
Best Regards, DonnaTuesday, April 28, 2009 3:30 PM -
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.netWednesday, May 6, 2009 3:52 PM