SSRS错误：将数据类型nvarchar转换为int时出错。

Question

我将这个存储过程传递给SSRS生成报表，但一直遇到“错误：数据类型nvarchar无法转换为int”，而我甚至没有使用任何nvarchar类型的参数。

Alter proc dbo.spPullOrderRosa1
@Subentity int,
@BegShipDate date,
@EndShipDate date,
@Store varchar(150),
@State varchar(150)
as
begin

select OpOrID as OrID, OpID, concat(OrCuFirstName,' ',OrCuLastName) as CustomerName,
b.SoName as StoreName,OpPrSKU as SKU, OpQty,StLongName as StateName,
cast(OpShipDate as date) as ShipDate, 
cast(d.DeliveryDate as date) as DeliveryDate,
e.StyName as SubEntity

from csn_order..tblOrderProduct a with (nolock)
left join csn_order..tblOrder f with (nolock) on a.OpOrID = f.OrID
left join csn_order..tblStore b with (nolock) on a.OpSoID = b.SoID
left join csn_order..tblplState c with (nolock) on f.OrCuStID = c.StID
left join csn_order..tblDeliveryDate d with (nolock) on a.OpID = d.DeliveryOpID
left join csn_order.dbo.tblSubEntity e with (nolock) on b.SoStyID = e.StyID
where (OpCancelled = 0) and (b.SoID in (select SoID from csn_order..tblStore where SoName in (select * from STRING_SPLIT(@Store, ','))  )) 
and (StID in (select StID from csn_order..tblplState where StLongName in (select * from STRING_SPLIT(@State, ',')) )) 
and (StyID =  @Subentity) and (OpShipDate >= @BegShipDate and OpShipDate <= @EndShipDate)

结束

- Yiting Ma

---

你所有的ID字段（用于连接）和OpCancelled是什么类型？任何int和nvarchar值的比较都可能导致此类错误。 - Arvo

你的 SSRS 参数中有任何多选项吗？例如，如果 @Subentity 是一个多选项，则 ID 将作为逗号分隔的字符串 varchar(max) 传递。例如："12, 34, 16"。 - codeulike

从管理工具中，存储过程是否正常运行？错误只是在 SSRS 中发生吗？ - codeulike

State和Store是多选的，但我使用了string_split来处理它们。 - Yiting Ma

它在SQL中可以工作，但在SSRS中不行。 - Yiting Ma

Answer 1

我肯定不会将其视为随机事件，因为当满足特定条件时，您不希望它再次弹出。以下是我尝试缩小范围的几个方法：

1）您使用LEFT JOINs，它可能会在您期望为整数（如QTY）的字段中留下NULL值，请尝试在SELECT子句中用COALESCE语句包装它们。

SELECT COALESCE(OpQty, 0) as OpQty, ...

2) SSRS可能会错误地猜测数据类型，如果前几个值是数字，则可能会将字符字段视为整数。找出生成错误的字段，并明确将其转换为NVARCHAR，这样SSRS就不会尝试将其用作整数。

最初的回答

SELECT CONVERT(nvarchar(50), OpPrSKU) as SKU, ...

3) 如果在 DETAIL 组的末尾添加了“TOTAL”字段，则 SSRS 可能会尝试对不是数字的字段进行数学运算。这种可能性较小，但仍值得寻找。

但重要的是要弄清楚哪个字段生成了错误，以便您可以将注意力集中在它上面。

编辑：如果您使用的是 SQL Server 2012 或更高版本，则还可以在报表中的数据集中使用 EXECUTE WITH RESULT SETS 子句明确设置返回的数据类型。一个很好的例子是http://www.sqlservercentral.com/blogs/sqlstudies/2016/01/14/what-is-result-sets/

请注意，这样做的缺点是，如果您更新存储过程定义以包括更多列，则必须记住跟踪并更新所有使用它的报表。如果您的设置不更改 SP 定义，则这不是问题，并且是告诉 SSRS 正确类型的最可靠方法，但对我而言，在 SP 内部进行类型转换在长期内更加灵活，并且对于我遇到的每种情况，都足以满足 SSRS。

- Robert Sheahan

---

我同意存储过程本身没有问题，我认为问题在于SSRS错误地猜测了数据类型，我建议修改存储过程以“诱导”SSRS正确地猜测全部数据的类型。 - Robert Sheahan

但是在这种情况下，为什么两个相同的rdl文件会返回不同的结果呢？ - Yiting Ma

我不确定确切的机制，但是 SSRS 似乎在您首次定义数据集并使用它来估计字段类型时抓取数据样本。如果在定义第一个 RDL 后发生更改（例如表中有新数据，甚至只是不同的排序），则新的 RDL 可能会有更好的猜测。再次强调，因人而异，但如果我仍然有“损坏”的 RDL，我会尝试找出哪个字段导致了问题。一旦设置了数据类型，RDL 不太可能“损坏”，但是新报告在缺少显式类型的情况下可能会再次猜错。 - Robert Sheahan

我找到了原因。在传递sp的数据集中，参数值没有包含在方括号中。这导致在HTML代码中显示为@subrntity而不是parameter!@subrntity.value。在我改变这个之后，我的rdl文件就正常工作了！ - Yiting Ma

我认为你对猜测问题是正确的。这只是因为SSRS无法真正确定输入，因为我没有将参数指向任何实际值。所以它将@subentity视为nvarchar而不是int的$subentity.value。 - Yiting Ma

Answer 2

仅仅想要提供我的意见（知道这篇文章已经超过两年了），我并不是说这个方法适用于所有人。我曾经遇到和OP一样的问题。

我创建了一个新的rdl文件，并复制了所有的对象。

- Daniel L. VanDenBosch