SQL Server Timeout Is By Design When Using Full-text???

Apr 21, 2008

Well, I just spent 2 days tediously debugging an issue with SQL Server 2005 and I have my answer, but I am disappointed... Here's the story. I have a simple Full-Text query integrated into my .NET app. Since app launch I have been getting intermittent complaints that the program does not work, and yet I was never able to reproduce the behavior. I finally got the stack trace from a user and it was a time out exception. On the weekend, I decided to work a bit and the FT (Full-Text) query was the first I tried. Boom - Timeout! Rerunning the query though, does not timeout! Exactly what my users are seeing. Subsequent queries return in the usual 0.5 seconds. So after running many traces over 2 days, I determined that Full-Text queries time out on the first FT query run after 45 seconds and the timeout will happen again if no FT queries are run for 30 minutes. So basically, if we run text queries less frequently than every 30 minutes, we get a timeout every time!!! So with this info, I finally found the fix...

http://support.microsoft.com/kb/915850

So this fix is spot on and the Status says: This behavior is by design. My problem is resolved with this command - sp_fulltext_service 'verify_signature', 0. But, the fix supposedly opens a security hole? So here are my questions:

1. How worried about this security hole should I be?
2. My server has internet access! So why am I getting the timeout? Internet Explorer works fine.
3. So since the query times-out with the verify_signature = 1, why are the subsequent queries allowed to execute? Shouldn't the subsequent queries fail as well since the word breaker is un-verified? Seems to me that if you're gonna let subsequent queries run without signature verification, why make the first one wait 45 seconds and timeout?
4. This timeout event should have AT LEAST shown some event in the trace that the timeout happened... Trace only showed that FT portion of query took almost exactly 45 seconds. Even with all the FT Event Classes turned on I saw nothing indicating a signature verification timeout.

Note: considering the fact that I have Internet access and still see this problem, it may be more widespread of a problem than MS believes. Also, others may be experiencing this and not realize what is happening because of the lack of any Trace events and the inability to reproduce! People may think (as I did) that the SQL Server is flakey or the network is flakey. I'd really like to see a hot-fix to address this.

Thanks,
-Valkyrie-MT

View 6 Replies


ADVERTISEMENT

First Connection Timeout When Using Full Text Indexing

Sep 15, 2006

After rebooting the computer (anytime), the first call to the database will almost always time out.  This did not happen prior to adding the full text index to the database.  It will time out even in the SQL Express Management Suite.

After that first time out, it works fine.

We tried a workaround by making our first call to the database a "dummy" call and wrapping it in a try/catch.

That didn't work either.  Any suggestions?

Regards,

Pat

 

View 4 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

Transact SQL :: Server Text Field Not Returning Full Text

Apr 21, 2015

I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.

View 3 Replies View Related

SQL Server DB Full Text Search

Nov 6, 2004

Where do I find and enable full text search in sql server db?

I have hunted, and hunted, and stumbled, and looked with no luck.

Should it have been installed during set up?

Thanks all,

Zath

View 1 Replies View Related

SQl Server Full Text Search

Jul 19, 2005

I have a server that has windows 2000 with Sql Server Enterprise edition sp3 installed and we are currently using the full text catalogs. I have another server that we have are trying to use the full text catalogs and it does not populate the catalog. I have check the microsoft search service to make sure it is logged in the same. I have gone through and recreated the catalogs twice and they will not populate. Can anyone give me any suggestion of things I can look for, Please ??

View 12 Replies View Related

Full-text Indexing In SQL Server 2005

Jan 5, 2008

in object explorer ,do right-click on database and is selecting preoperties and is selecting "files" page "use full-text indexing" ckeck box is disable.
how can enabled this check box?
thanks , mohsen

View 1 Replies View Related

Microsoft Full-Text Engine For SQL Server

Apr 13, 2008

Hello !
How can I install on  Microsoft SQL Server Express 2005   Full-Text Search Engine ?
I tried to create a full_text_catalog with SQL Server Management Studio Express and I get an error.
Msg 7609, Level 17, State 100, Line 2
Full-Text Search is not installed, or a full-text component cannot be loaded.
 

View 1 Replies View Related

Sql Server: Msg 7607 - Full Text Search

May 30, 2006

hi,

i am getting the above error message when trying to do full text search with containstable on a single table.

i am dynamically creating the search querry condition and some times its even having 18 times "AND" OR "AND NOT" in the search condition.

i have read in MS bug list that there is a limitation of length of the search string while doing FTsearch. it said that its been fixed in SP3 of sql server 2000.

But i am still getting the error 7607 even after installing SP3 on my server.

need advise on resolving this issue..

prad

View 3 Replies View Related

Full Text Indexing Feature Of SQL Server .

Mar 9, 2004

I am using the full text indexing feature of SQL Server .

I have indexed two text fields in the Content table and two other text fields in the ContentDetail table

The select queries are shown below for the two seperate calls.

I want to be able to show the results based on the rank ( The column that has the best match for the searched key ).

Since I am getting two different RANK arrays from the two calls, can i resort them in the code to get the new rank order to show the results in the screen



SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.Title
FROM Content AS FT_TBL
INNER JOIN
CONTAINSTABLE( Content, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC


SELECT KEY_TBL.RANK,FT_TBL.Description, FT_TBL.SubTitle
FROM ContentDetail AS FT_TBL
INNER JOIN
CONTAINSTABLE( ContentDetail, * ,
'ISABOUT (Cancer weight(1),Survivor weight(1), Shared weight(1), Story weight(1))' ) AS KEY_TBL
ON FT_TBL.ContentDetailID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

View 2 Replies View Related

Full-text Allowed In SQL Server 2005

Apr 8, 2007

Hi,

I am putting this question here but I am not limiting it to sql server 2005 express edition.

I am developing an app on a local machine (winxp with sql server 2000 personal edition) however I came to find out that full-text does not work in this setup unless I use a server type machine.

This fouls up my development somewhat and I would like to know if there is
a) a work around for my sql server 200 setup
b) does full-text serach work in sql server 2005 express edition which I have installed on my PC ?

Thanks for any input on this issue.

Tuka

View 2 Replies View Related

Sql Server 2005 Full Text Search Not Working As Expected On Production Server

Jan 28, 2008

I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows,  the full text search works perfect, but as the number of rows increases,  the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)       

View 2 Replies View Related

Change Table Design - TIMEOUT

May 21, 2007

Hi...
I need to alter a table and change the length of one of my fields. I have a couple million rows in the database already and I am getting timeouts when I try to save it after changing the length of the field.
Is there any way to set the database timeout differently for a session in Management Studio.

I have set the Remote Query Timeout property to 0(Unlimited), but my session in SQL Mgmt Studio still gives me the timeout.

If there is no solution to this, can anyone think of something better than this....
SELECT * INTO temp from mytable
DELETE FROM mytable
(Modify Design of mytable)
INSERT INTO mytable SELECT * FROM temp
?

Well, thanks in advance for any help!
-Greg

View 5 Replies View Related

Full-Text Search In SQL Server 2005 Express

Feb 8, 2007

I installed SQL Server 2005 express with advanced services which is supposed to include full-text search capability but I can't get it to work.  When I try to create a full-text catalog it gives me an error because it does not think the full-text service is installed or loaded.  I can't seem to find a reference to the full-text search feature to enable or install it.  any ideas?

View 3 Replies View Related

Full Text Search - Wildcards (SQL Server 2005)

Feb 5, 2008

the sql server documentation states that the use of wildcards is allowed by placing an '*' at the end of the search term.  I can get this to work OK in the SQL Server 2005 query window, heres an example
select ID, SUBSTRING(Title, 1, 100) AS Title, Implemented, Published from Table1 where contains(title,'"Therap*"') ORDER BY Title
 
this works OK and returns a list ot titles with the word Therapy in the title
  Im trying to implelemnt this functionalty in a web app with C#.  The string is passed to a stored procedure.  How on earth do I pass in the quotes ??
Ive tried building the string as normal then adding single quotes on the end, so I get something like
retval = txt + "*";    //txt contains the partial word im searching for, then add the wildcard
then  retval = "'" + retval + "'";  // add the single quotes
and pass txt as a string parameter to my stored procedure.  It doesnt work.  Can anyone tell me what im doing wrong ??
the same query works fine in the SQL query window.

View 7 Replies View Related

Full Text Search On Clustered SQL Server 2000

Jun 21, 2005

I am using SQL Server Enterprise 2000 and have created a full text search on the pubs database as per the instructions below:-

http://www.databasejournal.com/features/mssql/article.php/3441981

However, when I verify the search :-

SELECT title, notes
FROM titles
WHERE CONTAINS (notes, ' "quick easy" ')

no rows are returned.

In the full text tab under the pubs database, my demo catalog is listed, but the Item count and unique key count are both 1. There are 18 records in my titles table.

The full text files location is on my H drive which is set up as a dependency of SQL Server in cluster admin.

In SQL EM->Support Services->Full Text Search->properties, the location of my temporary files was set to C:winnt empgthrsvc. As nothing in cluster admin can see this directory, I changed it to H: emp but still no data is returned by the query. There is no default error log file listed and I can't work out how to change this.

I have checked the Search service is running under the local system account.

I can't find any error messages which may indicate a problem apart from the fact that the T-SQL query doesn't return any data.

Anyone any ideas? Any help is much appreciated.

Thanks in anticipation.

Claire

View 3 Replies View Related

SQL 2012 :: Full Text Search On Production Server?

Jan 29, 2014

Full Text Searches are working on my test server, but not on my production server. My test scenario is as follows:

CREATE FULLTEXT CATALOG FTC_Test
AS DEFAULT
AUTHORIZATION dbo

CREATE FULLTEXT INDEX ON guest.FtsTest(FullName)
KEY INDEX PK_FtsTest ON FTC_TestI wait briefly and then check to see if the index has been populated:
SELECT * FROM sys.fulltext_indexescrawl_end_date is not null,

So I'm assuming I don't have to wait anymore before I try some FTS searches. Right? I can't get any queries to return anything, though.

The following tells me the full text item count for the table is zero:

DECLARE @TableId INT
SELECT @TableId = id FROM sys.sysobjects WHERE [Name] = 'FtsTest'
SELECT OBJECTPROPERTYEX(@TableId, 'TableFulltextItemCount') AS TableFulltextItemCount

As mentioned, the full text search works on my test server. Both of them are SQL 20012 SP1 (11.0.3000) x64 running on WinServer 2008 R2 SP1.

View 3 Replies View Related

SQL Server 2012 :: Full-Text Search On FileTable

Apr 11, 2014

I am new to Full-Text Searches and FileTables. I am working on a resume search system. I created a FileTable and the Share which contains about 51,000 resumes in Word (DOC/DOCX), PDF and TXT formats that were copied over from our main file server. I followed the instructions on adding the Microsoft Filter Pack 2.0, and all searchable file types are listed in sys.fulltext.document_types.

I have a program that searches resumes by First and Last Name and/or E-mail Address. For a while, we thought it was working fine because we were getting hits, but then we noticed some Names and E-mail Addresses we new existed were not showing up in my CONTAINS queries -- but we verified the documents do exist and the Names exist in the File Name and inside the document along with the E-Mail address.

One example I'm working on now cannot find the First/Last Name with CONTAINS(name,"lastname") in a Word DOC file, but I can find the document using LEFT(name,5) = 'Smith'. It's a Word DOC file. For testing purposes, I opened the DOC file in Word, then used Save As to create a TXT and DOCX file within the same folder. Now, both of those are showing up in the CONTAINS but not the original DOC file.

View 0 Replies View Related

SQL Server 2012 :: Using Full Text Search In Standardization

Aug 11, 2014

I'm standardizing street addresses and i think full text search, maybe, work fine with my problem. My problem is that I have 2 millon of errors and using "LIKE" dont work correctly. This is what i have now:

UPDATE addresses
SET standarAddress = REPLACE (addresses.streetAddress, errors.streetAddress, corrects.streetAddress)
FROM corrects
INNER JOIN errors
ON corrects.id = errors.idCorrects

[Code] ....

View 0 Replies View Related

SQL Server 2008 :: Startup Without Full-Text Service

Feb 9, 2015

I am running SQL 2008 R2. I have SQL Full-Text Search installed (part of a standard Automated build) - but disabled and switched-off as the applications don't need it. However as a result I get SQL Server error 9954:

SQL Server failed to communicate with filter daemon launch service (Windows error: Windows Error: hr = 0x80070422(failed to retrieve text for this error)). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

appear in the event / error logs everytime I start SQL server. Is it possible to suppress this error (other than by starting the SQL Full-text service) ...

View 0 Replies View Related

SQL Server 2005 Full-text Indexing Problem

Dec 16, 2005

I just installed SQL Server 2005 and need to create a full-text indexing. I looked up how to do it, but the full-text indexing option is ghosted so i don't even have the option to enable it...any ideas? I tried searching for hours with no luck.

View 1 Replies View Related

Full Text Catalog On Remote (shared) Server

Nov 23, 2005

Can any one help - my Full text catalog on a remote shared sql server hasdied and i need to recreate it completely - I have done this before but i'velost the code to do it.If I remember right what i did was use start --> run --> to run an exe in themssql folder that connected to the remote server - but i've lost all theparameters I needed for it.ANy help much apprectiated

View 1 Replies View Related

SQL Server 2005 Full-Text Thesaurus File

Aug 8, 2007

Hi! I am moving this from the Transact SQL forum as I am not getting a reply. Hope it's OK!
Hello: I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).

I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.

I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.

// This query //

SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')

// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //

<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>

// returns this //

A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.

Appreciate your help, Thank you.

View 1 Replies View Related

PDF Filter For MS SQL Server 2005 Full Text Search

Jan 23, 2008

Hello, I have read on the multiple places that filter for full text search of PDF files using FTS2005 is included in the Reader 8 etc. However, I have not found any document or instruction etc on adobe documents, microsoft documents or web that details on how to actually configure the filter. Please help. thanks
Kumud

View 4 Replies View Related

Thesaurus Support In SQL Server Full Text Service

May 20, 2006

Hi,
 
I€™ve been trying to add keywords to the thesaurus used for SQL Server 2005 Full Text services but do not seems to get any improved results.
For example, I have a catalogue of article titles which contains titles such as €˜CRM€™ and then some others use the words €˜Customer Relationship Management€™, the ideal solution I would have thought was SQL Servers thesaurus support, I€™ve been though and added the values:
 
<expansion>
       <sub>customer relationship management</sub>
       <sub>crm</sub>
 </expansion>
 
to the txGlobal.xml file in the SQL Servers FTData directory, I then amended by test query to use this clause:
FORMSOF(THESAURUS,"CRM")
The idea being that when I search for CRM it will bring back articles with Customer Relationship Management also. However I€™m just getting results for the same results as for the word 'CRM' and no expansion is taking place it would seem.
 
Would anyone be able to clarify what the best approach to making this work would be:

Do I need to restart the SQL Server FullText service after making any changes to the XML files?
(Done this, no sucess)


Do I need to restart the SQL Server service after making any changes to the XML files?
(Done this, no sucess)

Do you need to re-populate the full text catalogues after making a change the XML files?
(Done this, no sucess)
 
If this syntax correct when trying to utilise the thesaurus?
CONTAINSTABLE (Article_Metadata, *, €˜FORMSOF(THESAURUS,"CRM")€™ )
(It doesn;t give any errors, just no improved results over:
CONTAINSTABLE (Article_Metadata, *, €˜"CRM"€™ )
 
 
Any help would be appreciated.
 
Thanks
Ed
 

View 3 Replies View Related

Full Text Search In SQL Server 2005 Express?

May 24, 2006

Newbie questions.

1. Can SQL Server 20005 Express do full text searches?

2. If not, is there a way to use SQL Server 20005 Express to search a database column containing text data type?

Using Visual Basic 2005 Express, I would like to do a simple search with a search textbox and button that will return the entire contents of a field of database text when one or more words in the search text box are in the field of text in the database.

I have been playing in Visual Basic 2005 Express and using SQL queries (SELECT, FROM, WHERE) to output to DataGridView controls by using ID columns as filters in the query, etc. This I can do. But I have not been able to use a word or phrase in the search textbox as a filtered query to output the entire database field of text which contains the search word or phrase in the search textbox.

Thanks for any help in getting me started with this.



View 4 Replies View Related

SQL Server 2005: Full Text Query Failed

Sep 3, 2007

Hi guys,

I am getting a really weird error message when executing a full-text query on SQL server 2005:

-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider."
-------------------------

Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.

The query is not particular complex:

SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID

It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.

Has anybody come across this error before? Any ideas on how I could fix it?

View 14 Replies View Related

Distribution Of SQL Server 2005 DB With Full-Text Search

Sep 8, 2007

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

View 8 Replies View Related

How Many Full-text Catalogs Does SQL Server 2005 Support

Jan 21, 2007

SQL Server 2000 is pretty well documented with the limit of 256 (see http://msdn2.microsoft.com/en-us/library/aa214780(SQL.80).aspx) but I can find no documentation anywhere that discusses the limit on SQL Server 2005.

Is there one ?

Please help !



Thanks

View 3 Replies View Related

SQL Server, Full Text Indexing, And ASP.NET Parameterized Queries

Aug 12, 2006

I've been driving myself nuts trying to get a sensible product search
going. The existing live site search is just a LIKE %searchterm% on
the Title field in our Products table. Fast, but not great ;) Talks
between IT and Marketing have resulted in this being the desired
results and order:

Exact Title match
Substring Title match
Substring Keywords match
Substring Description match

OK, I can easily do this with UNION queries using LIKE (and a virtual
"weight" column in each query), but the query takes too long. So I'm
trying out SQL Server full text indexing, in an attempt to get the
speed up (and the natural language stuff is just plain cool).

Where I'm running into problems is doing a FULLTEXT match on
Description. It seems that to do a phrase match (e.g. "new york"
should match only Descriptions where the phrase "new york" occurs) I
need to enclose the search term in quotation marks in the query (or
maybe single AND double quotes).

But using parameters in ASP.NET (which I'm supposed to do to avoid SQL
injection attacks, yes?) I don't really have full control of the
quoting - ASP.NET and/or SQL Server automagically quotes strings for
me before passing them into the query. I think.

For example, this doesn't find any description matches:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"@theSearchTerm"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

But this one (where I put in the actual string instead of using the
parameter) *does* get the desired results, including matches in the
Description:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"new york"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

Trying various permutations of quotes around the parameter gives me
either syntax errors or undesirable results.


Has anybody tried this sort of thing? How did you do it?

Thanks,

Greg Holmes

View 4 Replies View Related

How To Create Full Text Index By SQL Server Express??

Apr 14, 2007

Hi all..

I tried too much to create FULL TEXT INDEX by using SQL Server 2005 Management Studio Express, it returns this ERR MSG:

Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search

This problem dos not come when I use Microsoft SQL Server 2005 Management Studio, to create FULL TEXT INDEX!

My DB collation is: "Arabic_CI_AS"
But I don€™t need this, I can use English Language.

Please, what can I do?

View 4 Replies View Related

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_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON 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 View Related

Error: SQL Server Failed To Communicate With Full-Text Service

Apr 9, 2007

Hello,
I've enabled full-text indexing on one of my tables, and the following query used to work:
SELECT *
FROM TempAttachment
WHERE CONTAINS(attachment, 'text')


However, now I get the following error:
Msg 9955, Level 16, State 1, Line 1
SQL server failed to communicate with Full-Text Service (msftesql). The system administrator must make sure that same service account is used for both services and the service account has the permission to auto start the full-text service.


I've checked the configuration and verified that both accounts are the same. I've restarted the services, and tried rebooting, and still no luck. I did a search on this error, and found this page from MSDN, which doesn't help me much: http://msdn2.microsoft.com/en-us/library/aa337365.aspx.

Has anybody come across this before? Any help would be greatly appreciated!

View 3 Replies View Related







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