Cannot insert duplicate key.

Answered Cannot insert duplicate key.

  • Wednesday, June 27, 2012 9:25 AM
     
     

    Hi,

    I am creating service activities via C#.

    Scenario: I am retrieving a service activity and making its copies at different scheduled times, but I am getting duplicate key error while creating new activity. and there are no other details for the error. 

    Please Help!

    Thanks.


    Haris Adil e-Bizsoft

All Replies

  • Wednesday, June 27, 2012 10:57 AM
     
     Proposed Answer

    Hi,

    Remove the attribute which contains the service activity id from the new activity (e.g. entity.Attributes.Remove("serviceappointmentid") )!

    Greets,

    Andreas


    Andreas Buchinger
    Microsoft Dynamics Certified Technology Specialist
    MCPD: SharePoint Developer 2010

  • Wednesday, June 27, 2012 11:14 AM
     
     

    It didn't help. Still getting the same error.

    And its "activityid" instead of "serviceappointmentid" i.e. Primary key


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 11:20 AM
    Moderator
     
     

    Hello,

    Can you please provide your code?


    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 11:33 AM
     
      Has Code
    serviceProxy = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null);
            orgService = (IOrganizationService)serviceProxy;
            serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
    
            string Workshop = Request.QueryString["W"];
            Guid WorkshopID = new Guid(Workshop);
            string service = Request.QueryString["S"];
            Guid serviceActivityID = new Guid(service);
            Entity ServiceActivity = new Entity("serviceappointment");
            ColumnSet serviceactivitycolumns = new ColumnSet("subject", "siteid", "serviceid", "new_city", "regardingobjectid", "customers", "resources",
                                                             "scheduledstart", "scheduledend", "new_locationtype", "activityid");
            ServiceActivity = orgService.Retrieve("serviceappointment", serviceActivityID, serviceactivitycolumns);
            //Declaring objects
            EntityCollection Resources = new EntityCollection();
            EntityCollection Customers = new EntityCollection();
            Guid SiteID = new Guid();
            Guid ServiceID = new Guid();
            Guid RegardingID = new Guid();
            string regardingEntity = "";
            int City = 1;
            DateTime StartDate = DateTime.Today;
            DateTime EndDate = DateTime.Today;
            EntityReference sitelookup = new EntityReference();
            EntityReference servicelookup = new EntityReference();
            EntityReference regardinglookup = new EntityReference();
    
            //Assigning Values
            
            if (ServiceActivity.Attributes.Contains("resources"))
            {
                Resources = ServiceActivity.GetAttributeValue<EntityCollection>("resources");
            }
            if (ServiceActivity.Attributes.Contains("customers"))
            {
                Customers = ServiceActivity.GetAttributeValue<EntityCollection>("customers");
            }
            String Subject = ServiceActivity.Attributes["subject"].ToString();
            if (ServiceActivity.Attributes.Contains("siteid"))
            {
                SiteID = ((EntityReference)ServiceActivity.Attributes["siteid"]).Id;
                sitelookup = new EntityReference
                {
                    Id = SiteID,
                    LogicalName = "site"
                };
            }
            if (ServiceActivity.Attributes.Contains("serviceid"))
            {
                ServiceID = ((EntityReference)ServiceActivity.Attributes["serviceid"]).Id;
                servicelookup = new EntityReference
                {
                    Id = ServiceID,
                    LogicalName = "service"
                };
            }
            if (ServiceActivity.Attributes.Contains("new_city"))
            {
                City = ((OptionSetValue)ServiceActivity.Attributes["new_city"]).Value;
            }
            if (ServiceActivity.Attributes.Contains("scheduledstart"))
            {
                StartDate = DateTime.Parse(ServiceActivity["scheduledstart"].ToString());
            }
            if (ServiceActivity.Attributes.Contains("scheduledend"))
            {
                EndDate = DateTime.Parse(ServiceActivity["scheduledend"].ToString());
            }
            bool serviceType = Convert.ToBoolean(ServiceActivity.Attributes["new_locationtype"].ToString());
            if (ServiceActivity.Attributes.Contains("regardingobjectid"))
            {
                RegardingID = ((EntityReference)ServiceActivity.Attributes["regardingobjectid"]).Id;
                regardingEntity = ((EntityReference)ServiceActivity.Attributes["regardingobjectid"]).LogicalName;
                //Response.Write(regardingEntity);
                regardinglookup = new EntityReference
                {
                    Id = RegardingID,
                    LogicalName = regardingEntity
                };
            }
            ServiceActivity.Attributes.Remove("activityid");
            ServiceActivity["activityid"] = null;
            Entity new_workshop = new Entity("new_workshop");
            ColumnSet WScolumns = new ColumnSet("new_startdate", "new_enddate");
            new_workshop = orgService.Retrieve("new_workshop", WorkshopID , WScolumns);
            DateTime WSstartdate = DateTime.Parse(new_workshop["new_startdate"].ToString());
            DateTime WSenddate = DateTime.Parse(new_workshop["new_enddate"].ToString());
            int  count = (WSenddate-WSstartdate).Days;
            string sub = "";
            //Entity NewServiceActivity = new Entity("serviceappointment");
            //Guid NewID = new Guid();
            for (int x = 1; x <=count; x++)
            {
                sub = Subject + "Day " + (x+1).ToString();
                ServiceActivity["subject"] = sub;
                ServiceActivity["resources"] = Resources;
                ServiceActivity["customers"] = Customers;
                ServiceActivity["siteid"] = (EntityReference)sitelookup;
                ServiceActivity["serviceid"] = (EntityReference)servicelookup;
                ServiceActivity["new_city"] = new OptionSetValue(City);
                ServiceActivity["regardingobjectid"] = (EntityReference)regardinglookup;
                ServiceActivity["new_locationtype"] = serviceType;
                ServiceActivity["scheduledstart"] = StartDate.AddDays(x);
                ServiceActivity["scheduledend"] = EndDate.AddDays(x);
                ServiceActivity.Attributes.Remove("activityid");
                Response.Write(ServiceActivity["scheduledstart"].ToString() + "<br/>" + ServiceActivity["scheduledend"].ToString() + "<br/>");
                orgService.Create(NewServiceActivity);


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 11:38 AM
     
     

    Hi,

    I guess there will be a problem with resources i think but first I want  help with this error. I will figure that out later.

    Thanks


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 11:44 AM
    Moderator
     
     

    I believe that you have 2 problem with fields - customers and resources.

    Both fields are EntityReferenceCollection. You should not just copy values from existing serviceappointment you will have to instantiate those collections from scratch.


    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 11:48 AM
     
     
    OK. I will work on that. But even if I don't set these two attribute values while creating new record(left them null), I still get the same error. 

    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 11:52 AM
    Moderator
     
     
    OK. I will work on that. But even if I don't set these two attribute values while creating new record(left them null), I still get the same error. 

    Haris Adil e-Bizsoft

    In this case you should turn on tracing, restart iis and reproduce error one more time. I believe you will have logs inside which will contain description of error.

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 11:56 AM
     
      Has Code

    Hi,

    Try to remove this line

    ServiceActivity["activityid"] = null;

    Only remove the attribute from the collection on the entity!

    Greets,

    Andreas


    Andreas Buchinger
    Microsoft Dynamics Certified Technology Specialist
    MCPD: SharePoint Developer 2010

  • Wednesday, June 27, 2012 12:23 PM
     
     

    Hi,

    Yeah already removed it,.

    I enabled tracing but no luck. My website is still not on IIS. Should I put it there?

    Thanks


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 12:33 PM
    Moderator
     
     
    I enabled tracing but no luck. My website is still not on IIS. Should I put it there?
    I am sorry but how could it be?

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 12:49 PM
     
     

    I knew you would say that :)

    actually i am calling an website on an iframe to do this task. I was talking about that website.


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 12:56 PM
    Moderator
     
     

    I knew you would say that :)

    actually i am calling an website on an iframe to do this task. I was talking about that website.


    Haris Adil e-Bizsoft

    Appeared duplicate key error should appear in CRM Trace. And I believe that CRM is hosted with IIS.

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 1:04 PM
     
     

    Yes it is hosted with IIS. 

    C:\Program Files\Microsoft Dynamics CRM\Trace. Here the Trace log is empty, there is no error. I just activated trace with Powershell and restarted IIS.


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 1:14 PM
    Moderator
     
     
    Have you reproduced an error after you've activated traces and restarted IIS?

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 1:30 PM
     
     

    OK. I added the registries manually to enable trace now. And the error is being traced but w3wp log file size just keeps on increasing which is weird.

    What to do next? 


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 1:34 PM
    Moderator
     
     
    Could you reproduce your error, open trace file, find this error inside trace and provide it here?

    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 1:38 PM
     
     
    >Crm Exception: Message: Cannot insert duplicate key., ErrorCode: -2147220937
    [2012-06-27 18:23:56.843] Process: w3wp |Organization:9cea04a8-bb78-e111-9241-001438bf550d |Thread:   21 |Category: Platform.Sdk |User: fe34fa28-857a-e111-b2e5-001438bf550d |Level: Error | VersionedPluginProxyStepBase.Execute
    >Web Service Plug-in failed in SdkMessageProcessingStepId: {CC3B8615-ECD8-DB11-B397-0019B9204DA9}; EntityName: uom; Stage: 30; MessageName: Create; AssemblyName: Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35; ClassName: Microsoft.Crm.Extensibility.InternalOperationPlugin; Exception: Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
       at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
       at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider)
       at Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)
       at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
    Inner Exception: Microsoft.Crm.BusinessEntities.CrmDuplicateRecordException: Cannot insert duplicate key.
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DbCreate(IBusinessEntity entity, ExecutionContext context)
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DoCreate(IBusinessEntity entity, ExecutionContext context)


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 1:50 PM
     
     

    Hi,

    Do you have other plugins or workflow around this entity? Do you have duplicate detection rules on your crm system?

    Greets,

    Andreas


    Andreas Buchinger
    Microsoft Dynamics Certified Technology Specialist
    MCPD: SharePoint Developer 2010

  • Wednesday, June 27, 2012 1:55 PM
     
     
    Yes I have a plugin and workflows around the entity that is triggering this event. And yes there are duplicate detection rule on our system.

    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 1:58 PM
     
     

    Is it possible that one of the duplicate detection rules is triggered by your logic? Or could it be that the error is produced from another logic - the reason for this question is that in the first error message the entityname ist "uom" and not "serviceappointment"...

    Greets,

    Andreas


    Andreas Buchinger
    Microsoft Dynamics Certified Technology Specialist
    MCPD: SharePoint Developer 2010

  • Wednesday, June 27, 2012 2:11 PM
     
     

    OK. This may be the correct trace then

    >Crm Exception: Message: Cannot insert duplicate key., ErrorCode: -2147220937
    [2012-06-27 18:26:42.328] Process: w3wp |Organization:a926c73b-2946-47a6-a2de-738f84977ed0 |Thread:   29 |Category: Platform.Sdk |User: 85af7fa4-904d-e111-90cc-001438bf550d |Level: Error | VersionedPluginProxyStepBase.Execute
    >Web Service Plug-in failed in SdkMessageProcessingStepId: {C43B8615-ECD8-DB11-B397-0019B9204DA9}; EntityName: serviceappointment; Stage: 30; MessageName: Create; AssemblyName: Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35; ClassName: Microsoft.Crm.Extensibility.InternalOperationPlugin; Exception: Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
       at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
       at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider)
       at Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)
       at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
    Inner Exception: Microsoft.Crm.BusinessEntities.CrmDuplicateRecordException: Cannot insert duplicate key.
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DbCreate(IBusinessEntity entity, ExecutionContext context)
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DoCreate(IBusinessEntity entity, ExecutionContext context)

    OR

    [2012-06-27 18:26:42.328] Process: w3wp |Organization:a926c73b-2946-47a6-a2de-738f84977ed0 |Thread:   29 |Category: Platform.Sql |User: 85af7fa4-904d-e111-90cc-001438bf550d |Level: Error | BusinessProcessObject.ExecuteNonQuery
    >Exception when executing non-query: insert into [ActivityPointerBase]([SvcApptIsAllDayEvent], [ActivityId], [CreatedBy], [ModifiedOnBehalfBy], [OwningBusinessUnit], [CreatedOn], [RegardingObjectTypeCode], [StateCode], [SiteId], [StatusCode], [Subject], [ScheduledStart], [OwnerId], [ScheduledEnd], [RegardingObjectId], [IsWorkflowCreated], [ActivityTypeCode], [PriorityCode], [IsBilled], [OwnerIdType], [ScheduledDurationMinutes], [IsRegularActivity], [ModifiedBy], [ServiceId], [ModifiedOn], [TimeZoneRuleVersionNumber], [RegardingObjectIdName]) values (0, '9ed8e9dc-c8be-e111-b2b0-001438bf550d', '85af7fa4-904d-e111-90cc-001438bf550d', NULL, 'a29b7fa4-904d-e111-90cc-001438bf550d', '06/27/2012 13:26:42', 10014, 0, 'ac01d199-a9be-e111-b2b0-001438bf550d', 1, 'RBCPDay 2', '06/11/2012 07:00:00', '85af7fa4-904d-e111-90cc-001438bf550d', '06/11/2012 11:00:00', 'c5649a74-1eb6-e111-a1f8-001438bf550d', 0, 4214, 1, 0, 8, 240, 1, '85af7fa4-904d-e111-90cc-001438bf550d', '7b95ffd7-4498-e111-a737-001438bf550d', '06/27/2012 13:26:42', 4, 'RBCP Deleivery Batch-I -6/24/2012-Karachi');insert into [ServiceAppointmentBase]([ActivityId], [new_City], [new_SOGenerated], [new_LocationType]) values ('9ed8e9dc-c8be-e111-b2b0-001438bf550d', 1, 0, 0) Exception: System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'ndx_PrimaryKey_ActivityPointer'. Cannot insert duplicate key in object 'dbo.ActivityPointerBase'.
    Violation of PRIMARY KEY constraint 'PK_ServiceAppointmentBase'. Cannot insert duplicate key in object 'dbo.ServiceAppointmentBase'.
    The statement has been terminated.
    The statement has been terminated.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       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.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
       at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command, Boolean capturePerfTrace)
       at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate, Boolean capturePerfTrace)


    Haris Adil e-Bizsoft

  • Wednesday, June 27, 2012 2:21 PM
     
     

    Have you posted the complete source code - obviously the activityid is in the attribute list when the create-method is called!?


    Andreas Buchinger
    Microsoft Dynamics Certified Technology Specialist
    MCPD: SharePoint Developer 2010

  • Wednesday, June 27, 2012 2:24 PM
    Moderator
     
     Proposed Answer Has Code

    Try to use following code:

    ServiceActivity.Id = Guid.Empty;

    It seems that you haven't cleared Id of your record.



    Microsoft CRM Freelancer

    My blog (english)
    Мой блог (русскоязычный)
    Follow Andriy on Twitter

  • Wednesday, June 27, 2012 2:42 PM
     
     

    I was clearing it by using entity .attributes.remove as andreas suggested. Anyway i replaced it with this line of code but it didn't help. 

    It says (organization service fault)Cannot create duplicate key. Do I have to add anything in the code. This is the correct trace 

    ">Exception when executing non-query: insert into [ActivityPointerBase]([SvcApptIsAllDayEvent], [ActivityId], [CreatedBy], [ModifiedOnBehalfBy], [OwningBusinessUnit], [CreatedOn], [RegardingObjectTypeCode], [StateCode], [SiteId], [StatusCode], [Subject], [ScheduledStart], [OwnerId], [ScheduledEnd], [RegardingObjectId], [IsWorkflowCreated], [ActivityTypeCode], [PriorityCode], [IsBilled], [OwnerIdType], [ScheduledDurationMinutes], [IsRegularActivity], [ModifiedBy], [ServiceId], [ModifiedOn], [TimeZoneRuleVersionNumber], [RegardingObjectIdName]) values (0, '9ed8e9dc-c8be-e111-b2b0-001438bf550d', '85af7fa4-904d-e111-90cc-001438bf550d', NULL, 'a29b7fa4-904d-e111-90cc-001438bf550d', '06/27/2012 13:26:42', 10014, 0, 'ac01d199-a9be-e111-b2b0-001438bf550d', 1, 'RBCPDay 2', '06/11/2012 07:00:00', '85af7fa4-904d-e111-90cc-001438bf550d', '06/11/2012 11:00:00', 'c5649a74-1eb6-e111-a1f8-001438bf550d', 0, 4214, 1, 0, 8, 240, 1, '85af7fa4-904d-e111-90cc-001438bf550d', '7b95ffd7-4498-e111-a737-001438bf550d', '06/27/2012 13:26:42', 4, 'RBCP Deleivery Batch-I -6/24/2012-Karachi');insert into [ServiceAppointmentBase]([ActivityId], [new_City], [new_SOGenerated], [new_LocationType]) values ('9ed8e9dc-c8be-e111-b2b0-001438bf550d', 1, 0, 0) Exception: System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'ndx_PrimaryKey_ActivityPointer'. Cannot insert duplicate key in object 'dbo.ActivityPointerBase'.

    Violation of PRIMARY KEY constraint 'PK_ServiceAppointmentBase'. Cannot insert duplicate key in object 'dbo.ServiceAppointmentBase'."

    Haris Adil e-Bizsoft

  • Thursday, June 28, 2012 11:22 AM
     
     Answered

    Hi,

    Our CRM was not updated. After installing Update Rollups 6 and 8, I am finally able to create a new service appointment.

    And as I said the resources and customers field will cause the problem, they are! and the same error is generated.

    Anyways my first problem is solved. Thankyou Andrii and Andreas for your co-operation.

    Regards.


    Haris Adil e-Bizsoft

    • Marked As Answer by Harispk Thursday, June 28, 2012 11:22 AM
    •  
  • Thursday, June 28, 2012 12:57 PM
     
     Answered