SQL Server Reporting : error converting data type nvarchar to numeric

Question

SQL Server Reporting : error converting data type nvarchar to numeric

Tharindu Dharshana Withana 1

We have SQL Server Reporting Services running on our live system. But when we trying to move same reports in to new database and new reporting server, then reports starting to fail with below error.

Highly appreciate if someone can help

SQL Server Reporting Services is showing this error
___________________________________________________________________________________________________________________-
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset 'DSProductJournal'. (rsErrorReadingNextDataRow)
Error converting data type nvarchar to numeric.

SQL query
_______________________________________________________________________________________________________________________--
SELECT
COMPANY, [Table], [Service Item Group],
(CAST([Line No] AS char(10)) + [Order No_]) AS [Line No],
[Item No_], [Shelf No_], Quantity, [Serial No_], [Order No_],
Name, [Weight], Dimension, [Satus/OwnerShip], Condition,
[E.T.A. / Rec’d Date], [Cost Amount (Actual)], [Cost Curr],
[Costing Completed], [Currency Code], [Exchange Rate Amount],
Special, [(Sales Price).(Unit Price)], [(Sales Price).(Minimum Quantity)],
[COSTED S/P CDN], [Location Code], [Profit _] AS [Profit],
[Description Sales Price] AS [DescriptionSalesPrice],
[Currency Code.Sales Price]
FROM
dbo.V_ZItemLedgerEntryPurchLineUnionAll
WHERE
[Shelf No_] IN (@LisOsorio )
ORDER BY
[Service Item Group]

____________________________________________________________________-
(@LisOsorio ) is a user input. we noticed that as soon as we remove "WHERE
[Shelf No_] IN (@LisOsorio )" and reportstart running. please help

1 answer

Your answer

Answer 1

Michael Taylor 60,161

The error is pretty clear in this case I think. SQL found an NVARCHAR value (in column Shelf NO I'd wager and tried to convert it to NUMERIC and that conversion failed. Therefore somewhere in your data for the DSProductJournal you have a type mismatch. If you take the query and run it directly in SQL where @LisOsorio is a variable that you declare in SQL just like it is declared as a parameter in SSRS then it should fail.

I notice that in your WHERE clause you're using the column Shelf No but in the select statement you're using Shel No_. Do you have multiple columns and perhaps are using the wrong one?

Since @LisOsorio is user input is it possible the report parameter is declared as text and not an integral value so it is failing the call? Or do you potentially expect people to enter values such as "4,5" when they want to match shelf # 4 or 5? In that case it might be better to enable the option to allow multiple values instead.

Tharindu Dharshana Withana 1 Reputation point

2021-08-12T21:00:42.36+00:00

@Michael Taylor 1st of all.. many thanks for your answer. please check below my replies and appreciate your more inputs.
!

I have attached pic also how we selecting @LisOsorio
Do you have multiple columns and perhaps are using the wrong one? No we have only one column.
Since @LisOsorio is user input is it possible the report parameter is declared as text and not an integral value so it is failing the call? True. its declared as text . we are expect people to enter values such as "WW"

In that case it might be better to enable the option to allow multiple values instead.: Alardy enabeled
Also we have two server. one testing and one live. exactly the same report work in live one but not in test one.
Tharindu Dharshana Withana 1 Reputation point

2021-08-12T21:23:40.617+00:00

The error is pretty clear in this case I think. SQL found an NVARCHAR value (in column Shelf NO I'd wager and tried to convert it to NUMERIC and that conversion failed. Therefore somewhere in your data for the DSProductJournal you have a type mismatch
For above statement is that possible to change it to TEXT as it not NUMERIC anyway? also I'm wondering where to find this " DSProductJournal" . do you think that a query or procedure?
Michael Taylor 60,161 Reputation points

2021-08-13T02:08:32.847+00:00
You said this works on another server so it would be odd that the report is missed up now. However if you have typed your parameter as a string then you shouldn't run into any conversion issues. But to confirm the datatype of the Shelf No_ in the DB is NVARCHAR right?

In the report you're using a dropdown. Can you confirm the configuration of this dropdown? In most cases you assign the display of the dropdown to a text field (e.g. WW) while the value expression is an ID (because you're pulling it from a lookup). Thus the parameter itself would end up being an integral value if you're binding to a lookup table. However you can modify the parameter to use the textual value as the value as well. If that is applicable here.

But can you try the actual query that your report is going to use.

DECLARE @lob NVARCHAR(20) = `WW` SELECT ... FROM ... WHERE [Shelf No_] IN (@lob) ORDER BY...

This will help ensure there isn't an odd value in the DB table that is causing the issue.
Michael Taylor 60,161 Reputation points

2021-08-13T02:09:56.087+00:00
You can definitely force a parameter to a specific type if that is really the issue but in this case it seems like it should be correct.

... WHERE [Shelf No_] IN (CONVERT(NVARCHAR(20), @lob))

But that would be redundant if the parameter is actual a textual value already.
Tharindu Dharshana Withana 1 Reputation point

2021-08-13T18:28:32.977+00:00

Not no luck,
so I start to user below procedure .

But now getting issue with passing multiple values to the parameter

EXEC dbo.P_J_External_PRD
"WW","MT"

Says Procedure or function P_J_External_PRD has too many arguments specified.

CREATE OR ALTER PROC dbo.P_J_External_PRD
@LisOsorio as nvarchar(1000)
As

DROP TABLE P_J_External;

SELECT COMPANY, [Table], [Service Item Group],( cast([Line No] as char(10)) +[Order No_]) as [Line No], [Item No_],[Shelf No_], Quantity, [Serial No_], [Order No_], Name, [Weight], Dimension, [Satus/OwnerShip], Condition,
[E.T.A. / Rec’d Date], [Cost Amount (Actual)], [Cost Curr], [Costing Completed], [Currency Code], [Exchange Rate Amount], Special,
[(Sales Price).(Unit Price)], [(Sales Price).(Minimum Quantity)], [COSTED S/P CDN],[Location Code],[Profit _] as [Profit],[Description Sales Price] as [DescriptionSalesPrice],[Currency Code.Sales Price]
INTO P_J_External
FROM dbo.V_ZItemLedgerEntryPurchLineUnionAll

Select *
FROM P_J_External
WHERE [Shelf No_] in (@LisOsorio )
Order by [Service Item Group]
Tharindu Dharshana Withana 1 Reputation point

2021-08-13T18:38:51.203+00:00

also above error shows bcz of it
Michael Taylor 60,161 Reputation points

2021-08-13T18:43:52.823+00:00

You cannot use the IN clause with parameters in SQL like that. The recommended approach is to use a table value parameter which will then work. Alternatively it was common to accept a single string parameter and then use a UDF to split the string (although newer versions of SQL have a function for this now). But honestly I don't think this is the right solution. If you can do it in a sproc then it'll work as a raw query.

Additionally, creating a table in a sproc and then dropping it at the end is wasteful. It doesn't actually accomplish anything over simply selecting the data. The problem is with the data here.

Please show a screenshot of the declaration of your @LisOsorio parameter in the report.

Also please show the declaration for the Show No_ column. As I mentioned earlier your original query was using Show No in the WHERE clause but Show No_ in the select. In the select that column was text but in the WHERE clause it isn't the same column so it seems like you have 2 columns in your table and one isn't the correct one. Please confirm if this is true or not. If it is then change your query in the report to match the SQL query that is working WHERE [Shelf No] IN (@LOB)

Share via

SQL Server Reporting : error converting data type nvarchar to numeric

1 answer

Your answer