1

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

Improve this question
5
  • 1
    Your second fetch is just FETCH NEXT FROM tables_cursor INTO @table_name it should be INTO @table_name, @column_name. Also do you never use schemas other than dbo? And you should use QUOTENAME around the object names. And you aren't preserving the original column nullability. Do they all allow NULL? Commented Aug 8, 2013 at 16:14
  • @MartinSmith Thanks for such a quick response! I'm fairly new at this and always used dbo, is there an advantage on usinng schema? Not sure what QUOTENAME is. Commented Aug 8, 2013 at 16:19
  • @MartinSmith I tried fixing that second fetch but still getting the same error. :( Commented Aug 8, 2013 at 16:21
  • It alters the datatype only to the first one on the list before giving that error. Commented Aug 8, 2013 at 16:25
  • 1
    I wrote a script that does this (well, close) a loonnnnng time ago. It's posted on the Resources page of my blog: voluntarydba.com/page/Scripts-Code.aspx Convert text and ntext columns Note: watch out if you have fulltext indexes on your database. Commented Aug 8, 2013 at 16:33

3 Answers 3

Reset to default
1

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
Improve this answer
2
  • Thanks Keneth. It turned out that after closing SQL Management Studio and opening it again it worked. It seems like it kept a cached copy of the SP in memory. Commented Aug 8, 2013 at 16:45
  • @FedericoGiust I would recommend you to wrap in transaction as if something goes wrong, then you can rollback. This is more dangerous to do. Or you can just have print statements and then check them and then execute them once confirmed. Commented Aug 8, 2013 at 16:49
5

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).

Improve this answer
1
  • Thanks Aaron for your suggestion, I'll keep this in mind if we need to convert multiple columns again. Commented Aug 9, 2013 at 9:00
1

Below is one variation that will print the statements for you to analyze and then you can run them :

DECLARE @CurrentDataType VARCHAR(max) DECLARE @DataTypeToChange VARCHAR(max) SET @CurrentDataType = 'datetime' ---CHANGE HERE !! SET @DataTypeToChange = 'smalldatetime' ---CHANGE HERE !! SELECT 'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id], DB_ID()) + '.' + T.[name] + CHAR(10) + ' alter column ' + C.[name] + ' ' + @DataTypeToChange + ' ' + CASE WHEN C.[is_nullable] = 0 THEN 'not null ' ELSE ' null ' END FROM sys.[tables] AS T INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id] INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id] WHERE T.[is_ms_shipped] = 0 AND DTY.NAME = '' + @CurrentDataType + '' ORDER BY T.[name] ,C.[column_id]
Improve this answer
2

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.