Dynamics CRM 2016 Troubleshooting
Generic SQL error
When trying to create a new entity record (using Microsoft.Xrm.Sdk.Client.Create(...)) the following exception is generated:
Generic SQL error.
The error is an Microsoft.Xrm.Sdk.OrganizationServiceFault, but contains no other useful information.
This error indicates either:
- a problem with Microsoft Dynamics or more likely with the SQL Server instance that Dynamics is using.
- a problem with the application logic or with the design of the solution.
Try each of these in turn until you have the problem resolved:
- Check that you are not trying to create a record which already exists. Specifically if the record contains a field of type "Primary Key" then check that another record does not already exist with the same value.
If the CRM instance is on-site then:
- Log into the server hosting Microsoft Dynamics and make sure that it is not waiting to complete the installation of any Windows updates for Dynamics or SQL server. If it is then reboot the server to let it complete installation of those updates.
- Check whether there are any pending Windows updates to either Microsoft Dynamics or SQL server, and if there are then install those. This is important because if there is a problem that Microsoft can fix then there is a very real possibility that they have already found and fixed the problem.
- Look at the application event log on the dynamics server. This may give some additional clues to the problem.
In my case I could see an error logged, but it didn't contain anything helpful.
- On the server hosting Microsoft Dynamics run "Check for solutions to problem reports".
In my case this uploaded a number of problem reports for Microsoft Dynamics (and I assume therefore for the problem in hand). Sadly this didn't provide any solutions for me.
- On the database server run "SQL Server Profiler". Create a new trace, and I would suggest capturing all TSQL events. Then re-run the activity that is generating the "generic sql error". Stop the trace as soon as the error is generated and then review the trace.
Try enabling tracing and see if the trace gives a clue.
The easiest way of turning on various trace logs is to use the application "Diagnostics Tool for Microsoft Dynamics CRM 2011". This is a free tool available from https://crmdiagtool2011.codeplex.com/
Assuming you are using the "Diagnostics Tool for Microsoft Dynamics CRM 2011" then be sure to turn the "DevErrors" on.
- If the CRM instance is a Microsoft hosted Dynamics 365 installation then raise the problem with Microsoft.
If these notes do not help then the following external resources might help
These notes have been tested with Microsoft Dynamics CRM 2016, and may apply to other versions as well.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.