Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Unanswered: Full Tex Search question...

    I am trying to get a Full Text Search function together for a new application, but don't know the Full Text commands too well.

    Does anyone happen to know if it is possible to query a Full Text Indexed table without using dynamic SQL? Here is the example

    create table test1
    (col1 varchar(10),
    col2 varchar(100))
    -- You have to do the full text indexing yourself here...
    insert into test1
    select 'good row', 'here is some talk about supplies'
    union
    select 'good row', 'this is what we supplied'
    union
    select 'good row', 'supply and demand. Live it. Love it.'
    union
    select 'bad row', 'This is a row that should be ignored'
    go
    -- Don't forget to update the full text index...
    declare @word varchar(20)
    declare @query varchar(500)

    set @word = 'supply'

    set @query = 'select * from test1 where contains (Col2, ''formsof (inflectional, "' + @word + '") or "*' + @word + '*"'')'

    print @query

    exec (@query)


    I can do the simple queries with

    where contains (col2, "@word")

    But the FORMSOF part breaks down, and will not accept variables from me. Am I doomed to Dynamic SQL? Has anyone found better ways of doing this? Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    This works for me - try it.

    drop table ftable
    go
    create table ftable(id int identity primary key, code varchar(25))
    go
    insert ftable(code) values('table')
    insert ftable(code) values('tables')
    insert ftable(code) values('tables table')
    insert ftable(code) values('tables able')
    go
    -- do what it needs to do for creating a full-text index
    declare @word varchar(20)
    declare @query varchar(500)
    set @word = 'table'
    set @query = 'select * from ftable where contains (*, ''formsof (inflectional, "' + @word + '") or "*' + @word + '*"'')'
    print @query
    exec (@query)
    Last edited by snail; 02-02-04 at 16:19.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Thanks, Snail. I have gotten about that far, too. I was hoping for a non-Dynamic SQL way out. Unfortunately, I do not think you can supply paramters directly to a FORMSOF clause.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, Now I am ticked at Microsoft.

    I put in the stored procedure with the Dynamic SQL, but I forgot one important "feature" of dynamic SQL. The users need permissions on the tables AND the stored proc in order to run it. No problem, I say. I can protect the tables with a view. After all, that IS what Microsoft says in ALL of their documentation, right?

    Turns out, you can not do a Full Text Query on a view of a Full Text Indexed column..

    Server: Msg 7601, Level 16, State 2, Line 1
    Cannot use a CONTAINS or FREETEXT predicate on table 'DatabaseSearchResults' because it is not full-text indexed.

    So, has Microsoft turned their backs on "Best Practices"?

    Alternatively, how do the rest of you deal with Full Text Indexing? Any pointers?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •