locked
Plugin throwing Generic SQL Error RRS feed

  • Question

  • I'm writing a plugin for CRM Online (2013) which will write a code to a field to allow some de-duplication. The plugin is running in async (I have tried sync/post and get the same error). The step is Update of Contact.

    The error seems to be a bit intermittent. When the record is updated directly the plugin runs fine. When I run a workflow against a number of contacts it fails for some records and works for others, but there seems to be no consistency over which records it will fail against.

    Here is my code and it is failing on "service.update(contact)".

    string email = "";
    string code = "";
    ColumnSet conCols = new ColumnSet("lastname", "emailaddress1", "c20_dedupecode", "c20_updatededupecode");
    Entity contact = service.Retrieve("contact", targetEntity.Id, conCols);
    if (!(bool)contact["c20_updatededupecode"] && context.Depth != 1)
    return;// throw new Exception("1");
    
    tracer.Trace("depth: " + context.Depth.ToString());
    tracer.Trace("update code? " + contact["c20_updatededupecode"].ToString());
    
    code = targetEntity.Contains("lastname") ? targetEntity["lastname"].ToString() : "";
    tracer.Trace("build code");
    if (code == "" && contact.Contains("lastname"))
    code = contact["lastname"].ToString();
    if (code == "")
    throw new Exception("Contact must contain last name");
    
    email = targetEntity.Contains("emailaddress1") ? targetEntity["emailaddress1"].ToString() : "";
    if (email == "" && contact.Contains("emailaddress1"))
    email = contact["emailaddress1"].ToString();
    code += email;
    
    code = Regex.Replace(code, "[^a-zA-Z0-9]", "");
    
    contact["c20_dedupecode"] = code;
    contact["c20_updatededupecode"] = false;
    tracer.Trace("update contact");
    service.Update(contact);
    tracer.Trace("contact updated");

    Full error trace:

    Plugin Trace:
    
    [ExportaDedupeCode: ExportaDedupeCode.ExportaDedupeCodeClass]
    [6e8fcb4c-b977-e311-8462-6c3be5bedcf4: ExportaDedupeCode.ExportaDedupeCodeClass: Update of contact]
    
    ENTER: ExportaDedupeCode.ExportaDedupeCodeClass.Execute
    0
    update
    contact
    depth: 2
    update code? True
    build code
    update contact
    
    
    
    Error Message:
    
    Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: An error occurred in the ExportaDedupeCode.ExportaDedupeCodeClass plug-in:Generic SQL error.
    
    Server stack trace: 
       at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
    
    Exception rethrown at [0]: 
       at Microsoft.Crm.Sandbox.SandboxOrganizationService.Execute(String operation, Byte[] serializedRequest)
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
    
    Exception rethrown at [1]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.Crm.Sandbox.ISandboxOrganizationService.Execute(String operation, Byte[] serializedRequest)
       at Microsoft.Crm.Sandbox.SandboxOrganizationServiceWrapper.ExecuteInternal(OrganizationRequest request)
       at ExportaDedupeCode.ExportaDedupeCodeClass.Execute(IServiceProvider serviceProvider)Detail: 
    <OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
      <ErrorCode>-2147220891</ErrorCode>
      <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
        <KeyValuePairOfstringanyType>
          <d2p1:key>OperationStatus</d2p1:key>
          <d2p1:value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:int">0</d2p1:value>
        </KeyValuePairOfstringanyType>
        <KeyValuePairOfstringanyType>
          <d2p1:key>SubErrorCode</d2p1:key>
          <d2p1:value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:int">-2146233088</d2p1:value>
        </KeyValuePairOfstringanyType>
      </ErrorDetails>
      <Message>An error occurred in the ExportaDedupeCode.ExportaDedupeCodeClass plug-in:Generic SQL error.
    
    Server stack trace: 
       at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc&amp; rpc)
       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
    
    Exception rethrown at [0]: 
       at Microsoft.Crm.Sandbox.SandboxOrganizationService.Execute(String operation, Byte[] serializedRequest)
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]&amp; outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
    
    Exception rethrown at [1]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&amp; msgData, Int32 type)
       at Microsoft.Crm.Sandbox.ISandboxOrganizationService.Execute(String operation, Byte[] serializedRequest)
       at Microsoft.Crm.Sandbox.SandboxOrganizationServiceWrapper.ExecuteInternal(OrganizationRequest request)
       at ExportaDedupeCode.ExportaDedupeCodeClass.Execute(IServiceProvider serviceProvider)</Message>
      <Timestamp>2014-01-08T09:41:13.8880523Z</Timestamp>
      <InnerFault i:nil="true" />
      <TraceText>
    [ExportaDedupeCode: ExportaDedupeCode.ExportaDedupeCodeClass]
    [6e8fcb4c-b977-e311-8462-6c3be5bedcf4: ExportaDedupeCode.ExportaDedupeCodeClass: Update of contact]
    
    ENTER: ExportaDedupeCode.ExportaDedupeCodeClass.Execute
    0
    update
    contact
    depth: 2
    update code? True
    build code
    update contact
    
    </TraceText>
    </OrganizationServiceFault>
    
    

    Wednesday, January 8, 2014 10:41 AM

Answers

  • Hi Chris,

    I'm understand your points. You also have c20_updatededupecode as a flag.

    So I guess when you update contact, try to create a new object and only pass data need to update to it.

    Entity updateContact = new Entity("contact");

    updateContact["contactid"] = targetEntity.Id;

    updateContact["c20_dedupecode"] = code;
    updateContact["c20_updatededupecode"] = false;
    tracer
    .Trace("update contact");
    service
    .Update(updateContact);
    tracer
    .Trace("contact updated");

    Hope it helps.




    • Edited by Linh Giang Thursday, January 9, 2014 10:56 AM id
    • Marked as answer by ChrisJC119 Thursday, January 9, 2014 12:55 PM
    Thursday, January 9, 2014 10:51 AM
  • Hi Chris,

    Yes, you're right.

    My point is you only need to update 2 fields: c20_dedupecode, c20_updatededupecode. Then you should just post those 2 fields back to server.

    Because if you use the retrieved contact, you also post lastname and emailaddress1 back. It's also bad for audit, you will see lastname A change into A.

    We just got generic exception so I just guess.

    Hope it helps.


    • Edited by Linh Giang Thursday, January 9, 2014 11:10 AM
    • Marked as answer by ChrisJC119 Thursday, January 9, 2014 12:55 PM
    Thursday, January 9, 2014 11:09 AM

All replies

  • Hi,

    I don't know how your workflow work but look at your Plugin, I have some comments:

    1. If you're gonna update the record that trigger the plugin --> You should use Pre-Operation to avoid infinite loop (you don't even need to check context.Depth in this case)

    2. Why do you check context.Depth != 1 ? I think this code to avoid infinite loop so it should be context.Depth > 1 then return.

    Hope it helps.

    Thursday, January 9, 2014 10:27 AM
  • Hello Linh Giang,

    Thanks for your reply.

    1. I need to capture the data after it has been changed. I understood this meant I needed to use the post stage.

    2. Because the workflow is running at depth = 1, I need to catch when the plugin is fired from the workflow, in which case the depth = 2. context.Depth > 1 will do the same job, I'm just being wary for any unexpected values like -1.

    Thanks,

    Chris

    Thursday, January 9, 2014 10:45 AM
  • Hi Chris,

    I'm understand your points. You also have c20_updatededupecode as a flag.

    So I guess when you update contact, try to create a new object and only pass data need to update to it.

    Entity updateContact = new Entity("contact");

    updateContact["contactid"] = targetEntity.Id;

    updateContact["c20_dedupecode"] = code;
    updateContact["c20_updatededupecode"] = false;
    tracer
    .Trace("update contact");
    service
    .Update(updateContact);
    tracer
    .Trace("contact updated");

    Hope it helps.




    • Edited by Linh Giang Thursday, January 9, 2014 10:56 AM id
    • Marked as answer by ChrisJC119 Thursday, January 9, 2014 12:55 PM
    Thursday, January 9, 2014 10:51 AM
  • Thanks again for your reply.

    yes, c20_updatededupecode (2 option field) is the flag set by the workflow to trigger the plugin - which then resets it back to false/no.

    Are the first two bold lines you've put doing the same as:

    Entity contact = service.Retrieve("contact", targetEntity.Id, conCols);

    which allows me to use the information currently in the record as well as then update it.

    Thursday, January 9, 2014 10:55 AM
  • Hi Chris,

    Yes, you're right.

    My point is you only need to update 2 fields: c20_dedupecode, c20_updatededupecode. Then you should just post those 2 fields back to server.

    Because if you use the retrieved contact, you also post lastname and emailaddress1 back. It's also bad for audit, you will see lastname A change into A.

    We just got generic exception so I just guess.

    Hope it helps.


    • Edited by Linh Giang Thursday, January 9, 2014 11:10 AM
    • Marked as answer by ChrisJC119 Thursday, January 9, 2014 12:55 PM
    Thursday, January 9, 2014 11:09 AM
  • Hi Linh,

    That appears to have worked on my initial testing.

    I'll keep trying it for a while longer but I'm not getting the error now - lesson learned!

    Thanks,

    Chris

    Thursday, January 9, 2014 12:55 PM