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 > 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
FETCH NEXT FROM tables_cursor INTO @table_name
it should beINTO @table_name, @column_name
. Also do you never use schemas other thandbo
? And you should useQUOTENAME
around the object names. And you aren't preserving the original column nullability. Do they all allowNULL
?Convert text and ntext columns
Note: watch out if you have fulltext indexes on your database.