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.

CRM 2011: trigger a plugin from a stored procedure

No, it’s not possible, you’re dreaming. Or is it? Well, sort of. What’s possible is the following:

  1. I have created an entity called “new_pluginlauncher”. I have made a plugin firef upon the creation of a record of this entity, from which  I can do anything, depending on the parameters passed when creating the record. I can do things such as win or reopen a quote, create an order, close a case, well whatever really. As long as the correct guids and parameters are set upon record creation.
  2. I have created an SSIS package, based on Kingswaysoft,  that does that: create a record of that new_pluginlauncher entity
  3. I have deployed the SSIS package appropriately in the SQL Server instance
  4. I have created a stored procedure that calls that SSIS package

Bingo! From now on, I can access plugin code, on demand, from a SQL Server stored procedure.

And the best thing is: this is completely supported.

 

Here’s the code of the stored procedure. Pay attention to allowing xp_cmdshell.

ALTER Proc [dbo].[proc_CreatePluginLauncher]
@entitytype varchar(20),
@actiontype varchar(20),
@recordid varchar(50)
as
begin
declare @command varchar(8000),
@packagelocation varchar(1000),
@packagename varchar(200),
@param varchar(2000)
set @packagelocation = ‘SSISDBCRMCreateCRMPluginLauncher’
set @packagename = ‘CreatePluginLauncher.dtsx’
set @param = ‘select ‘ +
@entitytype + ‘ as EntityType, ‘ +
@actiontype + ‘ as ActionType, ‘ +
@recordid + ‘ as RecordGuid’
set @command =
‘dtexec /ISSERVER ‘ + @packagelocation + @packagename + ‘ ‘ +
‘/set package.variables[User::SQLCommand].Value;””‘ + @param + ‘”” ‘
—-now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @command
select @returncode
end