MS CRM Rollup 8 and 9 fail with Invalid length parameter passed to the LEFT or SUBSTRING function.

While installing MS CRM Rollup 8 or MS CRM rollup 9, we received an error that looked like this:

—————————
Microsoft Dynamics CRM 4.0 Update Rollup 9
—————————
Action Microsoft.Crm.Setup.Common.Update.DBUpdateAction failed.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Cannot insert the value NULL into column ‘InvoiceNumber’, table ‘sdia_MSCRM.dbo.InvoiceBase’; column does not allow nulls. UPDATE fails.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

The statement has been terminated.

—————————
Retry   Cancel  
—————————

It turns out this error is caused by an invoice having an invalid invoice number. By default, MS CRM gives invoices numbers that look something like this: INV-06881-39HRRD

In our CRM project, we had some invoice number formats being inserted manually by a remote Events Booking system we have written for the web (cool application actually). This used slightly different format invoice numbers intentionally. The solution to the problem was to either remove or renumber in the same format the invoice records. Once your invoice numbers are all as CRM expects them, then you can install either or both updates.

This is a SQL script that may help:


Execute this SQL script to generate correct invoice number, specifying 
correct length for your invoice number format 

(verify in your CRM parameters Automatic Numbering for invoice): 
@InvoiceNumberPref = 'INV' 
@invoicePART1Len = 5 
@invoicePART2Len = 6 
In this case : INV-02365-6Y5TRE 



-- Start Script ---------------------------------------- 
DECLARE @InvoiceNumberPref nvarchar(5) 
SET @InvoiceNumberPref = 'INV' 
DECLARE @invoicePART1Len int 
SET @invoicePART1Len = 5 
DECLARE @invoicePART2Len int 
SET @invoicePART2Len = 6 

DECLARE @i int 
DECLARE @j int 
DECLARE @n int 
DECLARE @strnum nvarchar(10) 
DECLARE @RandomChars nvarchar(10) 
DECLARE @RandomInvoiceNumber nvarchar(20) 

SET @j = 1 

DECLARE @invoiceid nvarchar(50) 
DECLARE @invoicenumber nvarchar(20) 

DECLARE cursor_invoices CURSOR FOR 
SELECT CONVERT(NVARCHAR(50),invoiceid), invoicenumber FROM dbo.invoice 
order by createdon 

OPEN cursor_invoices 

FETCH cursor_invoices INTO @invoiceid, @invoicenumber 

WHILE @@FETCH_STATUS = 0 
BEGIN 

SET @strnum = CONVERT(nvarchar(10), @j) 
SET @j = @j + 1 
SET @strnum = REPLICATE('0', @invoicePART1Len-LEN(@strnum)) + @strnum 


SET @RandomChars = '' 
SET @i = 0 
WHILE @i < @invoicePART2Len 
BEGIN 
SET @n = ASCII(0)+CEILING(RAND()*(ASCII('Z')-ASCII(0))) 
--exclude value range 58-64 
IF @n>ASCII(9) AND @n<ASCII('A') SET 
@n=ASCII(0)+CEILING(RAND()*(ASCII(9)-ASCII(0))) 
SET @i = @i + 1 
SET @RandomChars = @RandomChars + NCHAR(@n) 
END 

SET @RandomInvoiceNumber = @InvoiceNumberPref + '-'+@strnum+'-'+@RandomChars 

PRINT @invoiceid + ' ' + @invoicenumber + ' ' + @RandomInvoiceNumber 

UPDATE invoicebase 
SET invoicenumber = @RandomInvoiceNumber 
WHERE invoiceid = @invoiceid 

FETCH cursor_invoices INTO @invoiceid, @invoicenumber 
END 

CLOSE cursor_invoices 
DEALLOCATE cursor_invoices 

Check out our website for more CRM integration into DotNetNuke.

Digg This

Leave a Reply

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