CRM Error Message of the Day: 0x80040237

It's been a while since my last "error message of the day" post, but here's another one.

The description for this error is: DuplicateRecord: Operation failed due to a SQL integrity violation. However, the DuplicateRecord part is a bit misleading. The actual problem can be a variety of things. Just do a search for 0x80040237 and you'll find a lot of different situations.

I came across this error when working on an Experlogix project for a client. Experlogix has a great Product Configurator tool for CRM. You can read more about that here.

Anyway, IT operations had just implemented sql replication based on the excellent whitepaper by Sonoma Partners. This whitepaper describes how to increase performance by putting the reports on a dedicated sql server. To do this you need to replicate the CRM data to the dedicated report server.

After replication was in place we received errors when trying to create a configuration in Experlogix. A configuration is a quote with quotedetails.

The log files showed this:
w3wp-.log
0x80040e14
[2008-01-15 10:00:29.934] Process:w3wp Thread:3660 Category: Unmanaged.Platform User: PlatformUser Level: Error InternalCrmSetErrorInfo File: C:\bt\876\src\platform\include\Util\ICrmError.h Line: 362>Operation failed due to a SQL integrity violation.

w3wp-MSCRMServices-.log
Server was unable to process request. --> Exception has been thrown by the target of an invocation. --> Exception has been thrown by the target of an invocation. --> Exception from HRESULT: 0x80040237.DuplicateRecord 80040237 Operation failed due to a SQL integrity violation.

Error code 0x80040e14 is a general sql error and doesn't tell you much. Error code 80040237 is the CRM Operation failed due to a SQL integrity violation error.

So the trace files give little more information. Sql Profiler to the rescue!

We reproduced the error while we had Sql Profiler running. A few tips when using Sql Profiler:

1. Filter on DatabaseId, especially when the sql server runs multiple databases, which is common.
2. To get the database id run this statement in Query Analyzer: select db_id('organizationname_MSCRM'). This will return a number that you can use to filter on. This will limit the amount of data returned by the profiler.
3. Limit the number of events you want to see. I usually want to see some Stored Procedures and TSQL events and I'm not interested in Security Audits and Sessions events (these are enabled by default).

In the first trace I noticed the following statement: IF @@TRANCOUNT > 0 ROLLBACK TRAN
Normally you should see: IF @@TRANCOUNT > 0 COMMIT TRAN

So everything went fine up until the last update of the quote header, after which it rolled back.

I added some extra events from the "Errors and Warnings" category. And after doing a new trace the Exception event showed this: Error: 7139, Severity: 16, State: 1

Ok, now we're getting somewhere!

Doing a quick search shows this: "SQL server error 7139: Length of text, ntext, or image data (834122) to be replicated exceeds configured maximum of 65536."

So what was wrong? Experlogix uses a custom attribute (exp_config) to store the configuration of the quote. This is an ntext field in sql and can become quite large, depending on the complexity of the configuration.

Now you're saying, I'm not using Experlogix so I won't run into this problem, right? Wrong! This can also happen when using attachments. An attachment is also stored in an ntext field and can get pretty big.

So, what's the solution? Luckily it's easy. You can use sp_configure to change the value for max text repl size. The default value is 65536 but you can increase this up to 2.147.483.647.

Execute this statement in query analyzer:
sp_configure 'max text repl size', 1000000
reconfigure

This will increase the size from 65536 to 1000000. Obviously you can change this value according to your needs. Given that an attachment can be up to 4 MB you might want to set it to 5000000 for example. That way you're safe.

Disclaimer: These findings are based on CRM 3.0, but I assume it also applies to CRM 4.0.

Generic sql error - SQL Timeouts

We are having some troubles with a specific CRM installation lately. For our Mobile CRM application we use FetchXml filters to determine which records to sync and some of those queries consistently result in a Sql timeout.

We did some extensive sql profiling and indeed, the problem queries take 30 seconds to execute and then they timeout. Strange part is that when executing the exact same queries in the Query Analyzer, they execute 50-100 times faster! This difference is shown in the Sql Profiler, so it is purely a Sql thing and has nothing to do with the fact that there are more layers in between when the query is executed using CRM.

Also, we are not talking about a database with hundreds of thousands of records, we're talking about thousands. We are also talking about a rather large query with several joins between e.g. annotations, accounts, activitypointers and activityparties. Not a really simple query, but also not extremely complex.

We also did some database tuning and implemented some recommendations, like new indexes and statistics. This did help for the queries that were already executing fine, they were even faster, but this didn't help for the problem queries.

Some google-ing shows that more people have noticed the extreme difference in performance between executing queries using ADO.NET and directly in the Query Analyzer. Unfortunately, I haven't seen any good explanations or answers yet. Some people talk about different SET options which may affect the query execution plan. Other people found workarounds by changing the queries, etc. However, that's not an option because it's CRM that builds the queries so there's nothing we can do about that AFAIK.

I think the next step is to contact Microsoft support or the CRM team about this and see if they can come up with a solution. In the meantime, if anyone has any bright ideas, please comment :).