Full Text Search CONTAINS error on column list - MS SQL 2000

I am working on a full text search query and am receiving the following error:

Line 8: Incorrect Syntax Near '('

Line 8 is as follows:
CONTAINSTABLE (Product, (Product_Name,Product_Desc,Product_ManufacturerPart,UPC,Manufacturer_Name,Searchable_Terms,Book_Author,Book_Type), '(Cannon NEAR digital NEAR rebel)', 10) AS KEY_TBL

The issue is with the column list:
(Product_Name,Product_Desc,Product_ManufacturerPart,UPC,Manufacturer_Name,Searchable_Terms,Book_Author,Book_Type)

When I replace this column list with an asterisk (*), the query works fine.

From MSDN.com:
column_list
    Indicates that several columns, separated by a comma, can be specified. column_list must be enclosed in parentheses. Unless language_term is specified, the language of all columns of column_list must be the same.

I am conforming to this - why am I getting an error?
SELECT 
    FT_TBL.Product_Name, 
    FT_TBL.Product_Desc, 
    KEY_TBL.RANK
FROM 
    production.dbo.product AS FT_TBL 
    INNER JOIN
        CONTAINSTABLE (Product, (Product_Name,Product_Desc,Product_ManufacturerPart,UPC,Manufacturer_Name,Searchable_Terms,Book_Author,Book_Type), '(Cannon NEAR digital NEAR rebel)', 10) AS KEY_TBL
        ON FT_TBL.Product_ID = KEY_TBL.[KEY]

Open in new window

LVL 6
trickyidiotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please refer to the sql 2000 version of the syntax:
http://msdn.microsoft.com/en-us/library/aa258229(SQL.80).aspx
which does NOT have the (columnlist) variant of the syntax

the sql 2005/2008 versions do have that variant:
sql 2005:
http://msdn.microsoft.com/en-us/library/ms189760(SQL.90).aspx
sql 2008:
msdn.microsoft.com/en-us/library/ms189760.aspx

so, if you are indeed on sql 2000, you cannot use that syntax.
0
BrandonGalderisiCommented:
0
trickyidiotAuthor Commented:
Ok - I've moved to a less tempting solution, listed below

but I'm getting the same error - incorrect syntax near ')'
on the last line of the query
select 
    * 
from 
    product 
where 
    product_id in 
    (
        select distinct 
            p.product_id 
        from 
        (
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_Name, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_Desc, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_ManufacturerPart, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, UPC, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Manufacturer_Name, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Searchable_Terms, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Book_Author, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Book_Type, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
        )
    )

Open in new window

0
BrandonGalderisiCommented:
The problem now is that you aren't naming your derived table.  I added b to the next to the last line.
select 
    * 
from 
    product 
where 
    product_id in 
    (
        select distinct 
            p.product_id 
        from 
        (
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_Name, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_Desc, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Product_ManufacturerPart, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, UPC, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Manufacturer_Name, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Searchable_Terms, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Book_Author, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
 
            UNION ALL
 
            SELECT 
                p.product_id  
            FROM 
                product AS P 
                INNER JOIN 
                    CONTAINSTABLE(Product, Book_Type, 'cannon NEAR battery') AS KEY_TBL
                    ON p.Product_ID = KEY_TBL.[KEY] 
        ) b
    ) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trickyidiotAuthor Commented:
Thanks guys!

I didn't even realize I was reading the wrong doc on msdn...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.