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:
Omni.OmniSoapException: [0x80044150:appointment] Generic SQL error. ---> System.Web.Services.Protocols.SoapException: Server was unable to process request.
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
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:
-
Use SQL Profiler to examine an example of the error. For detailed instructions, see Troubleshooting CRM "Generic SQL Error".
-
Email to Riva Support a screenshot 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 data store.