LINQ to CRM Caching Issue

Recently I was given the task to create a fairly simple ASP.NET application to import records to a Microsoft Dynamics CRM 4.0 system from an Excel file. I was using LINQ to CRM and because some of the records being created were immediately required to link to other new records, I had to commit changes to the CRM database fairly frequently, and then immediately pull the newly created record.

Things were running along quite smoothly, when suddenly I started getting errors that the newly created record didn't exist. I could clearly see the record in CRM, so I knew it was being created, but could not figure out why it was suddenly not being found. It finally occurred to me that the original dataset was being cached, and was not being refreshed on subsequent requests.

After a little searching, I came upon a forum posting describing the same issue I was having, with a solution that worked like a charm for me. Hopefully it will help someone else out there.

I added the following method to my code, and right before I tried to retrieve the newly created record, I called it to clear the cache so that the dataset would be refreshed.

public static void ClearCache(string entityName)

{

    const string format = "adxdependency:crm:entity:{0}";

    var dependency =

        string.Format(format, entityName).ToLower();

    var cache = Microsoft.Xrm.Client.Caching.CacheManager.GetBaseCache();

    cache.Remove(dependency);

}

This solution worked perfectly for me, I hope it helps someone else out there.

Issue with Publishing Reports for External Use

We use Microsoft Dynamics CRM for almost everything; leads, opportunities, project planning, resource planning, time tracking and very importantly, invoicing.

Over the years we have developed a lot of code to easily enter our billable time and generate invoices.  One key Dynamics CRM add-on that we have is the "mPDF" software from Magnetism Software Solutions, you find more details here: http://www.magnetism.co.nz/solutionscasestudies/solutions/mpdf.aspx.

Basically it is a plug-in that lets you generate reports as PDFs and attach them to an email in a workflow.  For us, this allows us to fairly quickly fire off our invoices, project updates or schedules to our partners and clients.

In order for the mPDF software to work you need to publish the report for external use.

However, I had an issue where the report wasn't getting generated.  I checked the logs and saw that the MSCRMAsyncService was generating an error:

"System.Web.Services.Protocols.SoapException: One or more data source credentials required to run the report have not been specified."

This was not an issue with mPDF, but rather an issue with running reports that had been published for external use.

I navigated to the SRS Report Manager URL ("http://" followed by the reportserver name "/reports") and when I tried to run the external report I got prompted for credentials.  This should not happen. 

You will notice that any report that gets published for external use gets put in the root of the folder that represents your CRM organization.  The rest of the CRM reports remain in a folder called "custom".  The regular CRM reports will use a data source called "MSCRM_Datasource" in the custom folder, and the external reports will use the MSCRM_Datasource that is in the root of the CRM org folder (see example:)

SRS Report Manager

If look at the datasource in the custom folder for non-external reports, it should look something like this:

Internal CRM Reports Datasource

My problem was that the datasource for external reports for some reason (OK, it *might* have been me, but it might have been a system issue as well) was that is was identical to the internal datasource.

When running a report from CRM it will supply credentials to the report, and this datasource is setup to accept that and then communicate with SRS to render the report (via the SRS connector in case you ever wondered why you had to install it).  If we run it from outside of CRM, it does not have the credentials (which incidently are not standard windows credentials either, but rather unique CRM supplied ones) and the report cannot generate.

Finding a correct example of the MSCRM_Datasource and how it should be setup for externally published reports, I updated the datasource in the root of the CRM org folder to look like this:

Externally Published Reports MSCRM_Datasource

I basically had to the data source type, the connection string and the connect using.  This had to use a more traditional data connection approach with windows security and not from CRM.  The tests were successful and I was able to run external reports again.

Here is a table outlining the differences between the two datasources:

	Internal CRM Reports MSCRM_DataSource	External use CRM Reports MSCRM_Datasource
SRS Report Manager Location	\<<CRMORG>>\custom	\<<CRMORG>>
DataSource Type	Microsoft Dynamics CRM SQL	Microsoft SQL Server
Connection String	MSCRM Data Connector Connection String	Workstation ID=<<crmserver>>.Microsoft.Crm.Setup.Server;Data Source=<<sql instance>>;Initial Catalog=<<CRMORG>>_MSCRM;Connection Timeout=60;Integrated Security=SSPI
Connect Using	Credentials supplied by the user running the report	Windows Integrated Security

(replace anything in the << >> with your own settings)

I hope this post gave some insight on how the MSCRM_Datasource works for publishing reports for external use and perhaps will help you in future troubleshooting efforts!

Disclaimer: Before attempting to modify any of these settings, make sure you have backups, certifications, signoffs and confidence working on server side technologies. I supply this blog as information and take no responsibility for any inaccuracies or issues that arise.