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 *