Fix for: Microsoft Dynamics CRM 2011 U12 Generic SQL Error

Issue

A Generic SQL error may appear in the Riva On-Premise CRM Monitor application for some users. (For Riva Cloud users, this is extremely unlikely to occur, but if it does, contact the Riva Success Team.)

The crmex-log files contain an error stack similar to this:

Because this type of error is occurring in the Microsoft SQL server hosting the CRM datastore, Riva logs cannot determine what the specific error is without additional diagnostic investigation.

To gather additional information:

1. Submit a support request.

2. Use SQL Profiler to examine an example of the error. For detailed instructions, see Troubleshooting CRM "Generic SQL Error".

3. Email to Riva Support a screen shot of the results.

Possible Cause

Applying a U12 patch to the Dynamics CRM 2011 server breaks a feature used by Riva called “AllColumns”. With this feature, Riva can simply ask for all columns available in the CRM, and the system returns them. However, as of U12, it appears that SQL Server has an SQL bug that causes this query to fail with these Generic SQL errors.

Solution for Riva On-Premise

To prevent the error:

• Apply the following advanced custom options to the Riva CRM connection:

  Sync.MsCrm.ExcludedColumns.Appointment = subscriptionid,createdby,modifiedby,regardingobjectid

  Sync.Mscrm.UseExplicitColumnSet = true

Applies to

• Riva for Dynamics CRM 2011 U12.
• Riva for Sugar: Use SQL Profiler if Microsoft SQL Server is hosting the CRM datastore.