Solved

Using a variable in full text index predicate through ADO

Posted on 2004-10-15
5
747 Views
Last Modified: 2012-08-14
I need to run full text indexing on a table joined with another table using a variable as the full text predicate.

First I tried to run this query directly from the web site:

SELECT styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords, MIN(offerPrice.price) AS lprice, MAX(offerPrice.price) AS hprice
FROM styles INNER JOIN offerPrice ON styles.style_id = offerPrice.style_id
WHERE (offerPrice.defaultFlag = '1') AND CONTAINS(styles.*, '" & strSiteSearch & "')
GROUP BY styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords
ORDER BY '" & sort & "'

I get this error:
ASP Error occurred 10/15/2004 10:16:49 AM in Microsoft OLE DB Provider for SQL Server
Error number: -2147217887 (0x80040E21)
File: /search.htm, line 174
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

To get around that I thought I could just put it into a stored procedure. the stored procedure looks like this:
CREATE PROCEDURE dbo.siteSearch
@strSearch AS varchar(250)
AS
SELECT styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords, MIN(offerPrice.price) AS lprice, MAX(offerPrice.price) AS hprice
FROM styles INNER JOIN offerPrice ON styles.style_id = offerPrice.style_id
WHERE (offerPrice.defaultFlag = '1') AND CONTAINS(styles.*, @strSearch)
GROUP BY styles.style_id, styles.styleName, styles.itemNumber, styles.detailName, styles.shortDesc, styles.longDesc, styles.keywords
ORDER BY styleName
GO

I execute this stored procedure from the web site like this:
Set rsTHUMBS = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "EXECUTE siteSearch '" & strSiteSearch & "'"
rsTHUMBS.Open cmdTemp, , 1, 3
If NOT rsTHUMBS.EOF Then ...

That give me this error:
ASP Error occurred 10/15/2004 10:23:45 AM in Microsoft OLE DB Provider for SQL Server
Error number: -2147217900 (0x80040E14)
File: /search.htm, line 174
A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.

Any work arounds/suggestions? Is there a way to accomplish what I am trying to do?

Thank you very much for your help.
0
Comment
Question by:feti
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12321963
Can you use a wildcard in the first parameter of CONTAINS ?  shouldn't that be a specific column ?

0
 

Author Comment

by:feti
ID: 12321982
I have run this query and stored procedure without error in Enterprise Manager. It's only through ADO that I get the error. The wildcard simply says to check all of the fields in the catalog.
0
 
LVL 6

Accepted Solution

by:
JaffaKREE earned 200 total points
ID: 12322088
I get the feeling this is one of those "not supported" things.   I'm out of my league here, and this is all I found (google cache link):

http://64.233.167.104/search?q=cache:a1OqLW0qSZIJ:65.61.175.198/t883114.html+%22sql+server%22+%22A+variable+cannot+be+used+to+specify+a+search+condition+%22&hl=en
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question