Alter a fulltext index column from size 2000 to max

Thanks in advance for the help, I am a bit of a newbie.

How do I alter a column from varchar (2000) to varchar (max) when it is a fulltext indexed column. Keep in mind that this script will be deployed so the database name may change.

thnx

You can alter the data type only if there are no dependencies (indexes, constraints, references, computed column, partitioned etc) with few exceptions. To alter a column enabled for full-text index, you need to drop the fulltext index, alter the column and recreate the fulltext index.

|||

Like this:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]

WHERE ID = object_id(N'[dbo].tblNews') AND

OBJECTPROPERTY(id, N'tblNews') = 1)

DROP FULLTEXT INDEX ON tblNews;

GO

ALTER TABLE tblNews ALTER COLUMN whatsNew VARCHAR(max) null;

GO

--HOW DO I RECREATE THE FULL-TEXT INDEX?

--DOES IT COME FROM THE CATALOGUE?

GO

|||Yep.

Then

CREATE FULLTEXT INDEX ON tblNews(whatsNew) KEY INDEX <unique index on tblNews>

already ran aspnet_regsql but "Could not establish a connection to the database. "

i already ran aspnet_regsql but i still get below...anyone know why? thanks in advance

Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

Check whether SQL Server 2005 Express is running.

Jos

|||

Hi, Firstly ensure that you have completed aspnet_regsql properly by running through and check that the tables are there by using a application such as SQL server management tool.

Then ensure that the web.config file has the correct connection string for your website.

Any problems just post back here.

Dan

|||

yes..i only had sql 2000 installed.

thanks

|||

It has happened to the best

Jos