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>

almost newbie question:recover view

SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > SQL 2000 sp2
> > One of my users accidentally deleted one of her views. Using BackupExec
> 8.6
> > I restored the database to another SQL server. After restoring, I used
> the
> > Import DATA wizard to attempt to move the view from the recovered
database
> > to the "live" database.
> > I select "copy objects & data between SQL server dbs."
> > On the next page I uncheck Copy all objects and click on Select Objects.
> I
> > then use the following screen to select the view I wish to recover.
> > I run it and it says it completed successfully. However, when I look at
> the
> > views available, the one that supposedly transferred is not there.
> > Can views be restored or would it be just as easy to recreate it using
the
> > recovered view as a guide?
> >
> >
>

almost newbie question:recover view

SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> 8.6
> the
database
> I
> the
the
>