How can I ignore 'Arithmetic Overflow' related errors from within a data view?

Question

I have a complex data view that recursively links and summarizes information.

Each night a scheduled task runs a stored procedure that selects all of the data from the data view, and inserts it into a table so that users can query and analyze the data much more quickly than running a select statement on the data view.

The parent table consists of a few hundred thousand records and the result set from the export is well over 1,000,000 records in size.

For most nights the exportation process works without any trouble, however, if a user enters an incorrect value within our master ERP system, it will crash the nightly process because one of the decimal fields will contain a value that doesn't fit within some of the conversions that I have to make on the data. Debugging and finding the specific, errant field can be very hard and time consuming.

With that said, I've read about the two SQL settings NUMERIC_ROUNDABORT and ARITHABORT. These sounds like the perfect options for solving my problem, however, I can't seem to get them to work with either my data view or stored procedure.

My stored procedure is nothing more than a TRUNCATE and INSERT statement. I appended...

SET NUMERIC_ROUNDABORT OFF
SET ARITHABORT OFF

... to the beginning of the SP and that didn't help. I assume this is because the error is technically taking place from within the code associated with the data view.

Next, I tried adding two extended properties to the Data View, hoping that that would work. It didn't.

Is there a way that I can set these SQL properties to ignore rounding errors so that I can export my data from my data view?

I know for most of us, as SO answerers, our first inclination is to ask for code. In this case, however, the code is both extremely complex and proprietary. I know fixing the definitions that cause the occasional overflow is the most ideal solution, but in this circumstance, it is much more efficient to just ignore these type of errors because they happen on such a rare basis and are so difficult to troubleshoot.

What can I do to ignore this behavior?

UPDATE

By chance, I believe I might have found the root cause of the issue, however, I have no idea why this would be occurring. It just doesn't make since.

Through out my table view, I have various fields that are calculated. Since these fields need to fit in fields within the table that are defined as decimal (12, 5), I always wrap the view field statements in a CAST( ... AS DECIMAL(12, 5)) clauses.

By chance, I stumbled upon an oddity. I decided to see how SSMS "saw" my data view. In the SSMS Object Explorer, I expanded the Views->[My View]-Columns section and I saw that one of the fields was defined as a decimal (13, 5).

I assumed that I must have made a mistake in one of my casting statements but after searching throughout the code for the table view, there is no definition for a decimal(13, 5) field?! My only guess is that the definition that SSMS sees of the view field must be derived from resulting data. However, I have no clue how this could happen since I each field to a decimal(12, 5).

I would like to know why this is happening but, again, my original question still stands. How and what SET statement can I define on a table view that will ignore all of thee arithmetic overflows and write a null value in the fields with errant data?

FINAL COMMENTS

I've marked HeavenCore's response as the answer because it does address my question but it hasn't solved my underlying problem.

After a bit of troubleshooting and attempts at trying to get my export to work, I'm going to have to try a different approach. I still can't get the export to work, even if I set the NUMERIC_ROUNDABORT and ARITHABORT properties to OFF.

Answer 1

i think ARITHABORT is your friend here.

For instance, using SET ARITHABORT OFF & SET ANSI_WARNINGS OFF will NULL the values it fails to cast (instead of throwing exceptions)

Here is a quick example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_OverflowExample](
    [Value] [decimal](12, 2) NULL
) ON [PRIMARY]    
GO
INSERT [dbo].[tbl_OverflowExample] ([Value]) VALUES (CAST(9999999999.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[tbl_OverflowExample] ([Value]) VALUES (CAST(1.10 AS Decimal(12, 2)))
GO

--#### Select data without any casting - works
SELECT  VALUE
FROM    dbo.tbl_OverflowExample

--#### With ARITHABORT and ANSI warnings disabled - Returns NULL for 999999 but 1.10 as expected
SET ARITHABORT  OFF;
SET ANSI_WARNINGS OFF;
SELECT  CONVERT(DECIMAL(3, 2), VALUE)
FROM    dbo.tbl_OverflowExample
GO

--#### With defaults - Fails with overflow exception
SET ARITHABORT  ON;
SET ANSI_WARNINGS ON;
SELECT  CONVERT(DECIMAL(2, 2), VALUE)
FROM    dbo.tbl_OverflowExample

Personally though - i'd prefer to debug the view and employ some CASE /.../ END statements to return NULL if the underlying value is greater than the target data type - this would ensure the view works regardless of the connection options.

EDIT: Corrected some factual errors