Full Text Index
Could Full Index option only be configured during installation? When I
try sp_fulltext_table on a table, I get the message that full text is
not enabled for the system.
--sharif
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Clustered Index Vs. Full Text Index
Quick question about the primary purpose of Full Text Index vs. Clustered Index. The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself? I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database. Is this correct? I am kind of confused on why you would use full text index as opposed to clustered index. Thank you Goldmember
View Replies !
View Related
Full-Text Index
I am having an issue creating full indexes on both instances of an ActiveActive SQL Server 2000 cluster. I get the following error when trying to create the catalog: Access is denied to $SQL PATH$, or path is invalid. Full-text search was not installed properly. Does anyone have any suggestions that I may use to create the indexes?
View Replies !
View Related
Full Text Index
I am trying to enable full text index on all of my databases but notices that it is grayed out. Also the service Full Text Index service msftesql.exe is not installed. I have tried running the install again but it says nothing has changed on the machine so it just stops the install... Hope someone can help me.
View Replies !
View Related
MS SQL Full-text Index Search
First of all I’m new to MS SQL, I did work with mySQL Table name db (real db has 12 columns) Id c1 c2 c3 1 tom john olga 2 tom john olga bleee I enabled full text index on all columns Problem when I do search like this: SELECT * FROM db WHERE CONTAINS(*,'�tom� AND “john�') It will return only one row (id 2) – I understand that the full text search does look only at one column at a time because it did not return row #1 Anyway I thought that I can add extra column c4 and when user enters new data it will save data from columns c1, c2, c3 to c4 (varchar(750)) and then I will do search only on c4 – this way it will work the way I want. 1) Is there any better way to do this? 2) How do I sort results by “rank� with SQL
View Replies !
View Related
Full Text Index On A View
What is the procedure to create a full text index on a view? I createdfull text indexes on the underlying tables for a view, but when thequery is run against the view I am getting the errror message:Server: Msg 7601, Level 16, State 1, Line 1Cannot use a CONTAINS or FREETEXT predicate on table'dbo.apcgd_globaldir_master' because it is not full-text indexed.If there is a document which outlines steps to create a full text indexon a view that would be great.Thanks in advance.Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
View Related
Population Of Full-text Index
Hello , i need to work with a full-text index. Everything works fine. When i start EXEC sp_fulltext_catalog 'WGtestCatalog', 'start_full' nothing happens and the population is 0. Can anyone please give me a hint.. thanks Werner
View Replies !
View Related
Full Text Index Not Populating
I have a table with 13,000,000 records. I want to generate a full-text index on one column (a varchar 2000). I am able to define the full-text index, but when I click on "Start Full population", there is virtually no activity (no disk activity, no CPU activity, very little to indicate anything is happening. When I check the properties of the catalog, it shows 1 MB size and 0 records in the catalog. The status of the catalog is "idle" and the display in EM shows that the last full population occurred at (about) the time that I generated the population request. I have generated the request by using EM (right click on table) and through SQL Agent with the same result (no catalog generated). I am running SQL 2000 (SP4) on Windows 2000 (SP4) with 4 GB RAM and sufficient disk space available. I have enabled the full-text service and verified that it is running (I have stopped and restarted it as well). I have worked with Full Text indexes before and never had any kind of issue before. Any thoughts or suggestions would be welcome. Regards, hmscott CREATE TABLE [OMBRE_AUDIT_LOG] ( [LOG_SEQ_NBR] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [APP_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [USER_ID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [USER_ORGANIZATION] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ACTION_START_DATE] [datetime] NOT NULL , [ACTION_END_DATE] [datetime] NULL , [ACTION_CODE] [int] NOT NULL , [VIEW_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [USER_DEF_TRACKING_NBR] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CMD_XML_STREAM] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [REC_CREATE] [datetime] NULL CONSTRAINT [DF_OMBRE_AUDIT_LOG_REC_CREATE] DEFAULT (getdate()), [REC_UPDATE] [datetime] NULL , [ATTENTION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [REASON] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_OMBRE_AUDIT_LOG] PRIMARY KEY CLUSTERED ( [LOG_SEQ_NBR] ) ) GO
View Replies !
View Related
Full Text Index Wizard
Can any one tell me how to enable a database or table to create a full text index on a particular table. Iam selecting the database and just clicking the tools, Iam unable to see the full text wizard in the drop down menu as suggested in BOL. Help in this regard is highly appreciated. Thanks. sv
View Replies !
View Related
Full Text Index Scheduling
Dumb question: What vb/asp/dts method would display the last time a full text schedule had been performed by the SQL-7 server? I'm getting request to be able to view this type of stuff and I was thinking a good report would satisfy this situation. Sorry for the dumb question.
View Replies !
View Related
Full Text Index Return All
I've got a full text index working with a "CONTAINS" clause in the SQL. I'm looking for the character that I can place in CONTAINS(*,'WHATHERE') that will return everything. I've tried "*" and "%" but none of them will do it. Does anybody know? Thanks
View Replies !
View Related
Replication For Full-Text Index
I have built a Full-Text Index on a indexed view. I'd like to replicate this indexed view from a control database to a live database. What values should I specify for @type and @schema_option for the sp_addarticle sproc to ensure the Full-Text Index is still functional after it's replicated? For now, I have set @type="indexed view logbased" and @schema_option=0x90000F3. Are these values correct? Could anyone give me some advice on this? Thank you very much, Dandan
View Replies !
View Related
Is Full-Text Index Really That Much Overhead?
I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion. Is there any statistical data that could possible support or refute his statements? Thanks
View Replies !
View Related
Full Text Index Error
Hi, I build some t-sql code to check if full text is installed on the sql server. If not, some sql statements must be not executed. Here is my code: if (select serverproperty('IsFullTextInstalled')) = 1 Begin EXEC sp_fulltext_database 'enable' CREATE FULLTEXT CATALOG [...] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT CREATE FULLTEXT INDEX ON dbo.Test (Name LANGUAGE 0, Description LANGUAGE 0) KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON dbo.Test ENABLE End Statement 1 and 2 is not executed, but for statement 3 the server throws the following error: Full-Text Search is not installed, or a full-text component cannot be loaded. I don't know why the server tries to execute statement 3, because it is in an if statement. Any help is welcome.
View Replies !
View Related
Full Text Index Not Updating
I am tring to use full text indexing. I have created an index and catalog. I can search on stuff that was entered before I created the index using contains or freetext but if I search on anything afterwards the results come up blank. I have created the following database and tables. I am using sql express with advanced services. The primary key I went in after I created the tabled and modified the row to increment by 1 create database RSDB2 use rsdb2 create table support (ftid int NOT NULL PRIMARY KEY, problemId varchar(50) NOT NULL, problemTitle varchar(50) NOT NULL, problemBody varchar(max) NOT NULL, lOne varchar(50), lTwo varchar(50), lThree varchar(50), lFour varchar(50),) create fulltext catalog RSCatalog AS DEFAULT create unique index ui_Support on support(ftid) create fulltext index on support(problemBody) key index PK__support__7C8480AE on RSCatalog insert into support(problemId, problemTitle, problemBody) values('win1001','testing outt he database','testing out the databases full texting capabilities again.') select * from support where freetext(problemBody, 'testing');
View Replies !
View Related
Full Text Index Migration
How to migrate FULL TEXT indexes from SQL SERVER 2000 to 2005? Is it okay if I migrate the MSDB DB? Do i need to create the physical folders manually? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
View Related
SQL 2005 - Full Text Index Question
Hi!I'm using the Thesaurus search on a SQL Server 2005 DbThe thesaurus file is built like this (Reduced version)<XML ID="Microsoft Search Thesaurus"><thesaurus xmlns="x-schema:tsSchema.xml"><diacritics = false/><expansion><sub>William</sub><sub>Billy</sub></expansion><expansion><sub>Maria</sub><sub>Mary</sub></expansion><expansion><sub>Paul</sub><sub>Pablo</sub></expansion><expansion><sub>Richard</sub><sub>Ricky</sub></expansion><expansion><sub>Rebecca</sub><sub>Reba</sub><sub>Becky</sub></expansion></thesaurus></XML>And so on.It is working perfectly, but how can I make SQL Server aware of thefact that I changed (Adding expansions) the file without rebooting thewhole server?As of now I can add all the changes, but the server doesn't recognizetha new extensions until I reboot the machine. Why?P
View Replies !
View Related
Full-Text Index In Enterprise Manager
I've been trying to create a full-text index using Enterprise Manager. If I right-click on the table, "Full-Text Index Table" is grayed-out. If I right-click on Full-Text Catalogs, "New Full-Text Catalog" is grayed-out. If I try to start the Full-Text Indexing Wizard it tells me that the "Full-Text Server service needs to be running." The SQL database is on a remote server, and the host assures me that everything on their end is working properly. Does anybody know what I have to do??
View Replies !
View Related
Full Text Search Index Catalog
I have tried the full text search index catalog for searching text data. It seems to work reasonably fast for upto a 100,000 records but the performance fall considerably once you increase the database size to half a million records. Can any one suggest what measures should be taken to optimize the full text search index so that the retrieval speed can be acceptable, typically say less tha 15 to 20 seconds. Thanks
View Replies !
View Related
Full Text Index: Drop Vs. Disable
Hi, I need to bcp records into tables with full text indexes. As I understand, it is a good idea to remove indexes prior to inserting records for performance reasons. All articles I've read about it mention dropping indexes and recreating them after a table is updated, but sp_fulltext_database allows to disable indexes without dropping them. Here is the description of the 'disable' option from MS site: Removes all full-text catalogs in the file system for the current database and marks the database as being disabled for full-text indexing. This action does not change any full-text index metadata at the full-text catalog or table level. Does anyone know what is the best way to remove indexes before bcp-ing data in? Thanks in advance, Yana
View Replies !
View Related
Full-Text Index On Big Tables Questions
In my lab we are currently using Crystal Reports to sometimes search through varchar(255) fields for embedded text strings. Sometimes these searches take long periods of time due to the sizes of our tables (~40-50 million rows). Has anyone had experience with creating full-text indexes on tables of this size? How big can I expect this index to be? Will I really see that much of a performance increase?
View Replies !
View Related
Full Text Index On US English String
Hi, The following queries on a fulltext index is returning different results. select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'A.C.E') -- returns 1388 records select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'ACE') -- returns 1388 records select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'ace') -- returns 1388 records select CustomerNameLocal from dbo.Customers where contains (CustomerNameLocal,'a.c.e') -- returns 22 records Can someone let me know why the last query is retuning only 22 records. Since search on ACE and ace returns the same number of records - I guess there shouldn't be any problem with case sensitivity. Thanks, Loonysan
View Replies !
View Related
Problems Creating Full Text Index
Hi all, Im new to sql and very interreseted in the Full text features, however when im trying to execute the following query: USE Updater CREATE FULLTEXT INDEX ON dbo.Servers (ServerName) KEY INDEX ServerID ON UpdaterCatalog WITH CHANGE_TRACKING AUTO GO Where ServerID = Int NOT NULL IDENTITY and ServerName = VarChar(255) NOT NULL and UpdaterCatalog is just created I get the following error: Msg 7653, Level 16, State 1, Line 3 'ServerID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key. I cant seem to figure out why this wont work since unless im mistaking, both fields are legal. Note that creating an index on any other table doesn't work either. Im running Sql server standard edition (32 bits) on VISTA Ultimate X64 Thanks in advance, Koen
View Replies !
View Related
Full-Text Bug - The Content Index Is Corrupt
Hi, I'm using Sql Express with full-text-search enabled. The search worked great until last week, suddenly, I started to give the following error message: The execution of a full-text query failed. "The content index is corrupt." I've found this KB article: http://support.microsoft.com/kb/938243 And followed the steps they suggested, but it changed nothing - I'm having the same problem. Any idea, someone...?
View Replies !
View Related
Get Full-Text Index Structure Info!!??
hi there! how can i get the information represented in the table? Keyword ColId DocId Occ Crank 1 1 1 Arm 1 1 2 Tire 1 1 4 Maintenance 1 1 5 Front 1 2 1 Front 1 3 1 Reflector 1 2 2 Reflector 1 2 5 Reflector 1 3 2 Bracket 1 2 3 Bracket 1 3 3 Assembly 1 2 6 3 1 2 7 Installation 1 3 4 The Keyword column contains a representation of a single token extracted at indexing time. Word breakers determine what makes up a token. The ColId column contains a value that corresponds to a particular table and column that is full-text indexed. The DocId column contains values for a four-byte integer that maps to a particular full-text key value in a full-text indexed table. DocId values that satisfy a search condition are passed from the MSFTESQL service to the Database Engine, where they are mapped to full-text key values from the base table being queried. The Occ column contains an integer value. For each DocId value, there is a list of occurrence values that correspond to the relative word offsets of the particular keyword within that DocId. Occurrence values are useful in determining phrase or proximity matches, for example, phrases have numerically adjacent occurrence values. They are also useful in computing relevance scores; for example, the number of occurrences of a keyword in a DocId may be used in scoring. http://technet.microsoft.com/en-us/library/ms142505.aspx thanks
View Replies !
View Related
Can Not Create A Full Text Catalog Or Index
When I try to create a full-text catalog on my local database I get an error that I can not find support information for. Here is the command I run :CREATE FULLTEXT CATALOG asset_search_values_catalog on FILEGROUP ftFileGroup IN PATH 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' as default Here is the error message I get: Msg 7689, Level 16, State 1, Line 1 Execution of a full-text operation failed. 'No such interface supported' I check the properties of my database and Full-text is enabled. I am running SQL Server 2005, on an XP Pro, SP2. I had originially installed in side by side with SQL Server 2000. I even tried uninstalling SQL Server 2005 (to try a re-install), I could not even uninstall the database. What should I do.
View Replies !
View Related
Copy Database With Full-text Index
Hi, Can anyone please explain the proper precedure for copying a SQL Express database between two instances? I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application. I run into two problems every time I copy: 1) Orphaned users. I have to drop the database users and the re-map the server users to database users. 2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog. And I suspect there's an easier way.. Regards, Jens Erik
View Replies !
View Related
Full Text Search - Index Files
history.ix, index_a.ix, index_d_1.ix, index_di_1.ix, index_i_2.ix, index_k_2.ix, index_kl_1.ix, index_klh_2.ix, index_n.ix, index_r_l.ix, index_sv.ix, index_v.ix, index_v_ix.log, indexlog.dat. This files are generated durin full text search. now i have doubts regarding this, 1) Can we referrence this files directly 2) Where it will be located in our system? 3) is it loaded for each Full Text Index we created for the table. 4) How this file are used in Full Text Search.
View Replies !
View Related
Full Text Index Population Tuning
hello, I'm looking for a way to populate my index on insertion but not on updates. I tried each possible value for CHANGE_TRACKING MANUAL|AUTO|OFF and it automatically takes every changes that have been made before in account. is there a way to "flag" the rows that I don't want the server to re-index (i.e. updated rows). Thanks for reading, any help is welcome.
View Replies !
View Related
Problems Restoring Database With Full Text Index
I am trying to restore database from bak file, it seems it has some ull index, which I do not need. When I click restore I get this error message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'RAFAL_LAPTOP'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The file "sysft_spider_Fulltext" failed to initialize correctly. Examine the error logs for more details. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------BUTTONS: OK------------------------------
View Replies !
View Related
Full Text Index Problems With Grouping Data
Hi i have the following database structure: Documents:- DocumentID- DateCreated DocumentsLog:- DocumentLogID- DocumentID- Title- Content (Text) This allows me to log all changes to my documents. The DocumentsLog is joined to the Documents by taking the maximum DocumentLogID and grouping by the DocumentID (this returns a 1 to 1 relationship). Ok so here's my problem. I need to create a full text index to help me search my Documents. I tried creating a indexed view but it doesn't support text fields so that didn't work. I even tried convert/cast on the text field to a varchar(max) but it still didn't like it. My next approach was to add the full text index on the DocumentsLog table. The problem i have with this approach is that the data returned from the following query doesn't filter out the older logs: SELECT DocumentLogID, KEY_TBL.RANK AS Rank FROM DocumentsLog AS Address INNER JOINCONTAINSTABLE(DocumentsLog, Title, 'ISABOUT ("Cont*", About WEIGHT(0.9), Account WEIGHT(0.1) ) ' ) AS KEY_TBLON DocumentLogID = KEY_TBL.[KEY]ORDER BY Rank DESC I thought i could modify this to the following: SELECT MAX(DocumentLogID) AS DocumentLogID FROM (SELECT DocumentLogID, DocumentID, KEY_TBL.RANK AS Rank FROM DocumentsLog AS Address INNER JOINCONTAINSTABLE(DocumentsLog, Title, 'ISABOUT ("Cont*", About WEIGHT(0.9), Account WEIGHT(0.1) ) ' ) AS KEY_TBLON DocumentLogID = KEY_TBL.[KEY]) AS DocumentsLogAllGROUP BY DocumentID, RankORDER BY Rank But you can only add the order by on the outer select statement and if i apply the grouping on the rank i still get multiple rows with the same DocumentID if the rank is different. I feel i'm abit stuck, i've been thinking about this all day and trying various scenarios but nothing seems to work. I would really appreciate it if someone could help. Thanks
View Replies !
View Related
Full Text Index Not Working When Populated From Nvarchar. Bug?
Hi, I was wondering if any SQL Server gurus out there could help me... I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value. To re-create the problem quickly... If I populate the column via CONVERT(varbinary(max), 'test text') then there is no problem, I get results as expected. However if I populate the column via CONVERT(varbinary(max), CAST('test text' as nvarchar(max))) no results are ever returned. Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary. I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out). The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it). Any pointers / suggestions would be greatly appreciated. Cheers, Gavin. Below is a T-SQL script you can run to demonstrate the effect I'm experiencing... -- Create test database CREATE DATABASE FullTextTest GO USE FullTextTest GO -- Create test data table CREATE TABLE TestTable ( pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY, varbinarycol VARBINARY(MAX), documentExtension VARCHAR(5), ) GO -- The below single entry WILL BE FOUND (the text source is being entered directly) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html') -- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html') INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html') GO -- Create the full text catalog sp_fulltext_database 'enable' GO CREATE FULLTEXT CATALOG TEST AS DEFAULT GO CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033) KEY INDEX tablePK GO -- NOTE: You might need to give the catalog a chance to build before running the script below. -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
View Replies !
View Related
Exclude Html Tags From Full-text Index?
I ran a CONTAINS query for the word "target" in a bunch of index web pages. I came up with lots of matches -- but they were all inside html tags: <a href="www.foo.com" target = "_blank">lorem ipsum</a> Is there a good way to exclude tags (and their attributes) from the full-text index? Thanks!
View Replies !
View Related
How To Create Full Text Index By SQL Server Express??
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 Replies !
View Related
Can't Create A Full-text Index Or Catalogue On My Sql Table.
I am trying to run an SELECT statement with a CONTAINS statement from a aspx.net solution built using Visual Web Developer 2005 express edition. When I try to run the thing it throws an error saying "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'JournalArticle' because it is not full-text indexed.". I don't have access to the SQL Express server, I interface through Server Management Studio Express. There is an option to create a full-text index from the menu, but it is greyed out, presumably because there is no full-text catalogue. This is my real question, How do I create a full-text catalogue using Server Management Studio Express? The help function only provides examples of sql code, which I assume must be performed using sql server (which I don't have access to). Any help would be greatly appreciated. cheers, Bernie
View Replies !
View Related
Full Text Index For AdventureWorks Document Table
I am working the Books Online documentation for the full-text search feature of SQL Server 2005 Express Advanced and having a problem following the instructions. I made sure to choose the "Full Text Search" option during installation of VB 2005 Express Advanced. I downloaded, installed, and attached the AdventureWorks database successfully. I checked to ensure that the database was enabled for full-text search, but could not follow the instructions for indexing a table within the database. Here are the instructions from Books Online: To enable a table for full-text indexing Expand the server group, expand Databases, expand User Databases, and expand the database that contains the table you want to enable for full-text indexing. Right-click the table that you want to enable for full-text indexing. Select Full-Text index, and then click Enable Full-Text indexing. Another document notes: To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column. When I right-click the Document table (Production.Document) in the AdventureWorks database, there is no option to "Select Full-Text Index" or "Enable Full Text indexing". Am I missing something here? How do I get the the table indexed for full text search?
View Replies !
View Related
Full Text Index BLOB Data Problem
Hi all, I have an issue with full text indexing of some BLOB data (image data type). We recently moved a SQL 2000 database to a new server with SQL 2005 installed. Everything thing went very smooth. We have full text indexing enabled on several columns over several tables. We have a table for attachments that are stored as BLOBs that is also indexed. Since the migration, all of the full text searches work as before with the exception of the binary data. We used to be able to search inside Word, Excel, and other office documents. Now we cannot. I believe I have located the source of the problem, but I am not sure how to resolve. Anyone who has used this feature knows that when you index a BLOB (image data type) you have to select a second column that tells SQL what filetype is being stored. My column for this is called ATTACHMENT_CONTENTTYPE. SQL compares this with a list of iFilters it has to determine which filter it should use when searching the binary data for text. Here is an example of what is contained in my ATTACHMENT_CONTENTTYPE column: image/tiff application/x-zip-compressed text/richtext image/bmp audio/wav application/msword video/mpeg video/avi image/x-bmp image/vnd.ms-modi image/gif video/mpg application/octet-stream application/vnd.ms-excel All of the sql documentation I am reading is telling me that my column should contain file extensions like: .doc .xls .ppt Here is my dilemma. This all worked perfectly with SQL 2000. It is a big deal for me to change all of the data in this column as well as the application that populates it. Am I not understanding how this works, or is there some way I can get SQL 2005 to recognize the way my column is describing the data type? Thank you!
View Replies !
View Related
Full Text Index Not Working With Nvarchar Source
Hi, I was wondering if any SQL Server gurus out there could help me... I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value. To re-create the problem quickly... If I populate the column via CONVERT(varbinary(max), 'test text') then there is no problem, I get results as expected. However if I populate the column via CONVERT(varbinary(max), CAST('test text' as nvarchar(max))) no results are ever returned. Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary. I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out). The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it). Any pointers / suggestions would be greatly appreciated. Cheers, Gavin. Below is a T-SQL script you can run to demonstrate the effect I'm experiencing... -- Create test database CREATE DATABASE FullTextTest GO USE FullTextTest GO -- Create test data table CREATE TABLE TestTable ( pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY, varbinarycol VARBINARY(MAX), documentExtension VARCHAR(5), ) GO -- The below single entry WILL BE FOUND (the text source is being entered directly) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html') -- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html') INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html') GO -- Create the full text catalog sp_fulltext_database 'enable' GO CREATE FULLTEXT CATALOG TEST AS DEFAULT GO CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033) KEY INDEX tablePK GO -- NOTE: You might need to give the catalog a chance to build before running the script below. -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test') www.gavinharriss.com
View Replies !
View Related
Full-text Index Search Not Returning Expected Results
Hello, My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload. I'm testing this search, and it doesn't work. The table has the following columns: attachemtId (int) - primary key fileContent (image) - contents of the file fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc") I created a .doc file that simply says "ssn", and then run the following query: SELECT * FROM TempAttachment WHERE CONTAINS(fileContent,'ssn') and nothing is returned! I tried the same thing with a .pdf file, and same results. I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!
View Replies !
View Related
|
TRACKER
