Sql-server – Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF

sql-server-2008-r2t-sql

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 ' -> ' + 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, ''
);

Best Answer

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

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.

Sql-server – Convert all ntext columns to nvarchar(max)

Ok, I've re-written your SP for you with QUOTENAME and the schema like @MartinSmith suggested. Although I'm not quite sure why you are using an SP for this. It would seem like a stand alone piece of code without the SP wrapper would make more sense for something like this.

You use QUOTENAME to deal with odd characters in the name. For example Table-Test is a valid table name but won't work in your code unless you put []s around it [Table-Test]. QUOTENAME takes care of that for you. It also handles if you happen to have ']'s in your name as well. It generally considered a best practice when you are doing dynamic sql to include schemas and to use QUOTENAME where needed.

USE [EventLogic]
GO

/****** Object:  StoredProcedure [dbo].[usp_SL_ConvertNtextToNvarchar]    Script Date: 08/08/2013 16:28:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
/*
*/
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max)
  DECLARE @table_schema nvarchar(128)
  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;
  SET @sql = '';

  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR 
    SELECT SCHEMA_NAME(so.schema_id) AS table_schema, so.name as table_name, 
            sc.name as column_name
      FROM sys.objects so
      JOIN sys.columns sc ON so.object_id = sc.object_id
      JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                        AND stp.name = 'ntext'
     WHERE so.type = 'U' -- to show only user tables
  OPEN tables_cursor

  FETCH NEXT FROM tables_cursor INTO @table_schema, @table_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'ALTER TABLE Eventlogic.'+QUOTENAME(@table_schema)+'.' + 
                    QUOTENAME(@table_name) + ' ALTER COLUMN ' + 
                    QUOTENAME(@column_name) + ' nvarchar(max);'
    EXEC sp_executesql @sql
    --PRINT @sql

    SET @sql = 'UPDATE Eventlogic.'+QUOTENAME(@table_schema)+'.' + 
                    QUOTENAME(@table_name) + ' SET ' + 
                    QUOTENAME(@column_name) + '=' + 
                    QUOTENAME(@column_name) + ' '
    EXEC sp_executesql @sql
    --PRINT @sql

    SET @count = @count + 1;
    IF @count > 0
      PRINT ('Eventlogic.'+@table_schema+'.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_schema, @table_name, @column_name
  END 
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))  

END;

GO

Best Answer

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

Sql-server – Convert all ntext columns to nvarchar(max)

Related Question