Dynamics CRM 4.0 for Outlook – A SQL Error occurred

This is an error I am sure many organisations that have deployed Microsoft Dynamics CRM 4.0 for Outlook will have seen.  Typically, this happens when a user selects “Track in CRM” in Outlook and that email is quite large or contains attachments.  

A SQL Server error occurred. Try this action again. If the problem continues check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support.

To begin resolving this issue I went straight to the CRM 4.0 Diagnostics Tool and ran a Trace from the Microsoft Dynamics CRM Server.  Once I had replicated the error, I Found the following error message in the trace file.

>CrmSoapExtension detected CrmException:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I found the resolution to this issue in KB 918609 and the steps I carried out were as follows

(Steps take from http://support.microsoft.com/kb/918609 at 24/05/2011 10:39)

1.       Click Start, click Run, type regedit, and then click OK.

2.       Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM

3.       Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

4.       Rename the DWORD value to the following value:

OLEDBTimeout

5.       Right-click the DWORD value, and then click Modify.

6.       In the Edit DWORD Value dialog box, type 86400 in the Value data box, click Decimal in the Base option, and then click OK.

Note According to the requirement of the computer that is running SQL server and the number of customization files, the value can be larger than 86400. The value of 86400 is equivalent to 24 hours.

7.       Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

8.       Rename the DWORD value to the following value:

ExtendedTimeout

9.       Right-click the DWORD value, and then click Modify.

10.   In the Edit DWORD Value dialog box, type 1000000 in the Value data box, and then click OK.

                        

Notes

o    In the Value data box, you can type a value that is larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. This is hexadecimal 0x7FFFFFFF.

o    If this key already exists, notice the current value. After you have completed the import or the upgrade for Microsoft Dynamics CRM, set the value of this key back to the original value or delete the key if it did not previously exist. The default OLEDB timeout value is 30 seconds.

Dates issue in Microsoft Dynamics CRM 4.0 Report

Dates issue in Microsoft Dynamics CRM 4.0 Report

I came across a problem today with a report I am writing in Microsoft Dynamics CRM using Report Builder 2.0

In my report I am taking user submitted Parameter of data type Date/Time and searching for transactions in CRM which are between the two dates specified using the following query

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

FilteredAp_transaction.ap_date

between @start and @end

What I found was that the report was not bringing in the expected results

In SQL dates are stored as 2011-01-13 10:08:20.000 in the format YYYYMMDD hh:mm:ss[.mmm]

Example

In the interest of simplicity let’s assume we are looking for all transactions on the same day

@Start = 2011/03/30

@End = 2011/03/30

Running the following Query gives 8 results.  However I knew this was incorrect.

After some investigation I realised it was the time portion of the Date/Time data type that was causing issues.

By adding the following line (Thank you www.bennadel.com)

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

I now have 9 results.  The reason for this is because the variables in the report are of type, Date/Time.  As well as the query comparing the date, it is also comparing the time value.  As I had a time value of 15:49:33.000 this was not being included in the results.  The above CAST statement strips the time value and replaces it with 00:00:00.000

We now go from a value of 2011-03-30 15:49:33.000 to 2011-03-30 00:00:00.000

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

between @start and @end

Adding a Null value in SQL / Report Expression

When adding two fields together in a Microsoft Dynamics CRM 4.0 SSRS Report I was getting an issue where if one of the values being added was NULL, I would get the message #Error with the Matrix.

Below is an example of the Expression used within my Report.  In tests this worked fine when both fields had values, but if just one field was NULL the row would not calculate

=Fields!ap_taxtobeclaimed.Value + Fields!ap_taxclaimed.Value

To resolve this issue required me to use a SQL Function called COALESCE()

COALESCE() can be used to take an expression from SQL  and a value to use in the event that the values from the expression are NULL

COALESCE ( expression [ ,...n ] )

SELECT

(COALESCE(FilteredAp_transaction.ap_taxtobeclaimed,0) + COALESCE(FilteredAp_transaction.ap_taxclaimed,0)) AS TaxSum

,FilteredAp_transaction.ap_taxtobeclaimed

,FilteredAp_transaction.ap_taxclaimed

,year(FilteredAp_transaction.ap_date) as year

FROM

FilteredAp_transaction inner join

FilteredContact AS CRMAF_FilteredContact

on CRMAF_FilteredContact.contactid = FilteredAp_transaction.ap_contactnameid

Using the Coalesce expression I was able to display the correct Tax calculation per year

Report Builder 2.0

Today, I had the requirement to create a report in Dynamics CRM 4.0.

I did this using Microsoft SQL Server Report Builder 2.0, in the past I have always used Visual Studio but decided to give the Report Builder a go to get a customer perspective on the application

Report Builder is a stand-alone application from Microsoft available to download from the internet. 

 I specifically wanted to utilise the Matrix capabilities and using the wizard makes light work of setting up the data source connection and designing a query, including the fields, Relationships and applied filters.

I personally prefer to use not to use the wizards and use the drag and drop functionality

Useful Articles

Overview

http://www.microsoft.com/sqlserver/2008/en/us/report-builder.aspx

Report Builder 2.0 Download

http://www.microsoft.com/downloads/en/details.aspx?familyid=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Tutorials (Report Builder 2.0)

http://technet.microsoft.com/en-us/library/dd239338(SQL.100).aspx

Expression Examples (Report Builder 2.0)

http://msdn.microsoft.com/en-us/library/dd283099(v=sql.100).aspx