Sudden SQL error during INSERT/UPDATE operation (ARITHABORT)

Your database has been running fine for the longest time. All of a sudden INSERT or UPDATE operations fail with an error similar to the following.

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

Quick research tells you to simply add the following line right after you open a connection to the database or at the start of your query.

SET ARITHABORT ON

This seems to make things work again, but have you really solved the problem?

It turns out that this error is caused when you create an Indexed View. But I didn't do it? Well, if you run either the Index Tuning Wizard (SQL Server 2000) or Database Engine Tuning Advisor (SQL Server 2005), they will create what are called "Hypothetical Materialized Views" or index views. These views start with the prefix _hypmv. When the analysis is finished these views are supposed to be discarded. However, if for whatever reason these views are not deleted, any attempts to INSERT or UPDATE into the table(s) on which this indexed views were created, the error message relating to incorrect ARITHABORT setting will occur.

In my particular case, the reason why these views were not discarded was because my account had enough privileges to create these views but not delete them. Running the tuning tools would leave these indexed views behind without me realizing it.

So, if you see this error it may be that you have left behind some indexed views you did not create directly. Deleting them should get rid of the error.

Read full post...

SQL-equivalent to NVL2

I have been using T-SQL for as long as I can remember and one of the things I never found was an equivalent to Oracle's NVL2. Well, this time around I did not want to use a CASE statement or even the more involved COALESCE. I just needed a plain function that would evaluate if the given expression is null return this otherwise return this.

The following T-SQL function will provide the equivalent to NVL2, or at least something close enough. The trick is to use the sql_variant data type.

IF EXISTS (SELECT id
             FROM dbo.sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[isnull2]')
              AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION [dbo].[isnull2]
END
GO
CREATE FUNCTION [dbo].[isnull2]
(
    @expression sql_variant,
    @return_if_null_value sql_variant,
    @return_if_not_null_value sql_variant
)
RETURNS sql_variant
AS
BEGIN
    DECLARE @return sql_variant;
    IF (@expression IS NULL)
        SET @return = @return_if_null_value
    ELSE
        SET @return = @return_if_not_null_value
    RETURN @return
END

Please note that you will need to ensure you cast either the parameters you pass or the result to the desired type. This is specially true if you are calling the logic in a stored procedure or embedded query that is executed from a .NET or similar application via something like ADO.NET.

Read full post...