表适配器查询给出在 SQL Server Management Studio 中无法重现的错误

Question

我正在维护在 Visual Studio 的表适配器设计器中定义的一些查询，这些查询在 Windows 窗体应用程序 (.NET 2.0) 的一些报告中使用。 当我运行应用程序并执行特定查询时，出现错误：将表达式转换为数据类型smallmoney 时出现算术溢出错误。我很惊讶，因为查询应该产生相当小的数量，所以我使用 SQL 探查器捕获查询，并在 SQL Server Management Studio 中运行完全相同的查询（显然在同一个数据库上）。这里查询运行没有问题，小钱是“33.00”；不在 214,748.3647 边界附近。

为了使调试复杂化，这个问题只发生在客户端的 QA 环境中，并且在本地无法重现（并且由于法律原因无法将数据库复制到开发环境）。这使得调试周期非常慢，因为在客户端环境中构建和部署新版本需要长达 30 分钟的时间，因此我非常感谢一些提示，这些提示将使我能够通过尽可能少的实验来查明这个问题。在 SQL Studio 中摆弄查询对我没有多大帮助，因为我无法让它产生相同的错误。

以下是查询：

SELECT        CONVERT(varchar, Events.Occurred, 102) AS Day, Users.Name, COUNT(*) AS Deleted_Invoices, SUM(i.TotalExVat + i.TotalVat) AS Total
FROM            Events WITH (nolock) INNER JOIN
                         Users WITH (nolock) ON Events.UserID = Users.UserID INNER JOIN
                         Types AS t WITH (nolock) ON t.TypeID = Events.TypeID INNER JOIN
                         InvoicesEvents AS ie ON ie.EventID = Events.EventID INNER JOIN
                         Invoices AS i ON i.InvoiceID = ie.InvoiceID
WHERE        (Events.Occurred BETWEEN @startDate AND @endDate) AND (t.Name = 'InvoiceDeleted')
GROUP BY CONVERT(varchar, Events.Occurred, 102), Users.Name
ORDER BY Day, Users.Name

TotalExVat 和 TotalVat issmallmoney not null。数据表中的“Total”字段映射到“System.Decimal”。我可以尝试在表达式中将小钱转换为金钱，但当它在 SQL Studio 中运行良好时，为什么我必须这样做呢？

我得到的例外是： 异常类型：System.Data.SqlClient.SqlException 异常消息：将表达式转换为数据类型smallmoney 时出现算术溢出错误。

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at ... (calling code)

更新

将 SUM(i.TotalExVat + i.TotalVat) 更改为 SUM(i.TotalExVat) + SUM(i.TotalVat) 消除了错误，但我仍然不明白为什么，因为没有在产生的结果中小钱溢出。

更新2

新问题。现在，smallmoney 铸造问题消失了，但现在我遇到了超时问题。如果在 SSMS 中运行，同一报告中使用的另一个查询大约需要 5-6 秒。如果在表适配器中运行，则会在 10 分钟后超时。其他查询按预期运行，产生与 SSMS 中相同的结果。这支持了我的怀疑，即当我的表适配器尝试查询数据库时，出现了一些问题。

更新 3

这开始变得奇怪了。 Smallmoney 问题查询是用于生成报告的一系列查询中的第五个查询。在应用第一个更新中提到的修复后，我在第一个查询中遇到超时。当小钱在后面的查询中溢出时，该查询运行没有问题。 原因可能是什么？

当smallmoney查询给出错误时运行的查询在其工作时不运行：

SELECT        u.Name AS Username, rea.Text AS DeleteReason, COUNT(*) AS DeletedRegistrations, SUM(r.Shipments) AS DeletedShipments
FROM            RecordingsEvents AS re WITH (nolock) INNER JOIN
                         Events AS e WITH (nolock) ON e.EventID = re.EventID INNER JOIN
                         Reasons AS rea WITH (nolock) ON rea.ReasonID = e.ReasonID INNER JOIN
                         Users AS u WITH (nolock) ON u.UserID = e.UserID INNER JOIN
                         Recordings AS r ON r.RecordingID = re.RecordingID
WHERE        (rea.Category = 'DeleteRecording') AND (e.Occurred BETWEEN @startDate AND @endDate)
GROUP BY u.Name, rea.Text
ORDER BY Username, MAX(rea.SortOrder)

如果我限制smallmoney查询给出结果30.0的日期间隔，则上述查询有效。如果我将日期间隔扩大到小钱查询也曾经失败的时间段，我就会超时。当smallmoney 查询失败时，在smallmoney 查询之前运行的查询运行正常时，如何获得超时？ 在 SSMS 中运行所有查询都按预期工作。 顺便说一句，查询同步运行。

Answer 1

你能看看两者的执行计划吗？ （可从下面检索）

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%SELECT        CONVERT(varchar, Events.Occurred, 102) AS Day, Users.Name, COUNT(*) AS Deleted_Invoices, SUM(i.TotalExVat + i.TotalVat) AS Total%' and attribute='set_options'

我想知道其中一个是否最终制定了一个 SUMS 记录的计划，但后来最终被过滤掉了。

Answer 2

在查询中，您是否尝试将值转换为 MONEY？

就像

SUM(CAST(i.TotalExVat AS MONEY) + CAST(i.TotalVat AS MONEY))

当加法发生时，这些值似乎保持在相同的类型，所以任何溢出都会破坏它。

SMALLMONEY 看起来确实是一个非常小的使用类型。

看看这个例子

DECLARE @table TABLE(
        Value SMALLMONEY
)

INSERT INTO @table SELECT 200000
INSERT INTO @table SELECT 200000
INSERT INTO @table SELECT 200000
INSERT INTO @table SELECT 200000

--IS FINE
SELECT SUM(CAST(Value AS MONEY) + CAST(Value AS MONEY))
FROm    @table

--BREAKS
SELECT SUM(Value + Value)
FROm    @table

Answer 3

值得检查的是，更改 SSMS 的会话属性以匹配 .net 连接的会话属性是否能让您复制错误。

特别值得关注的是 ARITHABORT，我发现它会导致与您过去描述的问题类似的问题。默认情况下，我相信 SSMS 连接将 arithabort 设置为打开，而 .net 将其设置为关闭。

您可以使用 SET ARITHABORT [ ON | ] 更改 SSMS 中的属性。 OFF] 命令。您可能需要检查探查器跟踪以确认 .Net 环境中的设置。