Table adapter query gives an error that is not reproducable in SQL Server Management Studio

Question

I am maintaining some queries defined in the table adapters designer in Visual Studio used in some reports in a Windows Forms application (.NET 2.0). When I run the application and execute a particular query I get an error: Arithmetic overflow error converting expression to data type smallmoney. I was surprised, since the query should produce rather small amounts so I captured the query with an SQL profiler and ran the exact same query in SQL Server Management Studio (on the same database obviously). Here the query runs without problems and the smallmoney is "33.00"; not anywhere near the 214,748.3647 boundary.

To complicate debugging this problem only occurs in the client's QA environment and is not reproducible locally (and the database cannot be copied to the development environment for legal reasons). This makes the debugging cycle very slow since building and deploying new versions in the client's environment takes up to 30 minutes, so I would very much appreciate some hints that will make me pinpoint this problem with as little experimentation as possible. Fiddling with the query in SQL Studio doesn't help me much, since I can't make it produce the same errors.

Here is the query:

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 and TotalVat is smallmoney not null. The "Total" field in the data table is mapped to "System.Decimal". I could try to cast the smallmoney to a money in the expression, but why would I have to do that when it runs fine in the SQL studio?

The exception I get is: Exception type: System.Data.SqlClient.SqlException Exception message: Arithmetic overflow error converting expression to data type 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)

Update

Changing SUM(i.TotalExVat + i.TotalVat) to SUM(i.TotalExVat) + SUM(i.TotalVat) eliminated the error, but I still don't understand why, since there is no smallmoney overflow in the produced results.

Update 2

New problems. Now the smallmoney casting problem is gone, but now I have a timeout problem. Another query used in the same report runs in about 5-6 seconds if run in SSMS. If run in a table adapter it times out after 10 minutes. Other queries run as expected producing the same result as in SSMS. This supports my suspicion that something rotten is going when my table adapter attempts to query the database.

Update 3

This is starting to get weird. The smallmoney problem query was fill query number five in a series of queries used to generate a report. After I applied the fix mentioned in the first Update I get timeouts in the first of the queries. That query ran without problems, when the smallmoney was overflowing in the later query. What could be the reason for that?

The query running when the smallmoney query gives and error and NOT running when its working:

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)

The above query works if I limit the date interval where the smallmoney query gave the result 30.0. If I expand the date interval to a period where the smallmoney query also used to fail I get a timeout. How can I get a timeout in a query that runs BEFORE the smallmoney query when it runs fine when the smallmoney query fails? Running all queries in SSMS works as expected. BTW the queries run synchronously.

Answer 1

In the query, have you tried casting the values to MONEY?

Something like

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

It would seem that when the addition happens, the values are kept in the same type, so any overflow will break that.

SMALLMONEY does seem like a very small type to use.

Have a look at this example

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 2

It's worth checking whether changing the session properties for SSMS to match those of your .net connection enables you to replicate the error.

In particular, it's worth looking at ARITHABORT, which I've seen cause similar issues to the one you describe in the past. By default, I believe SSMS connections set arithabort on, whereas .net sets it off.

You can change the property in SSMS using the SET ARITHABORT [ ON | OFF] command. You may need to examine a profiler trace to confirm the settings in the .Net environment.

Answer 3

Can you look at the execution plans for both? (Retrievable from the below)

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'

I wonder if one of them ends up with a plan that SUMS records that later end up getting filtered out.