CRM 4.0 SQL Time out Issue

We recently started getting problem while creating new entity or importing any new entity in one of our CRM Environment.

Following is full description of error logged on Server Event Log.

Problem

The description for Event ID 17415 from source MSCRMAsyncService cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
<>
<>
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.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteReader(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.Asynchronous.DataAccessBase.ExecuteSqlCommandAndProcessRecords(IDbCommand command, RecordProcessor recordProcessor)
at Microsoft.Crm.Workflow.WaitSubscriptionDataAccess.Notify(IDbCommand selectCommand, WaitNotificationService waitNotificationService)
at Microsoft.Crm.Workflow.WaitSubscriptionDataAccess.Notify(String hostId, WaitNotificationService waitNotificationService, Int32 maxRetries)
at Microsoft.Crm.Workflow.WorkflowHost.OnNotificationTimerEvent(Object sender, ElapsedEventArgs e)
the message resource is present but the message is not found in the string/message table

Cause:
There is a slow response from SQL which leads to the CRM platform timing out.

Resolution:

After carefully debugging and investigating it turns to be SQL Time out issue and the process of creating new entity is taking more than 30 seconds.
We added following registry keys to HKLM\Sofware\Microsoft\MSCRM folder and this resolved this problem.

DWORD OLEDBTimeout = 300000 (decimal)
DWORD ExtendedTimeout = 1000000 (decimal)
DWORD NormalTimeout = 1000000 (decimal)

Cheers,
MayankP:)

Multiple Column Sort in CRM View

One of customer asked me this question and last week, fortunately I knew the answer which is quite simple as follows..

Step 1: Open the CRM View

Step 2 : Click on the first column header

Step 3: Now Hold down “shift” key

Step 4 :Click on the second column header

Now you will have second column as the sub-group of first column.

Cheers,
MayankP:)

CRM Plug-in / Custom Workflow debugging check list

UPDATE (30/07/2010) : I haved updated few steps based on comments from Andriy Butenko (CRM MVP)

If you got plug in or custom workflow application which is not working as expected on CRM server or giving unexpected error then following is the check list to set up for debugging Plug in/Custom workflow application.

1-Ensure that your plug-in is signed ( For Custom workflow this step is optional)

2-Rebuild the plug-in/Custom workflow

3-Restart CRM web site (i.e. run an iisreset command on server)

4. Also restart CRM Asynchronous Service on CRM server

5-copy the .pdb file and your .dll file to server\bin\assembly

6-Register the Plug-in Registration Tool V2 (http://code.msdn.com/crmplugin)

7. Refer to your dll in bin\assembly

8. Make it disk deployment (This step is also optional)

9-Register the step

10-Register the image if needed for relevant Plug-in

11- Open the relevant application in visual studio and click on Debug -> attach and then
select w3wp.exe if Synchronous plug-in or
select CRMAsyncService if asynchronous plug-in or Custom workflow.

12. You must be all set by then and just need to put breakpoint on relevant code line.

Note:- steps 9 & 10 are not needed for custom workflow application.

SOURCE

Hope this helps…

Cheers,
MayankP:)

Filtering Lookup Data in CRM 4.0

Following are the some options (supported and unsupported) in implement filer in CRM 4.0 entity form for any lookup fields.

Option 1: Change lookupsingle.aspx file (Unsupported)

George Doubinski explains full steps in his blog at Here

Option 2: Buy Stunware Filtered Lookup Add on for CRM

If you looking for truly global solution which is embedded in CRM for this then this would be best fit answer for this requirement.

Please check out full details at their website.

Option 3: Using java script and entity changes

This option requires you to do some entity changes and also put relevant java script code in CRM on load event.
Let’s understand this with one example, Case Form got account and contact look up. When ever user select particular account then while looking up contact only contacts record that belongs to selected account should get displayed, (i.e. contacts look up filtered based on account selected).
Now following steps to achieve this functionality using this Option

Step 1: (Entity Change)

Change the contact search field names in Contact Lookup view and Quick Find view (i.e. add parentcustomerid as searchable field in these view and publish the entity)

Step 2: (Java Script on Entity Form on Load)

Above screen print displays base code for this, before using relevant field name should get changed and then put in relevant CRM Form On Load Event.

Hope this helps!!

Cheers,
MayankP