Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF In my question over at Pull every other row, starting at 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the procedure using the code into place and tested from the calling app I get an error.


  SELECT error: SELECT failed because the following SET options have
  incorrect settings: 'CONCAT_NULL_YIELDS_NULL'


In testing if I set CONCAT_NULL_YIELDS_NULL OFF the procedure will fail when called from SSMS but if I set CONCAT_NULL_YIELDS_NULL ON it works fine, further testing is showing the calling app to explicitly be setting CONCAT_NULL_YIELDS_NULL OFF and I can not override that. So I need to figure out how to update the code block to prevent this problem.

SELECT @TreeLocationStructure = STUFF (
    (
        SELECT ' -&gt; ' + TreeLocation
        FROM (
            SELECT rn = ROW_NUMBER()
            OVER (ORDER BY ID), TreeLocation
            FROM #TreeSplit
        ) AS x(rn, TreeLocation)
        WHERE rn % 2 = 1
        ORDER BY rn FOR XML PATH,TYPE
    ).value('.[1]','nvarchar(max)'), 1 ,4, ''
);
Try changing your procedure to:

ALTER PROCEDURE dbo.ProcedureName
  @Params ...
AS
BEGIN
  SET NOCOUNT ON;
  SET CONCAT_NULL_YIELDS_NULL ON;

  ... the rest of your code ...
END
GO


You might also want to get in touch with the vendor of the software, as changing this setting to OFF has been deprecated since SQL Server 2005, and soon the setting will have no effect or cause an error:

http://technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx