PLS 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}"
Hilary 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
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:uxNZPiaDHHA.3476@.TK2MSFTNGP04.phx.gbl...
> 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
|||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
Hilary Cotter wrote:
> 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}"
>
>
|||Try drop fulltext index on TableName
Hilary 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
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:456486B0.6050700@.newsgroups.nospam...[vbcol=seagreen]
> 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
>
> Hilary Cotter wrote:
|||Hilary Cotter wrote:
> 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',
Hilary 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
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:OAR8Eu$DHHA.1748@.TK2MSFTNGP02.phx.gbl...
> Hilary Cotter wrote:
> I'm using SQL 2000 - it doesn't have DROP FULLTEXT statement
> Any other ideas?
> Thank you,
> Andrey
|||Hilary Cotter wrote:
> 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?
Hilary 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
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:456A53A3.5000603@.newsgroups.nospam...
> Hilary Cotter wrote:
> 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
|||Hilary Cotter wrote:
> I am unable to repro your problem. What sp are you running?
>
SP4 with Hotfix for SP 4