Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Microsoft Dynamics CRM - Generic SQL error.

Proposed Microsoft Dynamics CRM - Generic SQL error.

  • Tuesday, 29 November, 2011 3:55 AM
     
     
    Sign In to Vote
    0

    Dear All

    With Dynamics CRM 2011, experiencing an issue with SQL. 

    Log File;

    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-11-29T03:26:31.2765174Z</Timestamp>
      <InnerFault>
        <ErrorCode>-2147204784</ErrorCode>
        <ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
        <Message>Generic SQL error.</Message>
        <Timestamp>2011-11-29T03:26:31.2765174Z</Timestamp>
        <InnerFault>
          <ErrorCode>-2147220970</ErrorCode>
          <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
          <Message>System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #C6A5FCCA</Message>
          <Timestamp>2011-11-29T03:26:31.2765174Z</Timestamp>
          <InnerFault i:nil="true" />
          <TraceText i:nil="true" />
        </InnerFault>
        <TraceText i:nil="true" />
      </InnerFault>
      <TraceText i:nil="true" />
    </OrganizationServiceFault>
       at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
       at Microsoft.Crm.Extensibility.InprocessServiceProxy.ExecuteCore(OrganizationRequest request)
       at Microsoft.Crm.Asynchronous.PublishDuplicateRuleOperation.GetUnpublishedRecords(String entityName, String[] colSet, Guid ruleId, Int32 maxRecords, String[]& versionNumbers, IOrganizationService crmService)
       at Microsoft.Crm.Asynchronous.PublishDuplicateRuleOperation.PublishWithPaging(ShareableDuplicateRule rule, Boolean isMainEntity, AsyncEvent asyncEvent, DuplicateRuleDataAccess data, IOrganizationService crmService, IConfiguration configuration, PerformanceCounter _counter)
       at Microsoft.Crm.Asynchronous.PersistMatchCodeOperation.InternalExecute(AsyncEvent asyncEvent)
       at Microsoft.Crm.Asynchronous.AsyncOperationCommand.Execute(AsyncEvent asyncEvent)
       at Microsoft.Crm.Asynchronous.QueueManager.PoolHandler.ProcessAsyncEvent(AsyncEvent asyncEvent)

    additionally this error throws in every 5 minutes.

    Thanks

     

All Replies

  • Tuesday, 29 November, 2011 6:25 AM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    suggestions--since this is a SQL error, I would focus my troubleshooting on the db level.:

    1.  check the event log on both crm and sql

    2.  Check the sql server--are you low on disk space with SQL, are there possibly limits to how much your mscrm db can grow?

    Even if you are on CRM online, this may be applicable--at one time I received generic SQL errors and it turned out there was a limit on the amount of growth on our database at the Microsoft hosting center.

    3.  Increase the OLEDBTIMEOUT registry setting (create it if it doesn't exist).  http://support.microsoft.com/kb/918609

    4.  Are there any unsupported db hacks like triggers that are running on the base tables, or are there any integration processes reading or writing to CRM that could be creating database locks?  These are the kind of things that create generic errors in CRM because they are out of the supported crm world, so they don't give you specific errors in CRM.  If something is happening every 5 minutes, makes me wonder if there is some kind of process that is running every five minutes.

    5.  Are there any big/complex reports being run, or reports reading from CRM views or tables without "with (nolock)?"  These can cause database locks and result in SQL errors in CRM.

     

     
  • Thursday, 23 February, 2012 6:03 PM
     
     
    Sign In to Vote
    0

    Joel,

    We have the exact same error as Manjula reported.  And it is usually created when a number of workflows are fired based on the creation of an Order.  Some workflows fire, and some simply will not and the GENERIC SQL error is thrown.

    In response to your message:

    1. We previoulsy enabled SQL tracing but no errors pop up that seem to correlate with the "Generic SQL error" CRM registers

    2. Server has plenty of space (it is an on-premise install)

    3. We already created the OLEDBTIMEOUT registry setting and set it to 86400

    4. Pretty sure there are no "unsupported DB hacks".

    5. There is no heavy demand of the sort that you describe in item 5.  Moreover, it seems to happen on a particular transaction (creation of an ORDER, which triggers a number of workflows).  If it was a workload issue, one would suspect the error to be affecting other operations and in a more random fashion.

    Hardware-wise ,this CRM install runs along with SQL2008 on a Win2008R2 VM with 16 Gig of assigned RAM.  

    It is pretty darn annoying because it stops important back-office processes.

    Any clues anyone?

    Henri


    • Edited by Henri C Thursday, 23 February, 2012 6:25 PM
    •  
     
  • Thursday, 23 February, 2012 11:30 PM
    Moderator
     
     
    Sign In to Vote
    0

    It sounds like the most likely cause is SQL Deadlocks. You can use SQL Performance Monitors counters to get a better idea if this is the case.

    If this is the cause, then the solution is probably to try and get more control over the sequence of actions. For example, you might try creating one workflow rule, which then runs other child workflows in a given sequence, possibly with time delays between them. Or, it may be preferable to move some of the logic into plugins, where you get more control over the execution sequence

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk


     
  • Thursday, 1 March, 2012 11:19 PM
     
     
    Sign In to Vote
    1

    David,

    Indeed, we have now consolidated control over the launching of Workflows through a "Master Workflow Governor" and we will see if this was a remedy.  If it is, I shudder to think how larger organizations handle this because it is not of a lack of hardware/RAM etc.  Running 4 or 5 workflows on the CREATE event should not cause locks in SQL, at least one would think.

    Stay tuned.

    Henri

     
  • Thursday, 15 March, 2012 3:14 PM
     
     
    Sign In to Vote
    0
    Has this worked out for you? We're seeing these same error occasionally for our clients both On Premise and in the cloud solution.
     
  • Tuesday, 8 May, 2012 7:43 PM
     
     
    Sign In to Vote
    0
    I have same problem when I run customized workflow to trigger multiple times.
     
  • Thursday, 9 August, 2012 9:32 PM
     
     
    Sign In to Vote
    0

    Update:  the use of a workflow that governs the launch of other workflows on a CREATE event has stopped these errors.  So at least this worked for us.

    H

    Henri

     
  • Wednesday, 22 August, 2012 8:45 AM
     
     
    Sign In to Vote
    0

    Restarting SQL Server should help. It can be caused by multiple processes are launched by one event in CRM, for example several workflows and plugins fired up by creating a record.  You can avoid it by launching one process, that launches the other processes in course of time. Also, you can stack upon this issue when using synchronous plugin instead of async one.

    Best regards! :)