April 21, 2004 at 7:23 pm
The Microsoft SQL full-text indexing seems
to be working fine, however why does it only
work when I insert a value and it fails to
work when I insert the @txt varaible like
the original StoredProc below ?
Replaced "name like '%' + @txt + '%'" with
1. FREETEXT(NAME, 'ginger') --> works
2. FREETEXT(NAME, 'txt') --> fails
3. FREETEXT(NAME, @txt) --> fails
4. FREETEXT(NAME, '@txt') --> fails
------------------------------------------
** Originial **
CREATE PROCEDURE p_SEARCH_PRODUCTS
@TXT VARCHAR(500)
AS
DECLARE @today DECIMAL
set @today = DBO.DATETOMS(GETDATE())
SELECT
0 CATEGORY_ID,
P.PRODUCT_ID,
0 CAT_COUNT,
P.NAME,
P.SHORT_DESC,
P.THUMBNAIL,
MIN(S.PRICE) PRICE,
P.NAME_EXT,
0 PACKAGE_FLAG
INTO #OUT
FROM
PRODUCT P,
SKU S
WHERE
P.PRODUCT_ID IN (
select PRODUCT_ID from product where
name like '%' + @txt + '%'
)
AND P.PRODUCT_ID=S.PRODUCT_ID
AND @today between P.DATE_ACTIVATE AND P.DATE_DEACTIVATE
AND P.ACTIVE = 1 AND (P.DELETE_FLAG = 0 OR P.DELETE_FLAG IS NULL)
GROUP BY P.PRODUCT_ID,P.NAME,P.SHORT_DESC,P.THUMBNAIL,P.NAME_EXT
UPDATE #OUT SET PACKAGE_FLAG = 1 WHERE PRODUCT_ID IN (
SELECT PRODUCT_GROUP_PRODUCT_ID FROM PRODUCT_GROUP G WHERE
PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
) -- 1 IS QUALITIES USED FOR PACKAGE OPTIONS
OR PRODUCT_ID IN(
SELECT PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
AND PRODUCT_ID <> PRODUCT_GROUP_PRODUCT_ID
)
SELECT top 100 * FROM #OUT
GO
April 26, 2004 at 8:00 am
This was removed by the editor as SPAM
April 29, 2004 at 11:47 am
Check if your server does repopulation for full-text catolog after modifiing text field.
Alex
May 3, 2004 at 8:39 pm
It still fails
Please reply
May 4, 2004 at 3:18 pm
Did you do next actions?
- via EM find your DB and select full-text catalogs
- right click on catalog you are using and select "rebuild catalog"
Alex.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply