sajid

Hi,

I've the FTS in a stored procedure and i am calling the
stored procedure from ASP program. Here is the stored
procedure :

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure


CREATE PROC dbo.spWebSearch
(
@PriceID int,
@SearchTxt nvarchar(100),
@StoreID char(2)
)
AS

-- Code for Beavercreek
if @StoreID = 'BC'
Begin
select S.[KEY], S.RANK, PQ.ProductName,
PQ.Description, P.UnitPrice, PQ.ProductID, P.UnitPrice *
100 as list_price FROM (Products AS P INNER JOIN
Product_Quantity AS PQ ON P.ProductID=PQ.ProductID) INNER
JOIN ContainsTable(Product_Quantity,*,@SearchTxt) AS S ON
PQ.ProductID = S.[KEY] Order by S.Rank Desc;
End
GO

This is how i am calling it from the ASP page

PriceID = 1
StoreID = "BC"
str = "floppy"

spStr = "exec spWebSearch " & PriceID & "," & "'" &
newstring & "'" & "," & "'" & StoreID & "'"
rsProducts.Open spStr, MSCS ' Here MSCS is the connection
string.

I get the following error :
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]A variable
cannot be used to specify a search condition in a fulltext
predicate when accessed through a cursor.

/result.asp, line 173

Can't we pass the search criteria as a variable to a FTS
stored procedure? Also please note that i am using
clientside recordset cursor. If i execute the same stored
procedure from query ****yzer it works great. Any help is
appreciated.

TIA

Sajid

john kane

Sajid,
What is the SQL Server version (7.0 or 2000) and on what OS platform is it
installed? Could you post the full output of:

select @@version

Depending upon the exact version info, you may of hit a bug in SQL Server
that *might* be fixable via a SP upgrade, but this depends upon the SQL
Server version that you are currently using.

Regards,
John

hilary cotter

First of all, this is a horribly inefficient way of
calling an sp. It should be called using the command
object.

Secondly you are essentially expecting SQL Server to do
interpolation (or filling in the value of the variable)
which is something that it does not do.

You should try something like this.

create PROC dbo.spWebSearch --1, 'search text', 'bc'
(
@PriceID int,
@SearchTxt nvarchar(100),
@StoreID char(2)
)
AS
declare @strSearch as char(500)
-- Code for Beavercreek
if (@StoreID = 'BC')
Begin
set @strSearch='select S.[KEY], S.RANK, PQ.ProductName,
PQ.Description, P.UnitPrice, PQ.ProductID, P.UnitPrice *
100 as list_price FROM ProductQuantity as PQ, Products AS
P,ContainsTable(ProductQuantity,*,' + char(39)+ char(34)
set @strSearch=rtrim(@strSearch) + rtrim(@SearchTxt) +char
(34) +char(39) +') as S where P.ProductID=PQ.ProductID
and s.[KEY]=PQ.ProductID '
print @strSearch
end
exec sp_sqlexec @strSearch

fulltext

john kane

Sajid,
The use of variables in a search condition for FTS when used with a cursor
is a bug in SQL Server 2000 (Shiloh bug# 233886) that *might* or might NOT
be fixed in the next release of SQL Server. So, as Hilary suggests it's best
to re-write this proc and remove the cursor.

Regards,
John

hilary cotter

But, I don't believe a cursor is involved in this query.

Please correct me if I am wrong.

used with a cursor

*might* or might NOT

suggests it's best

sajid

Hi,

There are no cursor's involved. Also i would like to thank
you for helping out with this problem. The solution you
gave me worked.

Regards,

SAjid

Hilary

john kane

Hilary & Sajid,
I was responding to the below ODBC error and question:

"[Microsoft][ODBC SQL Server Driver][SQL Server]A variable
cannot be used to specify a search condition in a fulltext
predicate when accessed through a cursor. "

"Can't we pass the search criteria as a variable to a FTS
stored procedure? Also please note that i am using
clientside recordset cursor"


Regards,
John