Adding A Full Text Search Across Multiple Tables (with Text Fields)

Sep 7, 2007

Hi, i'm trying to do a full text search on my site to add a weighting score to my results.  I have the following database structure:

Documents:
 - DocumentID (int, PK)
 - Title (varchar)
 - Content (text)
 - CategoryID (int, FK)

Categories:
 - CategoryID (int, PK)
 - CategoryName (varchar)

I need to create a full text index which searches the Title, Content and CategoryName fields.  I figured since i needed to search the CategoryName field i would create an indexed view.  I tried to execute the following query:

CREATE VIEW vw_Documents
WITH SCHEMABINDING AS
SELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID

GO
CREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndex
ON vw_Documents(DocumentID)

But this gave me the error:

Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.

I tried converting the Content to a varchar(max) within my view but it still didn't like.

Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

View 2 Replies


ADVERTISEMENT

Full Text Search With Multiple Tables

Apr 16, 2008

hi friends,


I know how to create full text catalog with single table in single database.

But i need to know the steps to create full text catalog with multiple tables in single database.

View 2 Replies View Related

Full-text Search Across Multiple Tables

Jan 13, 2006

Hello,We are developing an application against an MS SQL Server 2000 databasewhich requires that we implement full-text searching across columns inmultiple tables. The research that we have conducted seems to indicatethat this is not directly possible within SQL Server 2000. And we canfind no way to implement this as the catalogs that are generated aretable specific.As a work-around, we are planning to create a secondary table usedstrictly for searching which is going to denormalize and combine thesearchable data into one catalog. We just want to confirm that this isthe recommended approach or if there is an alternate solution anyonehas used. Please note that upgrading to SQL Server 2005 is not anoption.Thanks.John FlemingJoin Bytes!

View 1 Replies View Related

Full-Text Search On Multiple Tables && Columns

Sep 19, 2007

Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the  [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.

View 2 Replies View Related

Full Text Search In SQL 2005 On Multiple Tables

Dec 28, 2007

Hi, There have been a number of posts on querying multiple tables using full text in SQL Server.  I haven't quite found the answer I'm looking for, and I'm aware that in 2005 some full text functionality changed. I have an Events table which joins to a Venue table and also a many-to-many link (via a table called EventsToArtists) to an Artists table.When a user searches for "Rolling Stones Wembley" (without the apostrophes), I want the query to look in the following columns:Event.EventNameEvent.DescriptionArtists.ArtistNameVenue.VenueNameI have done quite a bit of testing on this, and haven't yet got the desired results.  Rolling Stones will be found in the Artists table, and Wembley in the Venue table.  At the moment, if I search for Rolling Stones, it behaves as expected.  But if search for the venue name as well I get no results.  I'm pretty sure this is because EventName wouldn't contain all key words and so the record is excluded from the results.  At the moment I am using INNER JOINs between the tables....perhaps I should be executing a full text search on each table's catalogue and then do a UNION to join the resultsets?Hope you can point me in the right direction.Thanks 

View 5 Replies View Related

Full Text Search With Multiple Tables In Sql Server 2000

Apr 17, 2008

hi friends,
            i need the steps for full text search with more than one tables in single database. I know the steps for full text search by single table in single database.
 Thanks in advance

View 1 Replies View Related

SQL Server 2000 Full Text Search (extract Pieces Of Text)

Sep 12, 2007

Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
 Best regards.
Yannick

View 5 Replies View Related

Best Full-Text Indexing On Multiple Tables

Apr 7, 2006

Hi, I'm using SQL Express Advanced CTP and I'm in the process of adding a search facility to an asp.net site. I'm having a huge problem deciding the easiest and most optimised method to use when creating the index using multiple tables and where I can't seem to to produce a unique, clustered index.
The site I'm testing on is a portal in which we have various, different clients advertising their business.
A user will be able to search using keywords such as business activity,  business location and anything else that may appear in the business / publicity descriptions.
A publicity (publicity_id) may belong to one or more TOWNS (ville_id), e.g., Business A might have a promotional offer that applies to three local towns, Business B to all towns and Business C to just one town.
A publicity  may belong to one or more ACTIVITIES (activity_id), e.g., Business D is a resturant but has also a bar and Business E belongs to prĂȘt-Ă -porter, fashion, and beauty.
I'm using a view (ActivePubs) to produce a query across multiple tables Clients (business name), Publicities (publicity descriptions), Towns and Activities.
So from the nature of how the publicities are connected to activities and towns, I have many to many relationships that produce duplicate publicity_id values when I create the view, and thus this provides a problem when trying to create a  unique, clustered index on ActivePubs; and none of the coloumns produce unique values that monotonically increases, ideal, I believe, for creating unique, clustered indexs. I have also read that it is only best to use interger values for creating unique, clustered indexes. 
I thought that if I SELECT the row number in ActivePubs,
SELECT ROW_NUMBER() OVER (ORDER BY dbo.Publicities.publicity_id) AS ROWNUM, ...
in addition to the rest of the query, then I might be able to use the unique values from the query result to create the unique, clustered index
CREATE UNIQUE CLUSTERED INDEX CL_Inx_Pubs ON ActivePubs(ROWNUM)
but instead this produces the following error... 
Cannot create index on view "ActivePubs" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view.
So I scrapped this idea but without this method using just
CREATE UNIQUE CLUSTERED INDEX CL_Inx_Pubs ON ActivePubs(publicity_id)
I get the following error...
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'ActivePubs' and index name 'CL_Inx_Pubs'. The duplicate key value is (32).
I did consider NOT using a UNIQUE clustered index, but  got this error...
Cannot create nonunique clustered index on view 'daysandnights.ActivePubs' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.
The only other method I think about using is storing the query in a temporary table instead of using a view, then creating an additional column in the temporary table to store a unique, automatically incremented, PK for the table to give me my unique index, but my guess is that this is overkill?
Much appreciate any help you can provide.
Deakus

View 1 Replies View Related

Free Text Search Across Multiple Tables

Nov 30, 2006

Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.

View 3 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

SQL Search :: Full Text Search With Single Character Returns All Rows

Jul 21, 2015

Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.

Here is an example

create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;

[Code] ....

View 4 Replies View Related

SQL Server 2014 :: Semantic Search Not Finding Keywords Identified By Full-Text Search?

Nov 6, 2014

I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.

For instance, if I put the following string into a FT indexed table

'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'

The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'

Nor in

'J8999 - Everolimus (Afinitor)'

It did not recognize 'Afinitor' as a keyword.

In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.

why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?

View 0 Replies View Related

Create Site Search Using Sql Server Full Text Search

Jul 24, 2007

would you use sql server "full text search" feature as your site index?  from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think? 

View 1 Replies View Related

SQL Search :: Full Text Search Of PDF Files In A File Table

Mar 30, 2013

I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.

--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY

[code]....

View 14 Replies View Related

How Can I Search Throught DOCX (MS Word 2007) Documents By SQL Server 2005 Full Text Search Engine?

Dec 11, 2006

How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?

Should I something download?

View 6 Replies View Related

Full-Text Search: Prefix / Suffix Search

Sep 14, 2004

Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.

Example:

Say I want to find "Terminator" (1984).

I want to be able to use "Term" or "ator" as search results and still return the proper record.

Here is my Stored Procedure creation sql:


CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@searchTerm*"')
GO


--- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms.

I am trying to accomplish what is similarly done with LIKE '%term%'.

thanks, YM

View 1 Replies View Related

Full Text Search- Substring Search Not Working

Jul 6, 2007

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.

View 7 Replies View Related

SQL Search :: Can't Get Expected Results With Contains And Full Text Search?

Nov 1, 2015

I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:

123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999

I am trying this query:

select * from Mytable where
contains(MyColumn, '123.345.')

I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:

select * from MyTable where
MyCOlumn like '123.345.%';

View 12 Replies View Related

Why I Always See That Full Text Search Is Always Slower Than LIKE Search?

Apr 2, 2004

for example:

SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')

would always slower than

SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'


Why? and how can it be solved? can you help me?

:)

View 6 Replies View Related

SQL Search :: Full Text Search Anomaly

Jun 24, 2015

I'm experiencing an anomaly with my Full text index.

Setup : SQL Server 2005
Indexed Table:
Assets
ID - int (PK, auto increment)
Ref - varchar(50)

[code]....

I have re-built the full-text index, no change.

View 2 Replies View Related

Full Text Search Help

Apr 23, 2007

 Hi I have a full text index on my product table. When I do a search for Record, it returns all values for Record and Records.Now If I do a search with a spelling mistake say Recod . it doen't return anything.How can I get the full text to return my query even if there is a spelling mistake ? Thanks  My query:SELECT *  From Product        WHERE FREETEXT (description, @SearchString)

View 5 Replies View Related

Search Text Full

Dec 10, 2007

hi all.i want to search, for example :"test string" in database :  table have column(name) , i want to search all rows with  column(name) is "test " or "string" or "test string"i don't want to use(full text search of sqlserver 2005 ) can i help me.thanks in advance 

View 1 Replies View Related

Full-Text Search

Apr 19, 2008

Hello !
With SQL Server Management Studio Express I have created a catalog and a index.Here is the code :
create fulltext catalog myfirstcatalogcreate unique index myfirstindex on northwind.dbo.customers(companyname)create fulltext index on northwind.dbo.customers(companyname)key index myfirstindex ON myfirstcatalogWITH CHANGE_TRACKING AUTO  
With SQL Server Management Studio Express and the following command the full text search is working fine.
select companynamefrom northwind.dbo.customerswhere contains(companyname, ' "blauer" ') 
I have a big problem :
When I try to use this database (NOTRHWIND.MDF) into my .aspx file with VWD 2008 I get an error :
Cannot use full-text search in user instance.
 
Can you tell me what can I do to make use of full-text search inside my aspx pages ?
Thank You !

View 5 Replies View Related

SQL Full Text Search

May 4, 2005

Does anyone know how to use SQL Full Test Search with ASP.NET?

View 2 Replies View Related

Full-Text-Search

Mar 3, 2006

Hi friends
i work with sql server 2005 express(Management Studio)
can i use full text search service with that edition of sql server?
and how i can use that.
thank u.

View 6 Replies View Related

Full Text Search

Mar 24, 2006

I am asking about installing Microsoft Search service in SQL Server 2000 in Windows XP Professional
Is that possible?
And By the way it's not possible for me to switch to SQL Server 2005 that I know that full text indexing already exists in it
 
thanks

View 1 Replies View Related

Full Text Search

Jul 23, 2002

After following all of the steps in Swynk's Guide to Full Text Search, I still can't seem to retrieve any records from the full-text search. I get messages saying that I created the Index correctly and that I successfully populated the catalog. But when I View the Properties of the catalog, the Unique Word Count = 1 (but there are thousands of words in the table).
I know that there is a record with the word "help" in one of the full-text indexed columns, but when I call Contains(column-name, 'help'), I recieve an empty recordset.

Does anyone have any suggestion on what I can try next?

Thanks for any help,
Arthur

View 1 Replies View Related

Full Text Search

Mar 20, 2001

Hi,

My Prod. server is having SQL Server 7.0 with SP2 installed. I am planning to install Full Text Search component. I know I have to run setup and select Full Text Search from Server Components. But, Do I have to re-install service pack 2 after this ? What are the other concerns in, such modifications to the existing SQL installation ?

Thanks

View 1 Replies View Related

SQL Full Text Search

Jul 19, 1999

Greetings ! Has anyone seen a problem in SQL 7 where you create a full text catalog, define an index on a table, and run 'populate', and end up with an empty catalog ? It runs for a while during the population, and there are no messages in the logs, but the 'item count' property of the catalog is 0.
Also you can run a query that uses the index (i.e. uses 'CONTAINS') and you receive no error, and no results.
I've set this up on other servers, so I know it works, but can find no mention of this problem anywhere.

TIA

Eric

View 1 Replies View Related

Full-text Search

Aug 19, 2002

Hi, I got a problem in my SQL2000 server full-text search function. When I created the full-text catalog and index on tables, I got the message showing it is successful. But when I checked the properties of the catalog, I found "item count" is zero. Apparently, it didn't populate any data into index. What could the possible reason be? Any idea? I appreciate your comments.

Diego

View 2 Replies View Related

Full Text Search

Aug 8, 2004

Hi everyone

I am using SQL server 2000 with SP3 on Windows 2003 server.

I have scheduled a job to populate a full text index incrementally. Everytime it runs the job to populate the index table it writes some messages into event log.
Like this:
1.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3019
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:1
The crawl on project <SQL Server SQL00002000009> has started.

2.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3047
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:
The end of the incremental crawl for project <SQL Server SQL00002000009> has been detected. The Gatherer successfully processed 998 documents totaling 0K. It failed to filter 0 documents. 998 documents were modified. 0 URLs could not be reached or were denied access.

The event log is getting filled frequently.

Is there any way to stop this messages?
It is normal for Microsoft Search to write this message into EventLog?
Even though there is no chanage in the database it give the above messages.

Any one know the reason please help.

Quick reply is appreaciated.

Reagrda,

Atiq Rahman

View 2 Replies View Related

Full Text Search

Oct 25, 2007

Hi,
We have a windows 2003 server (standalone) running SQL server 2000. We need to enable full text search on the database available in it. We have already added full text search in the installation and the option is available but when we try to create a full text catalog on any of the databases, it crashes ie., doesnot respond and gives the typical 'report to microsoft window'. We need to restart the SQL server after the same. can someone help us out.
Thanks
Smith

View 6 Replies View Related

Full Text Search

Jan 20, 2004

Hi

This is Yogesh Jangam
I m using SQL Server 7.0 and ASP for an intranet application
I am using Full Text Search feature os SQl 7.0.

The problem is i am not sure where to populate my Catalog which i am using for searching Articles. Basically, Various writers will be submiting, approving Articles which can then be searched by other users to be read or referenced

i was thinking to populate full or incremental, once an Article is approved, but i think it is time and resource consuming


any way

suggest me!!

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved