Convert all ntext columns to nvarchar(max) I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max).

This is the code

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

  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;

  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR 

    SELECT 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_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE Eventlogic.dbo.' + @table_name + ' ALTER COLUMN ' + @column_name + ' nvarchar(max);')
    EXEC ('UPDATE Eventlogic.dbo.' + @table_name + ' SET ' + @column_name + '=' + @column_name + ' ')
    SET @count = @count + 1;
    IF @count &gt; 0
      PRINT ('Eventlogic.dbo.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  END 
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))  

END;


Whenever I try to run it, I get this error:


  Msg 16924, Level 16, State 1, Procedure usp_SL_ConvertNtextToNvarchar, Line 37
  Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.


We have 338 columns across different tables as ntext, due to legacy.

We upgraded to SQL Server 2008 R2, so we would like to convert these columns to nvarchar(max).

We want to follow the advice here ntext vs nvarchar(max) where Conwell suggests to do the update after the alter.


  SQL server moves the text from the LOB structure to the table (if less than 8,000 bytes).  So when we run the select again with IO STATISTICS we get 0 LOB reads. 


Any help with this error would be great.

UPDATE

Updated code as Martin mentioned.
It is actually altering only the first one on the list before giving the error.

2nd UPDATE

After making the changes on the code to fix the second fetch, I closed SQL Management Studio.
Opened SQL Management Studio again and run it and it worked. So thanks Martin again.

Thanks in advance.

Federico
While if you get this sorted out before you read my answer, making it unlikely to be useful for your particular case (unless you need to do this in multiple databases), I prefer string concatenation over cursors for this type of work. Primarily because you can print the string instead of executing it, verifying that at least the first 8K worth looks proper and correct.

DECLARE @sql NVARCHAR(MAX) = N'USE Eventlogic;';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ALTER COLUMN ' + QUOTENAME(c.name) 
  + CASE WHEN c.system_type_id = 99 THEN ' N' ELSE ' ' END
  + 'VARCHAR(MAX)' + CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END 
  + ';
  UPDATE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
    + ' SET ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(c.name) + ';
  PRINT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(REPLACE(t.name,'''','''''')) 
    + '.' + QUOTENAME(c.name) + ' (' 
    + CONVERT(VARCHAR(12), COUNT(*) OVER(PARTITION BY c.[object_id])) 
    + ' columns)'';'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE c.system_type_id IN (35,99);

SET @sql += N'
PRINT ''Total columns updated: ' + CONVERT(VARCHAR(12), @@ROWCOUNT) + ''';';

PRINT @sql;
--EXEC sp_executesql @sql;


When you're happy with the PRINT output, uncomment the EXEC and run it again.

Jon Seigel's script offers additional functionality (dealing with columns that participate in FULLTEXT indexes).