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!
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
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.
