November 12, 2015 at 9:52 am
When I'm writing procedures I tend to assume that CONCAT_NULL_YIELDS_NULL is ON, and indeed I rely on that in places. Most of our client connections set it on the connection, our SSMS connections are set to default it, etc, but we have a database default value of OFF (I know it's deprecated but I don't fancy the pain of going through all our procedures right now) and we have some legacy procedures that explicitly set it to OFF at the start. These procedures were written against SQL2000 by developers who were not database specialists, so some of our overnight batch jobs etc may depend on this setting and it's a big job (again I know it will have to be done at some point) to work out whether it's there for a purpose or due to laziness / lack of knowledge.
I've now added a filtered index to one of our tables. This adds the requirement that when I insert / update / delete on that table, I must have SET CONCAT_NULL_YIELDS_NULL turned ON. Fine by me, except that some of the procedures that manipulate this table are called by those ones that explicitly set it to OFF. These ones then result in an error:
Msg 1934, Level 16, State 1, Procedure Consolidation, Line 70
UPDATE failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'. 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.
I have added SET CONCAT_NULL_YIELDS_NULL ON to the start of both of my chains of procedures that manipulate the table. I had two errors before - one from the "Mapping" procedure (which calls lots of other procedures) and one from the "Consolidation" procedure which also calls other procedures below it. In the case of the Mapping procedure this has caused the error messages to go away, but I still get the above error from the Consolidation proc even when its definition starts with this setting. I can't make it go away unless I set the option within the proc that CALLS the Consolidation proc. I'd prefer to only change the procedures that reference the table in question for the moment rather than all the ones that may result in a call to one of those procedures. None of the sub procedures explicitly set it to OFF.
Any ideas?
-------------------------------Oh no!
November 12, 2015 at 12:36 pm
Explicitly set it ON immediately before DELETE/INSERT/UPDATE statements and OFF immediately afterward?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 13, 2015 at 2:48 am
ScottPletcher (11/12/2015)
Explicitly set it ON immediately before DELETE/INSERT/UPDATE statements and OFF immediately afterward?
You would think that would work, but at the same time you'd think that doing it at the start of the proc would do the same job assuming nothing in between sets it back, and if I set it one level higher it works, so there can't be anything setting it back... I don't need it set back within the proc either and there are a LOT of operations on that table in some of the procs.
I'm thinking maybe it's a glitch and there is no reason. Either way I'm just working around it for now - adding the directive to any procs that can call this one (around the call) basically the standard way of calling this proc may change from "EXEC Consolidator" to "SET Concat... EXEC Consolidator... SET Concat..."
Thanks for the response.
Kev
-------------------------------Oh no!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply