SQL Server Performance

How to use Contains

Discussion in 'General Developer Questions' started by Madhivanan, May 20, 2005.

  1. Madhivanan Moderator

    When I run the query,

    SELECT nn
    FROM tt
    WHERE nn is not null AND CONTAINS(nn, 'test')

    I get the error

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

    How can I set full-text index?


    Madhivanan

    Failing to plan is Planning to fail
  2. dineshasanka Moderator

    YOu can use like command can't you

    select companyname from customers where companyname like '%a%'
    SELECT nn FROM tt WHERE nn is not null AND nn like '%test%'

    quote:Originally posted by Madhivanan

    When I run the query,

    SELECT nn
    FROM tt
    WHERE nn is not null AND CONTAINS(nn, 'test')

    I get the error

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

    How can I set full-text index?


    Madhivanan

    Failing to plan is Planning to fail
  3. Madhivanan Moderator

    Yes I know that but I just wanted to know the usage of Contains


    Madhivanan

    Failing to plan is Planning to fail
  4. ranjitjain New Member

    from bol:
    This example enables full-text indexing for the Northwind database.

    USE Northwind
    EXEC sp_fulltext_database 'enable'

  5. dineshasanka Moderator

  6. Madhivanan Moderator

    Ranjith,
    When I execute

    Use Test
    EXEC sp_fulltext_database 'enable'

    I get error

    (1 row(s) affected)

    Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
    Full-Text Search is not installed, or a full-text component cannot be loaded.

    Dinesh,
    When I Goto Tools from EM, Full Text index in disabled



    Madhivanan

    Failing to plan is Planning to fail
  7. dineshasanka Moderator

    are you having correct edition of the SQL Server
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1cdv.asp

    quote:Originally posted by Madhivanan

    Ranjith,
    When I execute

    Use Test
    EXEC sp_fulltext_database 'enable'

    I get error

    (1 row(s) affected)

    Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
    Full-Text Search is not installed, or a full-text component cannot be loaded.

    Dinesh,
    When I Goto Tools from EM, Full Text index in disabled



    Madhivanan

    Failing to plan is Planning to fail
  8. Madhivanan Moderator

    I have Developer Edition with the version
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)



    Madhivanan

    Failing to plan is Planning to fail
  9. dineshasanka Moderator

  10. Madhivanan Moderator

    Dinesh,
    The link does not work and that should be SELECT fulltextserviceproperty('IsFulltextInstalled')
    The result is 0


    Madhivanan

    Failing to plan is Planning to fail
  11. dineshasanka Moderator

    Actually that i snot a link ,
    my mistake sorry about it
    0 means that your have not installed the full text search


    quote:Originally posted by Madhivanan

    Dinesh,
    The link does not work and that should be SELECT fulltextserviceproperty('IsFulltextInstalled')
    The result is 0


    Madhivanan

    Failing to plan is Planning to fail
  12. FrankKalis Moderator

    Madhivanan, check your installation and BOL first. <br /><br />Actually your query doesn't make too much sense. If that BLOB column contains your search string, doesn't that automatically mean, it is also NOT NULL. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />select * from sqlreferenz where contains(posting,'"start" AND "end" AND "gap"')<br /></font id="code"></pre id="code"><br />is a very basic example for a fulltext query I use<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  13. Madhivanan Moderator

    Still, I didnot solve this problem. Any other suggesstions?


    Madhivanan

    Failing to plan is Planning to fail
  14. Madhivanan Moderator

    Full-text index is disabled
    When I run this

    EXEC sp_fulltext_database 'enable'

    I got error
    (1 row(s) affected)

    Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
    Full-Text Search is not installed, or a full-text component cannot be loaded.

    It seems that it was not installed.
    How do I istall that?


    Madhivanan

    Failing to plan is Planning to fail
  15. dineshasanka Moderator

    make sure you've installed full-text service from the SQL Server CD. if you've applied sp3 before installing full-text service, make sure you apply it again.
    http://www.mcse.ms/message425675.html is another option that is reinstall the sql server

    quote:Originally posted by Madhivanan

    Full-text index is disabled
    When I run this

    EXEC sp_fulltext_database 'enable'

    I got error
    (1 row(s) affected)

    Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
    Full-Text Search is not installed, or a full-text component cannot be loaded.

    It seems that it was not installed.
    How do I istall that?


    Madhivanan

    Failing to plan is Planning to fail
  16. Madhivanan Moderator

    Yes. That will help me. Thanks Dinesh.


    Madhivanan

    Failing to plan is Planning to fail
  17. dineshasanka Moderator

    is it solved now?

    quote:Originally posted by Madhivanan

    Yes. That will help me. Thanks Dinesh.


    Madhivanan

    Failing to plan is Planning to fail
  18. Madhivanan Moderator

    No. I want to Install Full Text Serach Component. Let me intall and check


    Madhivanan

    Failing to plan is Planning to fail
  19. ghemant Moderator

    Plz Add the option of FULL TEXT SERACH ... it will definately work.

    hsGoswami
    ghemant@gmail.com

Share This Page