none
Problem converting campaign response from lead to contact, account and opportunity

    Question

  • Hello,

    I'm experiencing an error when converting a campaign response from a lead to a new account, contact and opportunity. This happens on the final step before the response is converted. I'm not sure what the reason is, but it seems to be related to whether or not there is an e-mail in the lead.

    So, my setup:
    1. Onpremise
    2. No customizations, meaning there are no javascripts in the solution
    3. One plugin on create message of Account --> creates URL of the account, not a lot of code -> pasted the code in at the end.
    4. The error is a genereic SQL error

    I've used tracing to find out what happens:

    Category: Exception
    >Crm Exception: Message: Generic SQL error., ErrorCode: -2147204784, InnerException: Microsoft.Crm.CrmException: Generic SQL error. ---> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.

    Category: Exception
    >Crm Exception: Message: Generic SQL error., ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.

    Category: Exception
    >Exception when executing non-query: insert into [EmailSearchBase]([EmailSearchId], [ParentObjectId], [ParentObjectTypeCode], [EmailAddress], [EmailColumnNumber]) values ('da474cbb-9d91-e011-920b-00155dfe3502', 'd8474cbb-9d91-e011-920b-00155dfe3502', 1, 'someonel2@example.com', 35) Exception: System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.
    The statement has been terminated.

    Category: Platform.SQL
    >System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #2E8EA82A: System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.
    >The statement has been terminated.

    Category: Application
    >MSCRM Error Report:
    --------------------------------------------------------------------------------------------------------
    Error: Generic SQL error.
    Error Number: 0x80044150
    Error Message: Generic SQL error.
    Error Details: Generic SQL error.
    Source File: Not available
    Line Number: Not available

    I've also downloaded the error log when presented:

    Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail:
    <OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
      <ErrorCode>-2147204784</ErrorCode>
      <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      <Message>Generic SQL error.</Message>
      <Timestamp>2011-06-08T07:05:47.2318275Z</Timestamp>
      <InnerFault i:nil="true" />
      <TraceText i:nil="true" />
    </OrganizationServiceFault>

    Also tried a SQL Server Profiler on the same error, which gives me three lines with error messages: (I've used the profiler that comes with SQL 2005)
    Exception: - Error: 2601, Severity: 14, State: 1
    User Error Message: Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.
    User Error Message: The statement has been terminated


    So what I've tried - remember the campaign response is from an existing customer (lead) and the e-mail is set in the lead.
    With plugin activated:
    - convert CR with e-mail to contact and opportunity -> OK!
    - convert CR with e-mail to contact, opportunity and account -> Error
    - convert CR without e-mail to contact, opportunity and account -> OK!

    With plugin deactivated:
    - convert CR with e-mail  to contact opportunt and account -> OK!

    Do you have any idea what may be causing this? From my experiments it seems like there is a problem converting the lead if it has an e-mail assigned to it  and you want to convert to a new account. There might be something with my plugin thats interfering with the converting process, but I can't see what the might be.

     

    Code for plugin. The plugin is synchronous, Post-operation (CRM 2011 only)

    public void Execute(IServiceProvider serviceProvider)
        {
          IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    
          // TODO - If you require tracing, uncomment the following line
           //ITracingService trace = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
    
          Entity entity = null;
          Guid id = Guid.Empty;
          // Check if the InputParameters property bag contains a target
          // of the current operation and that target is of type DynamicEntity.
          if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
          {
            // Obtain the target business entity from the input parmameters.
            entity = (Entity)context.InputParameters["Target"];
    
            // TODO Test for an entity type and message supported by your plug-in.
            if (context.PrimaryEntityName != "account") { return; }
            if (context.MessageName != "Create") { return; }
          }
          else
          {        
            return;
          }
    
          try
          {        
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
    
            //Create URL
            if (!entity.Attributes.Contains("description"))
            {
              
              entity.Attributes["description"] = BuildUrl(context.PrimaryEntityId.ToString());          
              service.Update(entity);
            }
          }
          catch (FaultException<OrganizationServiceFault> ex)
          {
            throw new InvalidPluginExecutionException("An error occurred in the plug-in.", ex);
          }
        }
    
        public string BuildUrl(string accountid)
        {
          return "<a href=\"http://crm/OrgName/sfa/accts/edit.aspx" + "?id={" + accountid + "}\">http://crm/OrgName/sfa/accts/edit.aspx" + "?id={" + accountid + "}</a>";
        }
      }
    

     

    Thursday, June 16, 2011 7:59 AM

Answers

  • What stage is the plugin registered on, and is it synchronous or asynchronous ?

    I think your problem is ultimately due to the fact that you're trying to update all the fields in the entity (because you're doing an Update on the contents of the Target InputParameter), and I expect you're doing this synchronously.

    Here are a few things you could try:

    • Run the plugin asynchronously
    • I'd normally suggest updating the entity by using the pre-event, and modifying the Target InputParameter, and hence avoiding an explicit Update. However, it looks like you're dependent on the entity id, which normally won't exist in the pre-create. You might be able to generate and set the id in the pre-event.
    • Create a new instance of the Entity class, and only set the Id and the field you want to update (description). Pass this instance to the Update method

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Proposed as answer by Faisal Fiaz Thursday, June 23, 2011 10:04 PM
    • Marked as answer by Strimmelpinne Friday, June 24, 2011 6:09 AM
    Thursday, June 23, 2011 5:25 PM
    Moderator

All replies

  • Hi,

    I'm facing the same problem when I create a new Account in Windows Service in CRM 2011. Without E-mail everythig works fine, but if the fields with new Account includes E-mail (Account.Emailaddress1) the insert fails with exactly same errors as in original post.

    I checked the EmailSearchBase table immediately after error occurs but there is no duplicate (Error message was "Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update' ")  for the insert clause which CRM tryes to execute (I can see the insert clause in Trace). So why there is error for duplicate if there is no duplicate? Maybe CRM somehow tryes to insert EmailAddress1 twice and when rollbacking after error, both lines disappiers. Does anybody know somethig about this?

    Sunday, June 19, 2011 11:08 AM
  • Hi,

     

    I solve my problem. It was me again :). I have a plugin for Account and a Create of Account step in it and the plugin fires when I insert an Account from my WebService. I found that when I dsibale that step, everythig works fine. So it is this step which causes the double row into EmailSearchBase table. Now I have to find out a way to handlle this but it is a different story. Just in case Strimmelpinne, check if You have some other plugin which fires on same time.

     

     

    Sunday, June 19, 2011 12:37 PM
  • Glad you found out where your problem was.

    I'm not sure what's causing my problem, as I don't have any more plugins than the one in my first post. The plugin works as it supposed to when creating new accounts using the account form. I don't understand why the EmailSearchBase is touched at all..? And why does the plugin work when creating new accounts using the form and not when converting the campaign reponses. I can also disable the step, and then converting CR works.

    Monday, June 20, 2011 9:34 AM
  • What stage is the plugin registered on, and is it synchronous or asynchronous ?

    I think your problem is ultimately due to the fact that you're trying to update all the fields in the entity (because you're doing an Update on the contents of the Target InputParameter), and I expect you're doing this synchronously.

    Here are a few things you could try:

    • Run the plugin asynchronously
    • I'd normally suggest updating the entity by using the pre-event, and modifying the Target InputParameter, and hence avoiding an explicit Update. However, it looks like you're dependent on the entity id, which normally won't exist in the pre-create. You might be able to generate and set the id in the pre-event.
    • Create a new instance of the Entity class, and only set the Id and the field you want to update (description). Pass this instance to the Update method

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Proposed as answer by Faisal Fiaz Thursday, June 23, 2011 10:04 PM
    • Marked as answer by Strimmelpinne Friday, June 24, 2011 6:09 AM
    Thursday, June 23, 2011 5:25 PM
    Moderator
  • Yes!! Fetched the new entity with only the field and the Id, and did an update on that entity. Works perfect. Thank you!!

    Friday, June 24, 2011 6:09 AM
  • Thank you so much!  You guys have saved my ass!

    I was getting a "Generic SQL Error" when converting leads to accounts.  I eventually traced the problem to a plugin that fires on create of account.  But I still couldn't figure out what to do about it.  I enabled tracing on the server and got the detailed message, same as above "Cannot insert duplicate key row in object 'dbo.EmailSearchBase' with unique index 'ndx_for_forward_update'.".  But even knowing that, I still had no idea what do, I could find no existing value and the plugin worked perfectly in every other situation.  So it really made no sense what was happening.

    It was a long shot, but I searched for that error, and found this thread.  

    My code update looks like this:

    Original code:

    Entity newRecord = (Entity)context.InputParameters["Target"];
    

    Updated to this: 

    Entity target = (Entity)context.InputParameters["Target"];
    Entity newRecord = new Entity();
    newRecord.Id = target.Id;
    newRecord.LogicalName = target.LogicalName;

    It seems like nothing, but that solved the problem.  It would have taken me a LOOOONG time to trace the problem down to that level on my own.  Thank you guys, SO MUCH!




    • Edited by Wedge609 Tuesday, January 31, 2012 12:54 AM
    Tuesday, January 31, 2012 12:48 AM
  • I have the same issue.

    I have run the plugin asynchronously, but the auto number is not refelecting in the entity? any idea to overcome this

    Friday, April 13, 2012 7:58 AM
  • I have tried the below solution provided in this thread and it's working fine

    Entity target = (Entity)context.InputParameters["Target"];
    Entity newRecord = new Entity();
    newRecord.Id = target.Id;
    newRecord.LogicalName = target.LogicalName;

    Friday, April 13, 2012 10:26 AM