Troubleshooting CRM “Generic Sql Error”

Copied from Erik post 

Original post : https://community.dynamics.com/product/crm/crmtechnical/b/xrmavanade/archive/2011/04/26/troubleshooting-crm-quot-generic-sql-error-quot.aspx

 

 

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 fairlycommon 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.