"Sajid" <sajidm@linkedtechnologies.com> wrote in message
news:1ac501c352b8$5a9eea10$a001280a@phx.gbl...
> 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
> >-----Original Message-----
> >But, I don't believe a cursor is involved in this query.
> >
> >Please correct me if I am wrong.
> >>-----Original Message-----
> >>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" <hilaryK@att.net> wrote in message
> >>news:012001c351fe$033eb490$a301280a@phx.gbl...
> >>> 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
> >>>
> >>> >-----Original Message-----
> >>> >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 analyzer it works great. Any
> help
> >is
> >>> >appreciated.
> >>> >
> >>> >TIA
> >>> >
> >>> >Sajid
> >>> >
> >>> >
> >>> >.
> >>> >
> >>
> >>
> >>.
> >>
> >.
> >