Post reply

Fulltext Question

  • May 27, 2003 at 4:56 am

    #82193

    Hi all,

    the following is an adaption of a BOL example I've found while searching for CONTAINS.

    USE fai

    GO

    DECLARE @SearchWord varchar(30)

    SET @SearchWord ='Hannover'

    SELECT MsgSubject FROM mails_header WHERE CONTAINS(MsgNoteText, @SearchWord)

    Can anyone explain why this doesn't work in QA.

    It comes up with

    Server: Msg 170, Level 15, State 1, Line 3

    Zeile 3: Wrong syntax ... '@SearchWord'.

    If I replace the variable with a string it works as it should

    USE fai

    GO

    DECLARE @SearchWord varchar(30)

    SET @SearchWord ='Hannover'

    SELECT MsgSubject FROM mails_header WHERE CONTAINS(MsgNoteText, 'Hannover')

    And on top a snippet of an asp page

    SQL = "SELECT MsgSubject AS Nachricht FROM mails_header WHERE CONTAINS(MsgNoteText, '" & Trim(Request("Suchbegriff")) & "') ORDER BY MsgDateReceived DESC"

    rst.Open SQL, cnn

    which also works fine.

    Any ideas?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 30, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • June 2, 2003 at 8:51 am

    #459355

    My understanding is that you must use the select statement in a string and then execute the string. So your code would look like this:

    USE fai

    Go

    DECLARE @SearchWord varchar(30)

    DECLARE @cStr varchar(400)

    SET @SearchWord = 'Hannover'

    SET @cStr = 'SELECT MsgSubject FROM mails_header WHERE

    CONTAINS(MsgNoteText,'''+ @SearchWord+''')'

    --print @cstr

    exec(@cStr)

    You can see what the sting actually looks like with print @cStr if needed.



    Michelle

  • June 3, 2003 at 12:02 am

    #459456

    Hi mimorr,

    what a great work, and how simple. Finally this has been solved. I've also asked this question on a list where a couple of SQL Server MVP hang around and didn't get a solution.

    Thanks!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 3, 2003 at 4:32 am

    #459497

    Actually the part about wrapping in a dynamic string is not correct. I just tried your example code from the beginning subing my table, column and a valid value and have no issues. Are you sure you didn't have a typo in your try that didn't get posted here?

  • June 3, 2003 at 4:39 am

    #459500

    Hi Antares686,

    quote:

    Actually the part about wrapping in a dynamic string is not correct. I just tried your example code from the beginning subing my table, column and a valid value and have no issues. Are you sure you didn't have a typo in your try that didn't get posted here?

    sorry, there is no typo. I've found this somewhere in BOL while looking for fulltext information and changed it for me.

    The snippet from BOL is

    USE pubs

    GO

    DECLARE @SearchWord varchar(30)

    SET @SearchWord ='Moon'

    SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)

    You'll find it while searching for CONTAINS at the bottom of that page.

    I've looked times and times again for typos, but either I am blind or there isn't a typo?

    However, finally it works!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 3, 2003 at 4:58 am

    #459504

    Odd, I have run the example and never had an issue.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply