This is a strange issue..
We are receiving Divide by zero errors that are causing SQL Server to terminate transaction and crashing the test website.
Arithmetic Abort is set to FALSE along with ANSI warnings and all the other defaults... No code change has happened so it is not explicitly set in the code either.
This is only happening on most of the databases on the server that contain very similar data and the same metadata, but not all.
This has started happening after I performed a in place upgrade of our 2008 test server to 2012. (the issue occurs in both compatibility modes)
If I explicitly turn ARITHMETICABORT to false inside the query window it runs fine... something seems to be ignoring the database wide settings. this happens from within management studio or from code
here is the first piece of code we detected it in, for reference, but I think this is not related to the code
SELECT sm.MarkupId, sm.WebSiteCode, sm.CurrencyCode, sm.OfferTitle, sm.Priority, sm.MarkupPercent, sm.MarkupDownAmount, sm.Cumulative, sm.AllowAdditionalDiscounts, sm.StartDate, sm.EndDate, sm.DayOfWeek, sm.StartTime, sm.EndTime, sm.DepartureStartDate, sm.DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, ISNULL(sb.CommissionPercent, 0) AS CommissionPercent, eg.Title AS EstabGroup, d.DomainName, sm.DurationFrom, sm.DurationTo, sm.BookingTotalFrom, sm.BookingTotalTo, ActualCommission = CASE WHEN ISNULL(sb.CommissionPercent, 0) = 0 THEN CAST(( ( sm.MarkupPercent - 100 ) / sm.MarkupPercent ) * 100 AS DECIMAL(18, 2)) ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - ( 100.0 - sm.MarkupPercent ), 2) END, sm.BoardTypeId, BoardTitle = ISNULL(b.Title, '') FROM SupplierMarkup_tbl sm INNER JOIN Domain_tbl d ON d.DomainId = sm.DomainId LEFT JOIN Supplier_tbl s ON sm.SupplierId = s.SupplierId LEFT JOIN acomEstabGroup_tbl eg ON sm.EstabGroupId = eg.EstabGroupId LEFT JOIN SupplierBookedItemType_tbl sb ON sb.SupplierId = s.SupplierId AND sb.WebsiteCode = sm.WebSiteCode AND sb.BookedItemTypeId = 17 LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = sm.BoardTypeId WHERE 1 = 1 UNION SELECT CASE WHEN cm.IsExtranet = 1 AND cm.GrossAdjustment = 1 THEN -2 WHEN cm.IsExtranet = 1 AND cm.NetAdjustment = 1 THEN -1 ELSE 0 END AS MarkupId, '' AS WebSiteCode, '' AS CurrencyCode, mo.Title AS OfferTitle, -1 AS Priority, cm.MarkupPercent * 100 AS MarkupPercent, 0 AS MarkupDownAmount, cm.Cumulative, cm.AllowAdditionalDiscounts, cm.StartDate, cm.EndDate, cm.DayOfWeek, cm.StartTime, cm.EndTime, cm.CheckInStartDate AS DepartureStartDate, cm.CheckInEndDate AS DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, 0 AS CommissionPercent, '' AS EstabGroup, d.DomainName, cm.DurationFrom, cm.DurationTo, cm.BookingTotalFrom, cm.BookingTotalTo, ActualCommission = 0, cm.BoardTypeId, BoardTitle = ISNULL(b.Title, '') FROM conContractEstabMarkup_tbl cm INNER JOIN Domain_tbl d ON d.DomainId = cm.DomainId LEFT JOIN Supplier_tbl s ON s.SupplierId = 97 LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = cm.BoardTypeId LEFT JOIN dbo.MarkupOffer_tbl mo ON mo.MarkupOfferId = cm.MarkupOfferId WHERE cm.EstabId IS NULL AND cm.Deleted = 0 ORDER BY Priority, Supplier
This is not strictly an answer as I cant tell why this would happen, but I have found the root cause. Hopefully someone can tell me why this behavior happens or if it is a bug ?
The error happens when the 2nd statement after the UNION doesn't return any rows.
not seen one like this in a while !