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.

CRM Lesson Learned: be careful what you update...

So, I thought it would be nice to start a new series, "Lessons Learned". Well, I'm not sure it's gonna be more than one post, but it sounds nice right? :)

Anyway, todays lesson learned is "be careful what you update". I was recently working on a callout for recurring appointments, a much requested feature for MS CRM. In short, when an appointment was completed a new appointment was created based on some settings in the regarding account record. The regarding account record was then updated with last visit date.

However, on testing this callout I found out that every time the appointment was closed and a new one was created (through the callout), the owner of the two appointments changed. It was set to the owner of the regarding account record.

Now, the account entity has a parental relationship with the appointment entity. This means that e.g. a share or assign of the parent account is cascaded to the underlying appointment. That would explain the owner change of the appointments.

But I didn't do any assign or anything that would trigger the cascading rules, I only updated a custom datetime field, or so I thought...

I searched through my code to see if I assigned anything to the ownerid of the account record, but there was nothing. Finally, after some fruitless attempts I went to every line of my code to see what could be wrong and then it struck me. I retrieved the parent account and the columnset contained the accountid, cwr_lastvisitdate and the ownerid! I needed the ownerid for a check.

After I retrieved the account I only updated the cwr_lastvisitdate field and then I called crmservice.Update() on it.

However, when you call Update on an entity it updates all the fields contained in the entity. And the entity also contained the ownerid! And apparently, because the ownerid was updated (even though it was not changed!), MS CRM interpreted it as an Assign. The rest is history. The assign triggered the cascading rules and the owner of all underlying records was changed to the owner of the account.

So the lesson learned: be very careful about which columns you retrieve when you want to update the retrieved entity. You might get some unexpected results :)

Happy coding and stay mobile!

CRM Error Message of the Day: 0x80131500

Ok it's been a while, but here's another CRM Error Message of the Day.

This time it's related to the Bulk Import Wizard. I was trying to import some contacts from a .csv file I got from a customer. Some of them imported successfully and other failed. The reason was "Error" and the reasonid 0x80131500.

Searching for this number on Google didn't return any relevant hits. I did find this article: Save time when you import leads, contacts, and accounts. Going through all the recommendations I ended up here, Importing dates. I quote:

"Dates must be in the format specified in Microsoft Dynamics CRM on the Calendar
tab of the System Settings page."

I did have a date field in my .csv file, so I started investigating. The date format in the System Settings was set to MM/dd/yyyy. However, when I tried to import a record with a date in this format it failed with 0x80131500. Note: this was a date like 12/23/2000. So 23 for a day would be fine, but 23 for a month would obviously fail.

When I changed the date in the import file to 23/12/2000, i.e. dd/MM/yyyy it went fine! Strangely enough, when I opened the just imported contact, the date I just imported was shown in the dd/MM/yyyy format, despite the fact that the date format in the system settings was MM/dd/yyyy.

I tested the same behavior on another CRM system with a different date format, but exactly the same happened. In other words, using the date format from the System Settings as described in the abovementioned article doesn't work!

Ofcourse, a colleague of mine told me to always use the yyyy/MM/dd date format, that always works and indeed, it did work. :)

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 :).