SSRS error : "Invalid length parameter passed to the LEFT or SUBSTRING function."

Question

Hello everyone, I'm facing an issue with a simple Query, that works fine on SQL Server and also on SSRS. In my Select there's a column with this structure "BANKNAME - ServiceName" (for Example: "DEUTCHEBANK - CustomerRetail"). I needed to split this string to isolate both name and Service, so I tried the following:

LEFT(CustomerFullName, CHARINDEX('-', CustomerFullName) -2) as CustomerFullName

SUBSTRING(CustomerFullName,CHARINDEX('-', CustomerFullName)+2, 200) AS Service

on SQL it works great, but on Visual Studio it produces the following error message: An error has occurred during report processing. (rsProcessingAborted) Cannot read the next data row for the dataset 'DataSet1'. (rsErrorReadingNextDataRow) Invalid length parameter passed to the LEFT or SUBSTRING function. This seems a bit strange because we checked the CHARINDEX results and it is always > 0. we checked also for missing values, but there are no NULLs. One attempt I tried was to change the Column name from CustomerFullName to Bank, and it worked for few minutes. then it returned the same error. What am I missing? thanks in advance!

Answer 1

You need to rewrite your query to handle data exceptions. Here is an example:

Create table test

(ID   int,CustomerFullName  varchar(50) )

 
insert into test values 
 (1,'DEUTCHEBANK - CustomerRetail') 
 , (2,'CustomerFullName')
 ,(3,null)

 
 select *,
Case when CHARINDEX('-', CustomerFullName)>0 then LEFT(CustomerFullName, CHARINDEX('-', CustomerFullName)) else CustomerFullName end as CustomerFullName,
Case when CHARINDEX('-', CustomerFullName)>0 then SUBSTRING(CustomerFullName,CHARINDEX('-', CustomerFullName)+2, 200)  else CustomerFullName End  AS Service

from test


drop table test

Answer 2

Hi @Marco Onnis
Please check the source data has not been changed by other users when you are using in VS.

CHARINDEX will return 0 if no spaces are in the string and then you look for a left of -2 length. You could add conditionality like Jingyang Li mentioned in the code.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.