Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 2011 Early bound query dies RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

      I'm having this weird behaviour in CRM 2011 when using the Organization service to execute a LINQ query or simply calling SaveChanges() keeps running and then throws the infamous "Generic SQL error" all the queries used to work before, so that's strange. I got the query itself from Event Log and ran in SQL manually takes under one second. Any clues?

     

    Here's what I get in the Event Log:

    Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 16; Database: MyApp_MSCRM; Query: select 

    "new_obj0".new_objId as "new_objid"

    , "new_obj0".OwningBusinessUnit as "owningbusinessunit"

    , "new_obj0".OwnerId as "ownerid"

    , "new_obj0".OwnerIdType as "owneridtype" 

    from

     new_obj0 as "new_obj0" 

    where

     ("new_obj0".new_objid= '01eb3b5f-32b9-e011-9cab-00155d6daf1a').

    Tuesday, August 9, 2011 8:07 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    If this is being executed from within a plug-in, this code will never work. Since the plug-in is executing when the transaction has already started, when you create a new ServiceProxy any requests it makes will be on a different transaction, which is why you are consistently getting a Generic SQL error.

    Instead of creating the OrganizationServiceProxy manually, you want something like the following:

    public sealed class MyPlugin : IPlugin
{
  ...
  public void Execute(IServiceProvider serviceProvider)
  {
     IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
     IOrganizationService service = factory.CreateService(null);
     //Use the service
   }
}

    Michael

    Thursday, August 11, 2011 4:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you include the proxy types file, they will be enabled automatically. In regards to it working other places, I don't have an answer for that one.

    Michael

    Thursday, August 11, 2011 4:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found out the reason some of the same code used to work before and now it doesn't. Having the plugin step set to Asynchronous or even Synchronous but Post-Operation in the update message allowed me to query the same record in the entity without running into deadlocks.

    • Marked as answer by DotNetMonkey Friday, August 12, 2011 3:33 PM
    Friday, August 12, 2011 3:33 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    When is this query being run?  Is it apart of a Plug-in?  If so, what stage and message is it attached to?
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com Please follow the forum guidelines when inquiring of the dedicated CRM community for assistance.
    Tuesday, August 9, 2011 8:25 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Dave - I'm still debugging this, it's in the update message of a plugin. It seems that after an entity refresh with CrmSvcUtil saving doesn't work, the error coming out are not helpful at all. Will keep on investigating.
    Tuesday, August 9, 2011 8:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm still stuck on this. Suddenly none of my LINQ queries work they just take forever and I get Generic SQL Error !!!

     

    Here's the stack trace

     

     

    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 System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

       at Microsoft.Xrm.Sdk.IOrganizationService.Execute(OrganizationRequest request)

       at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.ExecuteCore(OrganizationRequest request)

       at Microsoft.Xrm.Sdk.Client.OrganizationServiceContext.Execute(OrganizationRequest request)

       at Microsoft.Xrm.Sdk.Linq.QueryProvider.RetrieveEntityCollection(OrganizationRequest request, NavigationSource source)

       at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute(QueryExpression qe, Boolean throwIfSequenceIsEmpty, Boolean throwIfSequenceNotSingle, Projection projection, NavigationSource source, List`1 linkLookups, String& pagingCookie, Boolean& moreRecords)

       at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](QueryExpression qe, Boolean throwIfSequenceIsEmpty, Boolean throwIfSequenceNotSingle, Projection projection, NavigationSource source, List`1 linkLookups)

       at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](Expression expression)

       at Microsoft.Xrm.Sdk.Linq.QueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)

       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

       at MyProject_Plugins.Events.promoteInquiry(Entity oldEntity, IOrganizationService service)

    Wednesday, August 10, 2011 2:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If they're failing on the SQL side, then you can run the profiler in SQL management studio to figure out what's causing the delay.  I suspect there is a locking situation, where you're "Select"ing a row that represents data being updated by the CRM operation, and is therefore locked in SQL.  But that's speculation, since you haven't posted a lot of code or a very thorough explanation of what it does, when it runs, or how the data you're requesting is related to the operation being performed.
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com Please follow the forum guidelines when inquiring of the dedicated CRM community for assistance.
    Wednesday, August 10, 2011 4:24 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ok, after further investigation it seems that I cannot query the entity that is being tracked by the plugin context. There is a lock on that entity for only cause I can query others just fine. Any ideas?
    Thursday, August 11, 2011 2:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    To clarify the situation. You are executing a LINQ query from within a plug-in registered in either Pre-Operation or Post-Operation that attempts to query the entity that the plug-in is executing on?

    Michael

    Thursday, August 11, 2011 2:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes, that's what I'm doing. To get that LINQ query I create a separate OrganizationService instance and all nothing through the current plugin context.
    Thursday, August 11, 2011 3:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    LINQ queries aside, I have lookup field that refers to the same entity I can not update that, from the Event Log I see it's doing a select statement against that entity. Basically any form of query against the entity will cause an error.
    Thursday, August 11, 2011 3:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    When you say that you are creating a new OrganizationService instance, what do you mean? Are you using the IOrganizationServiceFactory?
    Thursday, August 11, 2011 3:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes.

     

               Uri organizationUri = new Uri("http://myserver/XrmServices/2011/Organization.svc");

                Uri homeRealmUri = null;

                ClientCredentials credentials = new ClientCredentials();

                credentials.Windows.ClientCredential = new System.Net.NetworkCredential("user", "password", "domain");

                OrganizationServiceProxy orgProxy = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null);

     

                orgProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());

                IOrganizationService orgService = (IOrganizationService)orgProxy;

                OrganizationServiceContext orgServiceContext = new OrganizationServiceContext(orgService);

                orgProxy.EnableProxyTypes();

     

                XrmDataContext myContext = new XrmDataContext(orgService);

    Thursday, August 11, 2011 3:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If this is being executed from within a plug-in, this code will never work. Since the plug-in is executing when the transaction has already started, when you create a new ServiceProxy any requests it makes will be on a different transaction, which is why you are consistently getting a Generic SQL error.

    Instead of creating the OrganizationServiceProxy manually, you want something like the following:

    public sealed class MyPlugin : IPlugin
{
  ...
  public void Execute(IServiceProvider serviceProvider)
  {
     IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
     IOrganizationService service = factory.CreateService(null);
     //Use the service
   }
}

    Michael

    Thursday, August 11, 2011 4:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Michael that seems to work now, but I have a couple of questions on this solution:

     - How can I enable proxy types this way?

     - How is it possible that the exact code works elsewhere in create messages?

     

    Thanks.

    Thursday, August 11, 2011 4:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you include the proxy types file, they will be enabled automatically. In regards to it working other places, I don't have an answer for that one.

    Michael

    Thursday, August 11, 2011 4:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found out the reason some of the same code used to work before and now it doesn't. Having the plugin step set to Asynchronous or even Synchronous but Post-Operation in the update message allowed me to query the same record in the entity without running into deadlocks.

    • Marked as answer by DotNetMonkey Friday, August 12, 2011 3:33 PM
    Friday, August 12, 2011 3:33 PM