Re: Full text search not giving any results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You're welcome, Sirisha,
First of all, thank you for the below info, as it is always helpful in
troubleshooting SQL FTS issues.
I can see that your column CVFileExt is defined as a char(3). What file
types are you storing in the column CVFile? MS Word document? Adobe PDF
files? WordPerfect files? Depending upon the file type and its associated
file extension, do you have the column CVFileExt populated with the value
"doc" for those rows that contain MS Word documents? If you're storing Adobe
PDF files, have you downloaded and installed the PDF IFilter on this server?
If so, see the below blog entries:

Full Text Search Adobe PDF files stored in SQL Server...
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!537.entry


Full-text searching with IFilter's...
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!511.entry

You should also review your server's application event log for "Microsoft
Search" or MssCi souce events for any errors such as "One or more documents
stored in image columns with extension 'doc' did not get full-text indexed
because loading the filter failed with error '0x1'."

You may also want to review the following two KB articles as well:
277549 PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer
Logon Account Through [NT4.0) Control Panel [or Win2K Component Services]
317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs

SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/


"Sirisha" <Sirisha@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B17D91B2-AFFB-4645-97A3-49CFB430AC7C@xxxxxxxxxxxxxxxx
> Dear John,
>
> Thank you for your reply. The table "testIndex" is a test table we created
> to test the full text search because the full text search was not working
> with the actual table.
> The actual table is "tblConsultantCVs". As you said, this table has the
> column "CVFile" with the content in it and the column "CVFileExt" which is
> a
> column to store the file extension or the document type. But the full text
> search doesn't work.
>
> I ran the SQL code you gave for the "tblConsultantCVs". I couldn't find a
> way to include the output as an attachment. So I am copying the output
> below.
>
> Please let me know if I am missing something in creating the full text
> index
> and thank you once again for your response.
>
> Regards,
> Sirisha
> --------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------------
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft
>
> Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>
> (1 row(s) affected)
>
> Name
>
>
> Owner
>
>
> Type Created_datetime
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------ -------------------------------
> ------------------------------------------------------
> tblConsultantCVs
>
>
> dbo
>
>
> user table 2002-03-28 18:27:20.460
>
>
>
> Column_name
>
>
> Type
>
>
> Computed Length Prec Scale
> Nullable
>
> TrimTrailingBlanks FixedLenNullInSource
> Collation
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------ ----------------------------------- ----------- ----- -----
> -----------------------------------
>
> ----------------------------------- -----------------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
> ID
>
>
> int
>
>
> no 4 10 0 no
> (n/a)
>
> (n/a) NULL
> UniqueRowID
>
>
> int
>
>
> no 4 10 0 no
> (n/a)
>
> (n/a) NULL
> Date
>
>
> smalldatetime
>
>
> no 4 no
> (n/a)
>
> (n/a) NULL
> Format
>
>
> tinyint
>
>
> no 1 3 0 no
> (n/a)
>
> (n/a) NULL
> Language
>
>
> tinyint
>
>
> no 1 3 0 no
> (n/a)
>
> (n/a) NULL
> Project
>
>
> int
>
>
> no 4 10 0 yes
> (n/a)
>
> (n/a) NULL
> Focus
>
>
> int
>
>
> no 4 10 0 yes
> (n/a)
>
> (n/a) NULL
> Submitted
>
>
> bit
>
>
> no 1 no
> (n/a)
>
> (n/a) NULL
> CVFile
>
>
> image
>
>
> no 1000 yes
> (n/a)
>
> (n/a) NULL
> CVFileExt
>
>
> char
>
>
> no 3 yes
> no
>
> yes
> SQL_Latin1_General_CP1_CI_AS
> EnteredDate
>
>
> datetime
>
>
> no 8 no
> (n/a)
>
> (n/a) NULL
> EnteredBy
>
>
> varchar
>
>
> no 20 yes
> no
>
> no
> SQL_Latin1_General_CP1_CI_AS
> UpdatedDate
>
>
> smalldatetime
>
>
> no 4 yes
> (n/a)
>
> (n/a) NULL
> UpdatedBy
>
>
> varchar
>
>
> no 20 yes
> no
>
> no
> SQL_Latin1_General_CP1_CI_AS
>
>
> Identity
>
>
> Seed Increment
> Not For Replication
> --------------------------------------------------------------------------------------------------------------------------
>
> ------ ----------------------------------------
> ---------------------------------------- -------------------
> UniqueRowID
>
>
> 1 1
> 0
>
>
> RowGuidCol
>
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
> No rowguidcol column defined.
>
>
> Data_located_on_filegroup
>
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
> PRIMARY
>
>
> index_name
>
>
> index_description
>
>
>
> index_keys
>
>
>
>
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------------
> IX_tblConsultantCVs
>
>
> nonclustered located on PRIMARY
>
>
>
> ID
> IX_tblConsultantCVs_FullText
>
>
> nonclustered, unique located on PRIMARY
>
>
>
> UniqueRowID
> PK__tblConsultantCVs__226D5FD0
>
>
> clustered, unique, primary key located on PRIMARY
>
>
>
> ID, EnteredDate
>
>
> constraint_type
>
>
> constraint_name
>
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
>
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------ ------------- ------------- -------------- ----------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------------
> DEFAULT on column Submitted
>
>
> DF__tblConsul__Submi__28263926
>
>
> (n/a) (n/a) (n/a)
> (n/a) (0)
> FOREIGN KEY
>
>
> FK__tblConsul__Focus__273214ED
>
>
> No Action No Action Enabled
> Is_For_Replication Focus
>
>
>
>
>
>
>
> REFERENCES
>
> ARDISD.dbo.tlkpConsultantTraits (Code)
> FOREIGN KEY
>
>
> FK__tblConsul__Forma__2455A842
>
>
> No Action No Action Enabled
> Is_For_Replication Format
>
>
>
>
>
>
>
> REFERENCES
>
> ARDISD.dbo.tlkpConsultantCVFormats (Code)
> FOREIGN KEY
>
>
> FK__tblConsul__Langu__2549CC7B
>
>
> No Action No Action Enabled
> Is_For_Replication Language
>
>
>
>
>
>
>
> REFERENCES
>
> ARDISD.dbo.tlkpConsultantCVLanguages (Code)
> FOREIGN KEY
>
>
> FK__tblConsul__Proje__263DF0B4
>
>
> No Action No Action Enabled
> Is_For_Replication Project
>
>
>
>
>
>
>
> REFERENCES
>
> ARDISD.dbo.tblProjects (ID)
> FOREIGN KEY
>
>
> FK__tblConsultan__ID__23618409
>
>
> No Action No Action Enabled
> Is_For_Replication ID
>
>
>
>
>
>
>
> REFERENCES
>
> ARDISD.dbo.tblConsultant (ID)
> PRIMARY KEY (clustered)
>
>
> PK__tblConsultantCVs__226D5FD0
>
>
> (n/a) (n/a) (n/a)
> (n/a) ID, EnteredDate
>
>
> No foreign keys reference this table.
> Table is referenced by views
>
>
>
> --------------------------------------------------------------------------------------------------------------------------
>
> ------
>
>
> "John Kane" wrote:
>
>> Sirisha,
>> Could you reply back with the full output of the following sql code?
>>
>> use <your_database_name>
>> select @@version
>> exec sp_help testIndex
>> go
>>
>> You need to alter the table testIndex and add a "file extension" column
>> defined as char(3), varchar(4) or sysname for use with the "cv" (image
>> data
>> type) as SQL Server 2000 only recognizes binary files stored in the image
>> column. You will also need to populate the "file extension" column with
>> "doc" or ".doc" (with varchar(4) or sysname) for MS Word documents stored
>> in
>> the cv column.
>>
>> Hope that helps!
>> John
>> --
>> SQL Full Text Search Blog
>> http://spaces.msn.com/members/jtkane/
>>
>>
>>
>> "Sirisha" <Sirisha@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5500EE30-72A6-4061-9E2D-00458C2E49E1@xxxxxxxxxxxxxxxx
>> > Hi,
>> >
>> > We have a table named "testIndex" which has columns: id (primary key),
>> > cv
>> > (image) and indexCol (varchar). We ran the following scripts to create
>> > a
>> > full
>> > text index on the column "cv".
>> >
>> > EXEC sp_fulltext_database 'enable'
>> > EXEC sp_fulltext_catalog 'TestCatalog', 'create'
>> > EXEC sp_fulltext_table 'testindex', 'create', 'TestCatalog',
>> > 'PK_testIndex'
>> > EXEC sp_fulltext_column 'testindex', 'indexcol', 'add' , 0, 'indexcol'
>> > EXEC sp_fulltext_table 'testindex','activate'
>> > EXEC sp_fulltext_catalog 'TestCatalog', 'start_full'
>> >
>> >
>> > For testing, we added couple of rows to the table "testIndex"
>> > insert into testIndex (id, cv, indexCol) values(1,"test", "varchar")
>> > insert into testIndex (id, cv, indexCol) values(2,"abcdef", "varchar")
>> >
>> > When we do the search using the query below, I am not getting any
>> > results
>> > back. select * from testindex where contains(cv,'abc')
>> >
>> > I made sure that the "MSService" is running under local system account
>> > and
>> > the builtin/admin is under the logins. I am doing the full text search
>> > for
>> > the test time. so I am not sure if I am missing anything. I need to
>> > make
>> > this
>> > work as soon as possible. Can anyone please help me with this issue.
>> > Any
>> > help
>> > would be really appreciated.
>> >
>> > Thanks in advance,
>> > Sirisha
>>
>>
>>


.

Relevant Pages

  • Re: Hypersonic SQL and foreign keys
    ... I've heard that Hypersonic SQL doesn't support foreign keys? ... > is a bit confusing. ... I can't tell you about Hypersonic's foreign key support, but your SQL is a problem. ... > username. ...
    (comp.lang.java.databases)
  • Re: Timeout or other timing-type problem???
    ... In your place I would trace the sql statements issued to sql server, either log them from code or even better, log them using sql tools. ... The INSERT statement conflicted with the FOREIGN KEY constraint ... How do I ensure it is exact? ... Here is how I'm establishing my connection and setting up my ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL 2008 FK Issues while Inserting
    ... this column is a foreign key to the ... other table with the same value for the primary key. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)
  • Re: dynamic check boxes
    ... change names in SQL & VB Code. ... preview a sample report, ALL volunteers print out, even tho only 3 checked. ... > the "key" to how the setup works is in the tables' primary and foreign key ... >> Richard Harison ...
    (microsoft.public.access.formscoding)
  • Re: FK to a view?
    ... To derive practical benefits, in a relational ... Since a view in SQL is not fully ... implemented in accordance with a view in relational model, ... create a foreign key on a view cannot be provided in SQL. ...
    (microsoft.public.sqlserver.programming)