persistent full text catalog

Hi
I've a database in development environment that uses a full text catalog
created on the directory d:\abc
When i migrate my database to production, on another server, i did the
complete restore from my backup done in development
all tables and so passed ok but my catalogs remains with the old path d:\abc
that doesn't exists on the new server (d: is cdrom)
how can i change it?!?1
i tried to use sp_fulltext_catalog 'cat', 'rebuild', 'new_path' and it
reported error saying thet the database is not yet fulltext enabled
i then tried to do so using sp_fulltext_database 'enable' and the error
returned was something like 'can't enable catalgos because path d:\abc does
not exists'
when i try to delete the catalog it again reports the fact that the database
is not fulltext enabled...
it seems that this is an infinite loop...
deleting the row from sysfulltextcatalogs does not do it because it removes
it but does not let me create another on on the same tables that the previous
was
using sp_fulltext_table and sp_fulltext_columns the error is te same...
how can i go arround this?!?!
best regards
Jorge Ribeiro
Jorge,
This is a bug related to moving / restoring FT-enabled databases to another
server where the drive letter for the FT Catalogs is different than the
orgianal server. You can work around this this "Catch-22" situation with the
following code (Note: Sysadmin level permission is required):
-- Enable System Table UPDATEs
sp_configure allow,1
go
reconfigure with override
go
use <your_user_database_name>
go
-- Record FT Catalog info. (Note: path = NULL)
select * from sysfulltextcatalogs
go
UPDATE sysfulltextcatalogs set path = '<new_drive_letter:\<new_directory>'
WHERE ftcatid = <ftcatid_of_affected_catalog>
go
-- Record new FT Catalog info.
select * from sysfulltextcatalogs
go
use master
go
-- Disenable System Table UPDATEs
sp_configure allow,0
go
reconfigure with override
go
Regards,
John
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:BDA37D16-A43A-4F51-9D9A-57F3CF7AE5B3@.microsoft.com...
> Hi
> I've a database in development environment that uses a full text catalog
> created on the directory d:\abc
> When i migrate my database to production, on another server, i did the
> complete restore from my backup done in development
> all tables and so passed ok but my catalogs remains with the old path
d:\abc
> that doesn't exists on the new server (d: is cdrom)
> how can i change it?!?1
> i tried to use sp_fulltext_catalog 'cat', 'rebuild', 'new_path' and it
> reported error saying thet the database is not yet fulltext enabled
> i then tried to do so using sp_fulltext_database 'enable' and the error
> returned was something like 'can't enable catalgos because path d:\abc
does
> not exists'
> when i try to delete the catalog it again reports the fact that the
database
> is not fulltext enabled...
> it seems that this is an infinite loop...
> deleting the row from sysfulltextcatalogs does not do it because it
removes
> it but does not let me create another on on the same tables that the
previous
> was
> using sp_fulltext_table and sp_fulltext_columns the error is te same...
>
> how can i go arround this?!?!
> best regards
> Jorge Ribeiro