Cannot drop Full-Text

Question

Cannot drop Full-Text

Barbara-167570

SSCrazy

Points: 2352
More actions
March 23, 2007 at 9:29 am

#63055

I’m having an issue dropping Full-Text from SQL 2000 - 8.00.760 - SP3, Enterprise Edition databases in DEV and QA boxes after restoring from Production (all three servers are the same version). The Full-Text path IS different, on Production it is S:\FT and on DEV and QA it is c:\ft. I know I’ve done it before and thought I had the process down but apparently I was wrong.

This was I though had worked in the past for me:

select * from sysfulltextcatalogs
update sysfulltextcatalogs set path = 'c:\ft' where ftcatid = 5
exec sp_fulltext_table @tabname = 'FT_Documents' , @action = 'drop'

exec sp_fulltext_catalog @ftcat = 'FT_catalog_FT Documents' , @action = 'drop'

Again, I thought this is what had worked for me in the past and from there I was able to rebuild Full-Text, not this time.

I could go on and on (and I will if you think the information will be helpful)with what I've tried but the short version is the above steps plus I tried the following:
UPDATE sysobjects SET ftcatid = 0
WHERE ftcatid > 0

Even after this has removed the record I cannot delete Full-Text

The error message I've been receiving:

Error 7619: Execution of a full-text operation failed. The specified object cannot be found. Specify the name of an existing object.

Server: Msg 15606, Level 16, State 1, Procedure sp_fulltext_table, Line 88 You must first create a full-text index on table 'FT_documents'.
Can anyone please help me and tell me the correct steps to remove Full-Text when you restore from another server. I'm sure there is some simple solution I'm overlooking but this is driving me insane and I need to get it resolved so I can move foward with some testing.
Any and all help is greatly appreciated!!
Thanks for your time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Just a word of caution as a reminder, you should never try to perform direct updates to system objects unless there is no way otherwise.

Check out this KB article it should help

http://support.microsoft.com/kb/240867

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 2

Thank for the response Antares.

I've actually seen that KB article before, unfortunately it does not cover what I am trying to accomplish. I do not want to move, copy or backup a full-text catalog I want to REMOVE it when I restore a backup from production (which has full-text running) to the QA/DEV environments where I don't want it running. Does anyone have an advice on how to do that? I'm thinking I can't be the only one who's ever wanted to do this.

Thanks again Antares!

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Think this is what you were after, sorry I misread your comments

http://www.sqlservercentral.com/columnists/rpearl/2819.asp

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 4

Antares,

Thank you again for your response. Unfortunately I have read that article as well and it does not work for the issue I'm having. I'm not migrating to SQL 2005, all of the servers I'm dealing with in my scenario are SQL 2000 Servers. Although I did attempt the suggestions made in that article, that was where I got the notion to:

UPDATE sysobjects SET ftcatid = 0

WHERE ftcatid > 0

Unfortunately that does not work for me either.

I'm totally at a loss. Am I trying to remove Full Text in the wrong order? Has anyone else run into this issue when the restore a backup on a different server that has Full-Text on it? Seems to me there should be an easy solution and I'm missing something.

Thanks once again for any and all assistance,

Barbara

Basu Hullur SSC Veteran Points: 216 More actions · Answer 5

Hi There:

I followed your steps and did not have any prolems moving the full text index from E drive to C drive, dropping it and recreating it....Just an FYI

Thanks.