sql server full text search: I'm new to SQL 2005, and I'm trying to create my first FT catalog using the
wizard.

After selecting the tables and columns I wanted in the index, I received an
errorbox, the copied text of which is:


TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot load tables or views.

------------------------------
ADDITIONAL INFORMATION:

Property TypeColumnName is not available for FullTextIndexColumn '[m_name]'.
This property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TypeColumnName&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

That link doesn't provide any more information. The catalog seems to be
created, but if I pull up its properties and click "tables", the error pops
up again.

FWIW, I tried creating a new catalog in a separate dB using a single table
with different fields, and I got the error again.

--

[quoted text, click to view]

Could you post the schema for the problem table here?

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



[quoted text, click to view]

[quoted text, click to view]


what's the easiest way to generate that in Query An.?
[quoted text, click to view]

Is this enough info:

p_id varchar(50) Unchecked
p_name varchar(255) Unchecked
p_subTitle varchar(255) Checked
p_desc varchar(1000) Checked
p_ingredients varchar(1000) Checked
p_img_lg varchar(255) Unchecked
p_img_sm varchar(255) Unchecked
p_img_pom varchar(255) Checked
p_img_lg_h int Unchecked
p_img_lg_w int Unchecked
p_img_sm_h int Unchecked
p_img_sm_w int Unchecked
p_img_pom_h smallint Checked
p_img_pom_w smallint Checked
p_status int Unchecked
p_man int Unchecked
p_dist int Unchecked
p_featured int Unchecked
p_cat varchar(25) Unchecked
p_hidden int Unchecked
display_order int Unchecked
row_id int Unchecked
META_title varchar(100) Checked
META_desc varchar(1024) Checked
META_keyword varchar(1024) Checked
p_perishable bit Unchecked
Unchecked

When I click on "Columns" in the FT Index Properties pane, the error is:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot load Columns Index Properties form.

------------------------------
ADDITIONAL INFORMATION:

Property TypeColumnName is not available for FullTextIndexColumn '[p_desc]'.
This property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)

The database was originally created in SQL2000, and this is an upgraded
installation to 2005.


[quoted text, click to view]

do an sp_help tablename and paste all the results here.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



[quoted text, click to view]

[quoted text, click to view]

p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no no SQL_Latin1_General_CP1_CI_AS
p_url_name varchar no 255 yes no no SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no no SQL_Latin1_General_CP1_CI_AS
p_img_lg varchar no 255 no no no SQL_Latin1_General_CP1_CI_AS
p_img_sm varchar no 255 no no no SQL_Latin1_General_CP1_CI_AS
p_img_lg_h int no 4 10 0 no (n/a) (n/a) NULL
p_img_lg_w int no 4 10 0 no (n/a) (n/a) NULL
p_img_sm_h int no 4 10 0 no (n/a) (n/a) NULL
p_img_sm_w int no 4 10 0 no (n/a) (n/a) NULL
p_status int no 4 10 0 no (n/a) (n/a) NULL
p_man int no 4 10 0 no (n/a) (n/a) NULL
p_dist int no 4 10 0 no (n/a) (n/a) NULL
p_featured int no 4 10 0 no (n/a) (n/a) NULL
p_hidden int no 4 10 0 no (n/a) (n/a) NULL
p_perishable bit no 1 no (n/a) (n/a) NULL
display_order int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
META_title varchar no 100 yes no no SQL_Latin1_General_CP1_CI_AS
META_desc varchar no 1024 yes no no SQL_Latin1_General_CP1_CI_AS
META_keyword varchar no 1024 yes no no
SQL_Latin1_General_CP1_CI_AS
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL

[quoted text, click to view]

I need the entire results set from sp_help, you have only given me the first
results set. However it appears that you are trying to set a document column
type for p_desc, but this is not an image or a varbinary(max) column. For
this to work it must be of one of those data types.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



[quoted text, click to view]

--

[quoted text, click to view]

Hi Hilary, I appreciate your trying to help, but you said "do an sp_help
tablename and paste all the results here." Which is what I did.

I'm not "trying to set" anything. This is an existing table in two different
databases, and all I've ever done was create the table initial in EM by
typing in the field name, setting it to varchar and a particular length, and
leaving "allow null" checked. I have no idea how to set a "document column
type" and never have. All I did was check "use full-text indexing" under the
Files property for the database in Management Studio, then use the wizard to
create the catalog, add the table, and select the varchar fields to be
indexed. When I hit "OK" the little green activity cursor spins and then it
throws the error. If I'm doing something wrong or missing a step, let me
know...or maybe there's something wrong with this installation or something
I missed when upgrading the server.

FWIW, I tried it a third time on a third database with a different table
structure, and received the same error on the first field in the list:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot load tables or views.

------------------------------
ADDITIONAL INFORMATION:

Property TypeColumnName is not available for FullTextIndexColumn
'[p_artist]'. This property may not exist for this object, or may not be
retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TypeColumnName&LinkId=20476

Here is an "sp_help" for the entire database (the third one):

sysconstraints dbo view
syssegments dbo view
ad_banners dbo user table
coe dbo user table
dtproperties dbo user table
email_list dbo user table
email_list_types dbo user table
ems_attribute dbo user table
ems_attribute_items dbo user table
ems_cart dbo user table
ems_cart_items dbo user table
ems_cross_promo dbo user table
ems_dept dbo user table
ems_discount_qty dbo user table
ems_distributors dbo user table
ems_errorlog dbo user table
ems_inventory dbo user table
ems_manufacturers dbo user table
ems_p_by_d dbo user table
ems_pop_quiz_q dbo user table
ems_pop_quiz_r dbo user table
ems_price_sale dbo user table
ems_price_std dbo user table
ems_product dbo user table
ems_product_description dbo user table
ems_product_review dbo user table
ems_promo dbo user table
ems_receipt dbo user table
ems_receipt_item dbo user table
ems_shopper dbo user table
ems_shopper_billto dbo user table
ems_shopper_shipto dbo user table
ems_shopper_type dbo user table
ems_sku dbo user table
ems_sku_by_user dbo user table
ems_system_config dbo user table
ems_tax dbo user table
ems_tax_by_zip dbo user table
nsnew_tracks dbo user table
rate_map dbo user table
rate_methods dbo user table
survey_abandon dbo user table
syscolumns dbo system table
syscomments dbo system table
sysdepends dbo system table
sysfilegroups dbo system table
sysfiles dbo system table
sysfiles1 dbo system table
sysforeignkeys dbo system table
sysfulltextcatalogs dbo system table
sysfulltextnotify dbo system table
sysindexes dbo system table
sysindexkeys dbo system table
sysmembers dbo system table
sysobjects dbo system table
syspermissions dbo system table
sysproperties dbo system table
sysprotects dbo system table
sysreferences dbo system table
systypes dbo system table
sysusers dbo system table
dt_addtosourcecontrol dbo stored procedure
dt_addtosourcecontrol_u dbo stored procedure
dt_adduserobject dbo stored procedure
dt_adduserobject_vcs dbo stored procedure
dt_checkinobject dbo stored procedure
dt_checkinobject_u dbo stored procedure
dt_checkoutobject dbo stored procedure
dt_checkoutobject_u dbo stored procedure
dt_displayoaerror dbo stored procedure
dt_displayoaerror_u dbo stored procedure
dt_droppropertiesbyid dbo stored procedure
dt_dropuserobjectbyid dbo stored procedure
dt_generateansiname dbo stored procedure
dt_getobjwithprop dbo stored procedure
dt_getobjwithprop_u dbo stored procedure
dt_getpropertiesbyid dbo stored procedure
dt_getpropertiesbyid_u dbo stored procedure
dt_getpropertiesbyid_vcs dbo stored procedure
dt_getpropertiesbyid_vcs_u dbo stored procedure
dt_isundersourcecontrol dbo stored procedure
dt_isundersourcecontrol_u dbo stored procedure
dt_removefromsourcecontrol dbo stored procedure
dt_setpropertybyid dbo stored procedure
dt_setpropertybyid_u dbo stored procedure
dt_validateloginparams dbo stored procedure
dt_validateloginparams_u dbo stored procedure
dt_vcsenabled dbo stored procedure
dt_verstamp006 dbo stored procedure
dt_whocheckedout dbo stored procedure
dt_whocheckedout_u dbo stored procedure
sp_emShop_Store_Builder dbo stored procedure
PK_ad_banners dbo primary key cns
PK_coe dbo primary key cns
pk_dtproperties dbo primary key cns
PK_email_list dbo primary key cns
PK_ems_attribute dbo primary key cns
PK_ems_attribute_items dbo primary key cns
PK_ems_cart dbo primary key cns
PK_ems_cart_items dbo primary key cns
PK_ems_cross_promo dbo primary key cns
PK_ems_dept dbo primary key cns
PK_ems_discount_qty dbo primary key cns
PK_ems_distributors dbo primary key cns
PK_ems_errorlog dbo primary key cns
PK_ems_inventory dbo primary key cns
PK_ems_manufacturers dbo primary key cns
PK_ems_p_by_d dbo primary key cns
PK_ems_pop_quiz_q dbo primary key cns
PK_ems_pop_quiz_r dbo primary key cns
PK_ems_price_sale dbo primary key cns
PK_ems_price_std dbo primary key cns
PK_ems_product dbo primary key cns
PK_ems_product_description dbo primary key cns
PK_ems_product_review dbo primary key cns
PK_ems_promo dbo primary key cns
PK_ems_receipt dbo primary key cns
PK_ems_receipt_item dbo primary key cns
PK_ems_shopper dbo primary key cns
PK_ems_shopper_billto dbo primary key cns
PK_ems_shopper_shipto dbo primary key cns
PK_ems_shopper_type dbo primary key cns
PK_ems_sku dbo primary key cns
PK_ems_sku_by_user dbo primary key cns
PK_ems_tax dbo primary key cns
PK_ems_tax_by_zip dbo primary key cns
PK_nsnew_tracks dbo primary key cns
PK_rate_map dbo primary key cns
PK_survey_abandon dbo primary key cns
DF__dtpropert__versi__6754599E dbo default (maybe cns)
DF_ad_banners_ad_clicks dbo default (maybe cns)
DF_ad_banners_ad_end dbo default (maybe cns)
DF_ad_banners_ad_priority dbo default (maybe cns)

I ran another test, and it looks like something's hosed on this install, or
I'm just missing something obvious.

-- I created a new database from scratch in Management Studio.
-- I added a table to it.
-- I added an int field and made it PK
-- I added two varchar fields, one nullable one not
-- I added 3 rows to the table with some dummy text in the varchar fields
-- I set the dB to full-text
-- I went to add a full text catalog
-- I added the table, and selected the varchar fields
-- I immediately got the error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot load tables or views.

------------------------------
ADDITIONAL INFORMATION:

Property TypeColumnName is not available for FullTextIndexColumn
'[some_desc]'. This property may not exist for this object, or may not be
retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TypeColumnName&LinkId=20476

------------------------------

Here's the sp_help from that test dB:

sysconstraints dbo view
syssegments dbo view
test_table dbo user table
syscolumns dbo system table
syscomments dbo system table
sysdepends dbo system table
sysfilegroups dbo system table
sysfiles dbo system table
sysfiles1 dbo system table
sysforeignkeys dbo system table
sysfulltextcatalogs dbo system table
sysfulltextnotify dbo system table
sysindexes dbo system table
sysindexkeys dbo system table
sysmembers dbo system table
sysobjects dbo system table
syspermissions dbo system table
sysproperties dbo system table
sysprotects dbo system table
sysreferences dbo system table
systypes dbo system table
sysusers dbo system table
PK_test_table dbo primary key cns

and sp_help from the table I created:

test_table dbo user table 2006-04-17 09:25:50.527
some_id int no 4 10 0 no (n/a) (n/a) NULL
some_name varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
some_desc varchar no 500 yes no no SQL_Latin1_General_CP1_CI_AS
some_id 1 1 0
No rowguidcol column defined.
PK_test_table clustered, unique, primary key located on PRIMARY some_id
PRIMARY KEY (clustered) PK_test_table (n/a) (n/a) (n/a) (n/a) some_id

Am I just too-noobie for full-text indexing or is there something wrong with
the install?

--

[quoted text, click to view]