 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / November 2006PLS HELP: Problem altering columns with full text serach enabled
Hi, I have the following script which enables full text search on the database: ------------------------------------------------------------------------------ sp_fulltext_database 'enable' sp_fulltext_catalog 'cntdocimg', 'create' sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg', 'PK_ContactDocumentImage' sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1, 'sDocType' sp_fulltext_table 'ContactDocumentImage', 'activate' sp_fulltext_catalog 'cntdocimg', 'start_full' sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking' sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex' ------------------------------------------------------------------------------- This script creates full text search for column "imgDocumentImage" of table "ContactDocumentImage" and defines type of stored document by looking at column "sDocType". Now, the problem is that i need to alter the column sDocType to make it nvarchar(10) instead of nvarchar(3) as it currently is; but when i run ALTER statement it says that sDocType column is used by some other object. Then i tried to disable full text search, and it did NOT help either. Could anyone please please please help me here! Thank you in advance, Andrey First off I think this statement is incorrect sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1, 'sDocType' it should be something like this sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1033, 'sDocType' replace 1 with your lcid. Secondly, the full-text index is the problem here. There is little you can do without dropping the fulltext index, then making the change, and then readding it. One option is to come up with a new extension XXX lets say and bind the iFilter to this extension. So if the extension is config and you want to have the word iFilter index it, you would put XXX in the column for documents of the config extension and then add the office iFilter pesistent handler to XXX, like this [HKEY_CLASSES_ROOT\.XXX\PersistentHandler] @="{98de59a0-d175-11cd-a7bd-00006b827d94}"  SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Hi, > [quoted text clipped - 34 lines] > Thank you in advance, > Andrey Hi Hilary, Thanks for the response! 1 as lcid works in SQL 2000, but you are right, in SQL 2005 it has to be 1033. It's fine if i have to drop the fulltext index, but i'm not sure how, and which piece of the script i will need to run after. Could you please help me based on the script below? sp_fulltext_database 'enable' sp_fulltext_catalog 'cntdocimg', 'create' sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg', 'PK_ContactDocumentImage' sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1, 'sDocType' sp_fulltext_table 'ContactDocumentImage', 'activate' sp_fulltext_catalog 'cntdocimg', 'start_full' sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking' sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex' Thanks a lot! Andrey > First off I think this statement is incorrect > [quoted text clipped - 20 lines] > [HKEY_CLASSES_ROOT\.XXX\PersistentHandler] > @="{98de59a0-d175-11cd-a7bd-00006b827d94}" Try drop fulltext index on TableName SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > Hi Hilary, > [quoted text clipped - 43 lines] >> [HKEY_CLASSES_ROOT\.XXX\PersistentHandler] >> @="{98de59a0-d175-11cd-a7bd-00006b827d94}" > Try drop fulltext index on TableName I'm using SQL 2000 - it doesn't have DROP FULLTEXT statement Any other ideas? Thank you, Andrey try sp_fulltext_column 'tablename', 'columnName', 'drop', SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com >> Try drop fulltext index on TableName >> [quoted text clipped - 4 lines] > Thank you, > Andrey > try sp_fulltext_column 'tablename', 'columnName', 'drop', Hi Hilary, I actually tried even more than that to completely disable fulltext search for the database: sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'drop' sp_fulltext_table 'ContactDocumentImage', 'drop' sp_fulltext_database 'disable' I was hoping it would disable fulltext search and "unlock" the column sDocType for altering. It didn't help - i still get the same error when trying to ALTER that column: Server: Msg 5074, Level 16, State 7, Line 6 The object 'CompanyDocumentImage' is dependent on column 'sDocType'. Server: Msg 4922, Level 16, State 1, Line 6 ALTER TABLE ALTER COLUMN sDocType failed because one or more objects access this column. Any ideas? Thank you, Andrey I am unable to repro your problem. What sp are you running? SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com >> try sp_fulltext_column 'tablename', 'columnName', 'drop', > [quoted text clipped - 21 lines] > Thank you, > Andrey > I am unable to repro your problem. What sp are you running? SP4 with Hotfix for SP 4 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.