ANSI_WARNINGS Problems

I've got a set of stored procedures that work together to accomplish a single goal.  I did things that way to make everything easier to unit test, and it worked like a charm.

Unfortunately, during actual testing a really strange error started occurring.  The last stored procedure called in the process (it's called by the "master" stored procedure) wasn't actually executing.  I added logs in everywhere and couldn't figure it out until a coworker suggested wrapping it in a TRY/CATCH.  Doing that exposed the issue, this guy: UPDATE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Does that make sense to you?  No?  Good, I don't feel so bad now.  It didn't make sense to me either.  Fortunately, I work with an awesome team and one of the database guys figured it out pretty quickly.  It turns out that the stored procedure that wasn't getting called (we'll refer to it as "dbo.UpdateRecords") needed to have ANSI_WARNINGS set to ON: SET ANSI_WARNINGS ON.  Additionally, the calling stored procedure (we'll refer to it as "dbo.DoItAll") needed to set ANSI_WARNINGS to on right before calling dbo.UpdateRecords.  So the end result is like this:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS OFF
GO
CREATE PROCEDURE dbo.DoItAll
AS
BEGIN
    ...
    SET ANSI_WARNINGS ON
    EXEC dbo.UpdateRecords
    ...
END

SET ANSI_NULLS, ANSI_WARNINGS ON
GO
CREATE PROCEDURE dbo.UpdateRecords
AS
BEGIN 
...
END