August 28, 2008 at 11:08 am
I want to alter the datatype of a clumn text to nvarchar(MAX) of a table who has thousands of records in SQL Server 2008.
I tried following ways but I was not able to change the datatype of a column.
1St way:
I went to the design structure of a table and changed the datatype from text to nvarchar(MAX) and when I hit Save it gave me
Validatin Warnings Dialog box saying that
'Maps' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Do you want to continue attempting to save?
I clicked on 'Yes' and then and it gave me
Post-Save Notofications Dialog box saying that
Errors were encountered during the save proess. Some database objects were not saved.
'Maps' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2Nd way:
I wrote T-SQL Statement to change the datatype like
Alter table dbo.Maps
alter column Content nvarchar(MAX)
It gave me error that
Msg 7614, Level 16, State 1, Line 1
Cannot alter or drop column 'Content' because it is enabled for Full-Text Search.
SO I disabled full-text indexing for that table and then tried to run that statement again, it gave me the same me error.
Does anyone know how to solve this problem?
Thank You
September 23, 2008 at 10:24 am
Did you figure it out yet?
I imagined you need to take THAT column out of full-text first
then disable Full-Text on the table (optional)
run the ALTER command
then re-add the column back to Full-Text
February 10, 2009 at 5:54 pm
you may have to drop the full text index
syntax: DROP FULLTEXT INDEX ON Maps
also, if there are any constraints connected to the field you're converting (like defaults) you will need to drop them too.
The follow script may be useful to you, it converts all text fields into varchar(max) fields
IF OBJECT_ID('spDropColumnConstraints') IS NOT NULL
DROP PROC spDropColumnConstraints
GO
CREATE PROC spDropColumnConstraints(
@table sysname
,@column sysname
)AS SET NOCOUNT ON
DECLARE @tableID int
DECLARE @constraint sysname
DECLARE @constrainttype nchar(1)
DECLARE @sql nvarchar(4000)
SET @tableID = object_id(@table)
DECLARE cur_constraints CURSOR FOR
select
--db_name()as CONSTRAINT_CATALOG
--,t_obj.name as TABLE_NAME
--,user_name(c_obj.uid)as CONSTRAINT_SCHEMA
--,c_obj.nameas CONSTRAINT_NAME
--,col.nameas COLUMN_NAME
--,col.colidas ORDINAL_POSITION
--,com.textas DEFAULT_CLAUSE
c_obj.xtype
,c_obj.[name]
fromsysobjectsc_obj
join syscommentscom on c_obj.id = com.id
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol on t_obj.id = col.id
and con.colid = col.colid
JOIN sys.tables st ON col.id = st.[object_id]
where
c_obj.uid= user_id()
AND col.[Name] = @column
AND st.[object_id] = @tableID
IF EXISTS (SELECT1
FROMsysobjects WITH(NOLOCK)
INNER JOIN
syscolumns WITH(NOLOCK)
ONsysobjects.[id] = syscolumns.[id]
WHEREsyscolumns.name = @column
ANDsysobjects.name = @table)
BEGIN
OPEN cur_constraints
FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = N'ALTER TABLE '+@table+N' DROP CONSTRAINT '+@constraint
PRINT 'DROPPING THE CONSTRAINT:'+@constraint+' (id:'+CAST(object_id(@constraint) AS VARCHAR)+') OF TYPE:'+@constrainttype+' FROM TABLE:'+@table
EXEC sp_executesql @sql
FETCH NEXT FROM cur_constraints INTO @constrainttype, @constraint
END
CLOSE cur_constraints
DEALLOCATE cur_constraints
END
ELSE
PRINT 'The column "'+@column+'" or the table "'+@table+'" does not exist.'
GO
--Convert all text fields into varchar(max) fields
DECLARE @ColumnName VARCHAR(300), @TableName VARCHAR(300), @SqlText NVARCHAR(MAX), @DbName VARCHAR(300)
SELECT @DbName = DB_NAME()
DECLARE mycursor CURSOR FORWARD_ONLY FOR
SELECT c.[name],ta.[name]
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables ta ON c.[object_id] = ta.[object_id]
WHERE t.name = 'text'
OPEN mycursor
WHILE 1=1
BEGIN
FETCH NEXT FROM mycursor INTO @ColumnName, @TableName
IF @@fetch_status <> 0 BREAK;
PRINT('Table: ' + @TableName + ' Column: ' + @ColumnName)
SET @SqlText = ''
--NOTE: Untested section
IF EXISTS (
SELECT 1
FROM sys.fulltext_indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.name = @TableName )
SET @SqlText = 'DROP FULLTEXT INDEX ON [' + @TableName + ']'
SELECT @SqlText = @SqlText + '
spDropColumnConstraints ''' + @TableName + ''', ''' + @ColumnName + '''
BEGIN TRY
ALTER TABLE [' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] VARCHAR(MAX) NULL
END TRY
BEGIN CATCH
DBCC CleanTable (''' + @DbName + ''', ''' + @TableName + ''')
ALTER TABLE [' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] VARCHAR(MAX) NULL
END CATCH'
PRINT('Attempting to run: ' + @SqlText)
BEGIN TRY
EXEC SP_EXECUTESQL @SqlText
END TRY
BEGIN CATCH
PRINT('!!!!!!!!!! FAILURE!')
PRINT(ERROR_MESSAGE())
PRINT('
')
END CATCH
END
CLOSE mycursor
DEALLOCATE mycursor
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply