Re: SQL2005 FT error adding table column



--

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%23xK4RNhYGHA.1352@xxxxxxxxxxxxxxxxxxxxxxx
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.

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)
DF_ad_banners_ad_priority_count dbo default (maybe cns)
DF_ad_banners_ad_start dbo default (maybe cns)
DF_ad_banners_ad_views dbo default (maybe cns)
DF_ad_banners_d_flag dbo default (maybe cns)
DF_coe_date_internal dbo default (maybe cns)
DF_coe_e_date dbo default (maybe cns)
DF_coe_e_date_end dbo default (maybe cns)
DF_email_list_email_status dbo default (maybe cns)
DF_email_list_submit_date dbo default (maybe cns)
DF_ems_cart_items_add_date dbo default (maybe cns)
DF_ems_cart_items_is_promo dbo default (maybe cns)
DF_ems_cart_items_promo_amount dbo default (maybe cns)
DF_ems_cart_items_promo_id dbo default (maybe cns)
DF_ems_cart_items_promo_type dbo default (maybe cns)
DF_ems_cart_items_qty dbo default (maybe cns)
DF_ems_errorlog_error_date dbo default (maybe cns)
DF_ems_inventory_inventory dbo default (maybe cns)
DF_ems_pop_quiz_q_pq_status dbo default (maybe cns)
DF_ems_price_sale_price_sale dbo default (maybe cns)
DF_ems_price_sale_sale_end dbo default (maybe cns)
DF_ems_price_sale_sale_start dbo default (maybe cns)
DF_ems_price_sale_shopper_type dbo default (maybe cns)
DF_ems_price_std_price dbo default (maybe cns)
DF_ems_price_std_shopper_type dbo default (maybe cns)
DF_ems_product_p_dist dbo default (maybe cns)
DF_ems_product_p_featured dbo default (maybe cns)
DF_ems_product_p_img_lg dbo default (maybe cns)
DF_ems_product_p_img_lg_h dbo default (maybe cns)
DF_ems_product_p_img_lg_w dbo default (maybe cns)
DF_ems_product_p_img_sm dbo default (maybe cns)
DF_ems_product_p_img_sm_h dbo default (maybe cns)
DF_ems_product_p_img_sm_w dbo default (maybe cns)
DF_ems_product_p_man dbo default (maybe cns)
DF_ems_product_p_status dbo default (maybe cns)
DF_ems_product_review_review_date dbo default (maybe cns)
DF_ems_product_review_review_status dbo default (maybe cns)
DF_ems_promo_auto_add dbo default (maybe cns)
DF_ems_promo_promo_status dbo default (maybe cns)
DF_ems_receipt_c_approval dbo default (maybe cns)
DF_ems_receipt_date_created dbo default (maybe cns)
DF_ems_receipt_item_promo_id dbo default (maybe cns)
DF_ems_receipt_shiprate_status dbo default (maybe cns)
DF_ems_receipt_status dbo default (maybe cns)
DF_ems_shopper_date_created dbo default (maybe cns)
DF_ems_shopper_mail_flag dbo default (maybe cns)
DF_ems_shopper_reg dbo default (maybe cns)
DF_ems_shopper_security_level dbo default (maybe cns)
DF_ems_shopper_source dbo default (maybe cns)
DF_ems_shopper_status dbo default (maybe cns)
DF_ems_shopper_type dbo default (maybe cns)
DF_ems_sku_status dbo default (maybe cns)
DF_ems_system_config_em_freeshiplimit dbo default (maybe cns)
DF_ems_system_config_em_handling dbo default (maybe cns)
DF_ems_system_config_em_inventorythreshold dbo default (maybe cns)
DF_ems_system_config_em_ordermin dbo default (maybe cns)
DF_ems_system_config_em_ship_offset dbo default (maybe cns)
DF_ems_system_config_em_usecrosspromo dbo default (maybe cns)
DF_ems_system_config_em_usedistrib dbo default (maybe cns)
DF_ems_system_config_em_usefreeship dbo default (maybe cns)
DF_ems_system_config_em_useinventory dbo default (maybe cns)
DF_ems_system_config_em_usemanufacturers dbo default (maybe cns)
DF_ems_system_config_em_useordermin dbo default (maybe cns)
DF_ems_system_config_em_userating dbo default (maybe cns)
DF_ems_system_config_em_usetopsellers dbo default (maybe cns)
DF_nsnew_tracks_mediatype dbo default (maybe cns)
DF_rate_map_c_status dbo default (maybe cns)
DF_survey_abandon_event_date dbo default (maybe cns)



--
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



"msft-sql" <aklist@xxxxxxxxxxxxx> wrote in message
news:Ob0Qa4LYGHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
do an sp_help tablename and paste all the results here.

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


"msft-sql" <aklist@xxxxxxxxxxxxx> wrote in message
news:uiHYQyIYGHA.4920@xxxxxxxxxxxxxxxxxxxxxxx
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.


"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:upMmVICYGHA.3808@xxxxxxxxxxxxxxxxxxxxxxx
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



"msft-sql" <aklist@xxxxxxxxxxxxx> wrote in message
news:ehEIui$XGHA.5004@xxxxxxxxxxxxxxxxxxxxxxx
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.













.