question

TharinduWithana-3715 avatar image
0 Votes"
TharinduWithana-3715 asked cooldadtx commented

SQL Server Reporting : error converting data type nvarchar to numeric

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 (@LOB)
ORDER BY
[Service Item Group]

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

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

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 @LOB 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 @LOB 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.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@cooldadtx 1st of all.. many thanks for your answer. please check below my replies and appreciate your more inputs.
!122852-sql-report.jpg

I have attached pic also how we selecting @LOB
Do you have multiple columns and perhaps are using the wrong one? No we have only one column.
Since @LOB 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"
122903-sql-tabel.jpg



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.

0 Votes 0 ·
sql-report.jpg (16.6 KiB)
sql-tabel.jpg (35.2 KiB)
cooldadtx avatar image cooldadtx TharinduWithana-3715 ·

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.

0 Votes 0 ·

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?

0 Votes 0 ·
cooldadtx avatar image cooldadtx TharinduWithana-3715 ·

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.

0 Votes 0 ·

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
@LOB 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 (@LOB)
Order by [Service Item Group]

0 Votes 0 ·
Show more comments