locked
Getting an error while trying to add a new attribute to CRM Account entity RRS feed

  • Question

  • Hi,

    In CRM 3.0 we're trying to add a custom attribute (Boolean) to Account entity, which we've done many times before, but now getting Error = 0x80040e31 (SQL timeout expired) in production environment. We have crm database with two front-end servers and various web applications and services accessing CRM database, but Account is the only entity that we now have a problem with. Any ideas on how to work around this would be greatly appreciated. 

    Tuesday, October 18, 2011 1:58 PM

Answers

All replies

  • Could be worth looking at the Event Log on the SQL Server and seeing if SQL is logging a Application Error or Security Error that is leading to the Timeout.  Might also be worth looking at the Event Log on the CRM Server to see if this provides any other details.

    Is the error only ever for adding a Boolean type field to the Account entity?  i.e. adding Text fields to the Account entity is fine, and adding a Boolean field to the Contact entity is fine?


    Kind Regards, Paul | http://crmconsultancy.wordpress.com
    Tuesday, October 18, 2011 2:24 PM
  • Paul, 

    Thank you for the suggestion. To your questions: the error is not limited to a Boolean attribute, but it is limited to Account entity.

    What do you mean by Security Error? Could there be an issue like that even if I have admin rights and have no issues adding attributes to other entities? Do you mean Account view in the database itself?

    Looking at the logs did not produce any other error than "SQL Timeout" error. I can see that the attribute was inserted successfully into "Attribute" and StringMap tables. And then the error comes: (see below). It seems like something is holding a lock on that view. I tried to do it off hours - same problem. Could something be there that holding a lock permanently?

     >Exception when executing batches query: if exists (select * from sysobjects where name = 'Account' and xtype = 'V')

    begin

        drop view Account

    end Exception: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

       at Microsoft.Crm.Metadata.SqlHelper.ExecuteBatches(CrmDbConnection connection, SqlTransaction transaction, String registryName, SqlCommand sqlCommand)

    [2011-10-14 12:24:54.6] Process: w3wp |Thread: 7140 |Category: Platform.Metadata |User: 00000000-0000-0000-0000-000000000000 |Level: Error | AttributeService.Create

    at AttributeService.Create(Guid entityId, AttributeInfo attributeInfo)

    at AttributeCreate.Execute(User user, ParameterBag paramBag)

    at SystemCustomization.CreateAttribute(XmlNode data)

    at RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean verifyAccess)

    at RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess)

    at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

    at LogicalMethodInfo.Invoke(Object target, Object[] values)

    at WebServiceHandler.Invoke()

    at WebServiceHandler.CoreProcessRequest()

    at SyncSessionlessHandler.ProcessRequest(HttpContext context)

    at CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()

    at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    at HttpApplication.ResumeSteps(Exception error)

    at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)

    at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)

    at HttpRuntime.ProcessRequest(HttpWorkerRequest wr)

    at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)

     

    Tuesday, October 18, 2011 6:58 PM
  • Aye - looks like some other process has a SQL Lock on one of the Views that CRM recreates when an attribute is added to the Entity.

    MSCRM maintains 2 SQL Views (Account and FilteredAccount) for each entity alongside 2 SQL Tables (AccountBase and AccountExtension) - adding or removing attributes to an Entity forces CRM to DROP and RECREATE the Views, and ALTER the underlying Tables.  If the Views or Tables are locked for any reason, then CRM would not be able to do this and so be prevented from creating the attribute.

    Sounds like some other process has stopped and retained a SQL Lock on one of these Views which is stopping new attributes being added to the Account entity as a result.  Best bet might be to access SQL Server to check whether a process has a SQL Lock on this View and possibly forcibly remove the process from locking the view.

    You can do this via SQL Management Tools I believe (althrough my knowledge here is a little rusty) using the Activity Monitor in SQL Management Studio, or you could potentially simply restart the SQL Server out of hours (which may then require the CRM Server to be restarted afterwards).


    Kind Regards, Paul | http://crmconsultancy.wordpress.com
    Tuesday, October 18, 2011 7:30 PM
  • This may be a silly question, but have you tried using sp_lock / Activity Monitor /etc to determine if there are any locking/blocking issues? There could be some kind of long-running integration process that is preventing this, since it seems to only be the Account entity that exhibits the problem.

    Tuesday, October 18, 2011 7:38 PM
  • Thanks, all for your recommendations. Somehow made it work last night, but not exactly sure whether it was pure luck or increased timeout on a server that helped (http://kbalertz.com/918609/occurs-import-large-customization-files-Microsoft-Dynamics.aspx)
    • Marked as answer by EllaPromis Wednesday, October 19, 2011 3:26 PM
    Wednesday, October 19, 2011 3:26 PM