Problems with creating fulltext indexing on my table.

Problems with creating fulltext indexing on my table.

Post by Thomas Sch » Sat, 31 Jul 2004 14:23:29

I was having a problem creating a fulltextindex on the following table.

I have the following table:

CREATE TABLE [dbo].[QADocs] (
[fullFileName] [varchar] (100) NULL ,
[fileNameLong] [varchar] (100) NULL ,
[fileNameShort] [varchar] (100) NULL ,
[fileExtension] [varchar] (10) NULL ,
[version] [varchar] (20) NULL ,
[document] [image] NULL ,
[recordNumber] [bigint] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (200) NULL ,
[documentType] [varchar] (20) NULL

I am indexing on document and trying to use fileExension as my file Type. I
tried to use documentType, but got the same error as it was building the
index (except the error said 'documentType' is specified more than once.

Column names in each table must be unique. Column name fileExtension in
table 'dboQADocs is specified more than once.

It isn't in there more than once.

But when I did a "select * into QADocsOld from QADocs" and tried to
fulltextindex that file, it worked. Can't figure out why it was giving me
that error (as there were no duplicate names - which I got regardless
whether I use the fileExtension field or the documentType as the document
type selection).

Obviously, if it wasn't in the new file, it wasn't in the old file.

What happened?



Problems with creating fulltext indexing on my table.

Post by Hilary Cot » Sat, 31 Jul 2004 20:38:57

this is a bug in Enterprise Manager which I have reported to Microsoft. To
get it to work change the name of the column DocumentType to Document_Type
and make it char(4). Varchar(4) will work, but char(4) is more efficient, or
use the following TSQL statements within Query Analyzer - where it does

Note that I created a PK on the recordnumber column and called it PK_QADocs.
Change the second statement to reflect the actual name of your primary key.

exec sp_fulltext_catalog N'test', N'create'


exec sp_fulltext_table N'[dbo].[QADocs]', N'create', N'test', N'PK_QADocs'

exec sp_fulltext_column N'[dbo].[QADocs]', N'document', N'add', 1033,

exec sp_fulltext_table N'[dbo].[QADocs]', N'activate'

Hilary Cotter
Looking for a book on SQL Server replication?