Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using a field in FREETEXT index

Posted on 2009-04-20
9
Medium Priority
?
350 Views
Last Modified: 2012-08-13
I'm trying to create a view that searches for a field from one table in a FREETEXT index, but it doesn't seem to like me using a field name there instead of a variable.  Any way I can get around that?

Error:
Error in WHERE clause near 'dbo'.
Unable to parse query text.
SELECT     dbo.[Equipment Notes].[Order ID]
FROM         dbo.[Equipment Notes] CROSS JOIN
                      dbo.[Form Equipment Order Search]
WHERE     FREETEXT(dbo.[Equipment Notes].Note, dbo.[Equipment Notes].[Notes Contain])

Open in new window

0
Comment
Question by:rogermccoy
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:ALaRiva
ID: 24188948
I've never attempted to use FREETEXT with data from a field, HOWEVER, one thing that I see is that you are calling the field "NOTE" and the field "NOTES CONTAIN", from the same table.

I'm assuming that is a typo, and you intended to call NOTES CONTAIN field from "Form Equipment Order Search".

Also, You should avoid spaces in field and table names, it is considered bad practice, and will cause you headaches in the future.
0
 

Author Comment

by:rogermccoy
ID: 24189010
Oops, query should have read as follows.

Same error though, even without the table name.
SELECT     dbo.[Equipment Notes].[Order ID]
FROM         dbo.[Equipment Notes] CROSS JOIN
                      dbo.[Form Equipment Order Search]
WHERE     FREETEXT(dbo.[Equipment Notes].Note, dbo.[Form Equipment Order Search].[Notes Contain])

Open in new window

0
 
LVL 10

Expert Comment

by:ALaRiva
ID: 24189032
As I said, I have not used FREETEXT with values from another table, so I don't know if that could be a limitation, but I'm going to venture and say so.

However, since it seems you are running this procedure with data from a form from some interface (Access, VB6, .Net, etc.) then why not convert this into a Stored Procedure, pass in the search form data, and return the result.

Or do you need to store the search data they enter?

- Anthony
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rogermccoy
ID: 24189072
I need to store it for a little while.  I dump it into a table that's used to browse results in an Access form.
0
 
LVL 10

Accepted Solution

by:
ALaRiva earned 500 total points
ID: 24189090
ok, then, for a workaround, unless anyone can confirm that it is possible to use FREETEXT() with search data from a table.

Run the stored proc to return this result, THEN store it in the table.  So you'll be able to get your result properly, and still store it.

If you need help with this, just let me know.

HTH
0
 

Author Comment

by:rogermccoy
ID: 24189146
I was thinking that as a possibility, but was hoping to avoid it.  I'm going to correct myself on an earlier comment here:

> I dump it into a table that's used to browse results in an Access form.

I was thinking of another, similar, app that I'm working on simultaneously.  Should have read:

> I use the view as a linked table used to browse results in an Access form.

So, in practice, I was trying to avoid an intermediary table or having to execute a stored procedure on this one.  Normally no code is executed for the search; the join in the view handles the other (non fulltext-indexed) search fields automatically, so I was trying to avoid extra steps.

I'll hold out for a while to see if there's another workaround... If I don't get one in a few days, I'll mark your solution as accepted.
0
 
LVL 10

Expert Comment

by:ALaRiva
ID: 24189162
10-4, if I think of something else to offer and have time to test it, I will.

Otherwise, good luck.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24190515
You cannot do this (not even wth a Stored Procedure).  It is looking for a literal value.  The FREETEXT definition of the second argument is:

freetext_string:
Is text to search for in the specified column. Any text, including words, phrases or sentences, can be entered.
0
 

Author Closing Comment

by:rogermccoy
ID: 31572478
Good enough.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

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…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 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