Messing around with the CRM Database

Today I encountered an interesting bug in CRM: impossible to delete an invoice, coming along with the nice “Generic SQL Error”.
Digging a little, I quickly found that the SQL error was related to the foreign key constraint between InvoiceBase and InvoiceExtensionBase, namely.

The delete statement conflicted with the REFERENCE constraint “FK_InvoiceExtensionBase_InvoiceBase”. The conflict occurred in the database…, table…, column ‘InvoiceId’.

Counting records of both these tables  in the DB, I found different results… Hmmm

It just so happened that for performance reasons, my customer recently decided to transpose some plugin code directly in SQL code, and that included the creation of invoices. Quite crazy you might say, ok let’s not start this debate, as we are all fully aware of the risks (especially with CRM 2013 where the extension tables simply disappear…). To achieve that task, he run the SQL profiler and executed all the tasks he wanted to have in SQL. He then reorganized the logged SQL commands in to a few stored procedures. Except that he went probably a bit too far, as he inserted the invoiceid into the table InvoiceExtensionBase, as well.

Si what I had was records in the table InvoiceExtensionBase, starting from the day the plugin was replaced by the stored proc. And the consequence was that it was then impossible to delete an invoice. Interesting conclusion then: since the invoice entity had not been customized, the CRM platform simply did not insert anything into the extension table. So deleting all the records from InvoiceExtensionBase solved the problem.

Leave a Reply

Your email address will not be published. Required fields are marked *