Troubleshooting CRM "Generic Sql Error"

The Microsoft CRM web-services normally give you pretty good error messages when you make an invalid request, but occasionally you will get this error message: “Generic Sql Error”. These errors pop up enough that they’ve become a fairly common question on the CRM development forums. The usual responses are to either enable CRM tracing or use SQL Server Profiler to get a better error message. I find that the CRM trace logs don’t always give you anything better that the same generic error message the SDK provides, so SQL profiler is probably the better option. However, if you don’t have experience using the SQL Profiler it can be hard to find the actual error message in the vast amount of data it provides. These are the steps I take to get to the bottom of most CRM “generic SQL” errors using the SQL Profiler:

1. Open SQL server profiler on your SQL machine. You can usually find it in the start menu under SQL server 2008 -> Performance Tools.
2. In SQL server profiler, click File -> Templates -> New Template.
3. Select "Microsoft Sql Server 2008 R2" in the Server Type dropdown.
4. Give the Template a name in the “New Template Name” field (something like “CRM Generic Sql Error”), and then click the “Event Selection” tab.
5. On the “Event Selection” tab, check the “Show All Events” and “Show all Columns” checkboxes. 
6. In the Events table, scroll down and expand the “Errors and Warnings” section, then check the checkbox next to “Exception”  (this will automatically check all the other boxes on the same row).
   
   
   
7. In the same “Errors and Warnings” section, also check the “User Error Message” checkbox. This event will give you the real SQL error message.
8. Expand the “Stored Procedures” section and check the “RPC Completed” checkbox. This event will give you the actual SQL statement that caused the SQL error (since CRM uses stored procedures for nearly everything).
9. Next, click the “Column Filters” button, and select “DatabaseName”, and enter the name of your CRM database in the “Like” field.  This will ensure that you only see CRM related data in the profiler output.
   
10. Next, save the template and then click File -> New Trace.  Enter the information to connect to the SQL server when prompted and then select the template you created in the “Use The Template” field. Then click Run to start tracing. 
11. Now just go do whatever it was in CRM that caused the Generic Sql Error while the trace is running. You will want to limit the amount of time the trace is running so that you have less data to sift through in the SQL profiler.  Afterwards, you can click the red “Stop” button in SQL Profiler to stop tracing.  
12. Then you can search through the recorded events to find the one that caused the error. Fortunately the actual SQL errors are usually highlighted in red.  
    
    
    
    In my case, there was a red “User Error Message” event that read “Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘Account’ because it is not full-text indexed”.  And the next event listed in an RPC:Completed event for the RetrieveMultiple request on the account entity that caused the error (you can see it has "1 - Error" in the error column).  So it turns out that I had used a “contains” operator in a query-expression condition (which isn’t supported).  Switching it to a “Like” operator fixed the error.   

 ~Erik Pool

 This posting is provided "AS IS" with no warranties, and confers no rights.

 

Comments

[Erik Pool]

Christophe, CrmDiagTool will just enable CRM platform tracing for you, but often the error messages in the platform trace files will still just say "generic sql error" so you need to dive deeper.

[Christophe Trevisani Chavey]

I used to identify this generic sql error with platform tracing activated by CrmDiagTool4.

Did not resolve for you?

This method is really the last I use in critical situation. Probably the more efficient in this case.

[Safiulla]

This is a very good article to identify the exact CRM error. Thanks !

[jimenezalbert0]

Thanks this was very useful

[Erik Pool]

Hi Scott,
No, unfortunately I don't know of any way to troubleshoot a Generic SQL error on CRM Online. Debugging in a CRM Online environment is pretty much restricted to using the tracing service. I would post the details of the error to the CRM Development Forum, someone there may have seen the same error.

[Scott Farriss]

Hi Erik,
Thanks for the article. Do you know of any tools, or any way to retrieve these errors from CRM Live? We are receiving the "Generic SQL Error" in the MS hosted environment, and this does not happen when we test locally.

Thanks