Weird Stored Procedure / FREETEXTTABLE Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Weird Stored Procedure / FREETEXTTABLE Issue

Author

Topic

Mr Fett
Starting Member

28 Posts

Posted - 2008-01-23 : 10:00:04

Hi all,

I'm fairly new to stored procedures but pretty familiar with SQL - I'm slowly moving all of my SQL statements to Stored Procedures on my websites - all have worked very well apart from this one which conducts a freeTextTable search:

CREATE PROCEDURE dbo.searchProducts

@searchWord varchar(80)

AS

SELECT DISTINCT PRODUCT_products.productName, PRODUCT_products.seoName, PRODUCT_products.seoCatPath, PRODUCT_products.productSummary, PRODUCT_products.price, PRODUCT_products.thumbWidth, PRODUCT_products.thumbHeight, PRODUCT_products.imageAvailable, PRODUCT_products.productID, PRODUCT_products.categoryID, PRODUCT_productVariations.specialOffer, PRODUCT_productVariations.salePrice,
(SELECT MAX(price) FROM PRODUCT_productVariations WHERE productID = PRODUCT_products.productID) AS maxPrice,
(SELECT MIN(price) FROM PRODUCT_productVariations WHERE productID = PRODUCT_products.productID) AS minPrice,
(SELECT MAX(salePrice) FROM PRODUCT_productVariations WHERE productID = PRODUCT_products.productID) AS maxSalePrice,
(SELECT MIN(salePrice) FROM PRODUCT_productVariations WHERE productID = PRODUCT_products.productID) AS minSalePrice,
(SELECT COUNT(*) FROM PRODUCT_productVariations WHERE productID = PRODUCT_products.productID) AS numberOfVariations

FROM PRODUCT_products INNER JOIN PRODUCT_productVariations ON PRODUCT_products.productID = PRODUCT_productVariations.productID INNER JOIN FREETEXTTABLE(PRODUCT_products, *, @searchWord) AS productFullText ON PRODUCT_products.productID = productFullText.[key]
GO

This kicks up a "ODBC Driver does not support requested..." error BUT if I replace the @searchWord with a hardcoded string such as 'football' then the search performs perfect. I've double checked that the value @searchWord is being passed to the stored procedure - any ideas anyone?

Also - if I put the same SQL statement directly in VBSCRIPT (not in a Stored Procedure) it works fine.

Mr Fett
Starting Member

28 Posts

Posted - 2008-01-23 : 10:18:40

Quick Addendum, I've replaced the FREETEXTTABLE search with a standard search ( '%' + searchWord + '%' ) and this works fine, I've also tried:

FREETEXTTABLE(PRODUCT_products, *, @searchWord) (ERROR: ODBC driver does not support the requested properties)
FREETEXTTABLE(PRODUCT_products, *, '@searchWord') (ERROR: Just searches for the string "@searchWord")
FREETEXTTABLE(PRODUCT_products, *, '' + @searchWord + '') (ERROR: Check Syntax)
FREETEXTTABLE(PRODUCT_products, *, '%' + @searchWord + '%') (ERROR: Check Syntax)

Mr Fett
Starting Member

28 Posts

Posted - 2008-01-23 : 13:28:53

Anyone, anyone? Bueller?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-24 : 07:17:32

does your code work outside of the SP environment??? ie. direct in Query Analyser?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources