Full-text Of XML Data Column

Mar 23, 2004

Hi gurus

A developer created a stored procedure that search in a huge table in a column with ‘like’ statement.
I know that the best solution in most case is use of full-text.

But the content of this specific column is a XML data and Full-text don´t find words as desired.

For example:
Table content:
ID = 1
DsColumn = ´<Name>BETH</Name>´

select * from tbResp
where DsColumn like '%BETH %'
Results:
IDDsColumn
--- ---------------------------------
1<Name>BETH</Name>
1 row(s) affected


select * from tbResp
where
contains(DsColumn, ' BETH ')

0 row(s) affected

Can anyone give me a better solution?

tks

View 1 Replies


ADVERTISEMENT

Computed Column &#043; Full-Text = Fail

May 30, 2008

I'm trying to figure to figure out the best way to query our database for

One of our developers is implementing a "global search" feature on our intranet. The idea is that a single search string will query multiple columns in a table. For example, searching for "mike fedex" would return the following records:

company | firstname | lastname | notes
FedEx | Mike | Smith | this guy is cool
Paint World | Mike | Anderson | Used to work for FedEx

but not:
Bodum | Mike | Easton | I like apples

because it does not contain all of the search terms.

I tried to create a computed column on our "contacts" table that simply concatenated all of the fields I wanted to search. The idea was to then use CONTAINS or CONTAINSTABLE on the computed column like this: SELECT... WHERE CONTAINS(computed_column, 'word1 AND word1 AND word3'). That would work great, until I realized you can't add a computed column to your full-text index.

Pain.

Any suggestions?

EDIT: The magic sauce needs to be able to search multiple terms across multiple columns with no concern for the order of the words in the search term. For example, searching for "Please Help" and "Help Please" would return the same results. Using LIKE '%Please%Help' and LIKE '%Help%Please' would return very different results.

View 4 Replies View Related

Specify Column List In Full Text CONTAINS Search

Apr 18, 2006

Can anybody provide an example of how to specify a column list in a CONTAINS statement? Documentation says it must be comma separated inside parenthesis - I have tried (every combination of) this but always get the error "Incorrect syntax near '('.", for example:

SELECT .... WHERE CONTAINS((Name,Description), '"options"')

This works fine when just querying one column (without the parenthesis).

View 4 Replies View Related

Full-text Indexing Of An Image Column With A .PDF File

Mar 16, 2005

Hi,

I am having difficulty in indexing a Table when it contains a PDF file in an Image column. Basically I have a table with a unique id, some other fields, and an Image column. The Image column contains uploaded files from a web application. Full-text indexing works fine for all types of files that I have uploaded into this table which includes (.doc, .xls, .ppt) but I get the following message in my Event Viewer if one of thee files is a PDF file.

One or more documents stored in the Image columns with extension '.pdf' did not get full-text indexed because loading the filter failed with error '1x01'.

I have searched Microsoft's site and implemented their prescribed fixes but none has worked including installation of SP3 of Sql Server and/or modifying the registry key to run the Sql server indexing procedure as a single threaded process.

Has anyone experienced this problem? Please help.

Thanks
Kamal

View 1 Replies View Related

Full-Text On HTML Stored In Nvarchar(MAX) Column

May 2, 2007

What is the best way of using the Full-Text feature on HTML?
I want to only search the text and omit the html tags.

If that involves storing as a different format, can someone tell me the best way of doing that?
I'm very new to sql and especially full-text.

Thanks.

View 1 Replies View Related

Full Text Search And CONTAINSTABLE - Can I Weight By Column?

Aug 8, 2006

I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.

It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow:
===================
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight
FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight
FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight
FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight
FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

ORDER BY 'theWeight' DESC, m.ShortName

===================

What is the better way?

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

View 1 Replies View Related

SQL 2005 Bug? Cannot Create Full Text Index For VARBINARY Column That's Populated With Converted NVARCHAR Values

Jul 11, 2007

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 - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(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.

View 14 Replies View Related

SQL 2005 Bug? Cannot Create Full Text Index For Varbinary Column That's Populated From Converted Nvarchar Values

Jul 11, 2007

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.

UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...




Code Snippet

-- 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 10 Replies View Related

Importing Data From Oracle9i CLOB Column To MS SQL Server Text Column

Jul 20, 2005

Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com

View 1 Replies View Related

Not Able Input Full Data Into Char Column

Jul 3, 2002

Hi,

I have a table with column size char(100), But As soon as I enter 60 charcters I get an error 'Maximum limit violation'

Any help please

Thanks
Raja Jayaseelan

View 1 Replies View Related

Data Conversion Failed. The Data Conversion For Column Value Returned Status Value 4 And Status Text Text Was Truncated Or On

Jan 7, 2008

Hi Experts,

I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.

If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.

I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.

Can anyone suggest how to solve this issue.

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

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

Searching Database Text W/o Using Full-text Indexing

Mar 31, 2004

I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.

'Open sql connection
SqlConnection1.Open()

Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String

While datareader2.Read

intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString

'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)

'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then

cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()

End If
End While
datareader2.Close()

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

Trim Text Data Stored In Image Column

Mar 13, 2012

I need to trim (removing white spaces) the data or text in a column which is of type IMAGE.

I would like to achieve this through a PL/SQL procedure or function.

View 14 Replies View Related

Importing Excel Data With More Than 255 Char Text Column

Apr 21, 2008



I am trying to import data from an excel file. One of the columns contains textual information with linefeeds. Its length is greater than 255 characters. I am having trouble with truncation of the data. Is there a limitation I am running into? Is there a work around?

TIA
eventnext

View 2 Replies View Related

How? : Using A Varchar, Text Data Type Variable As Valid Column Name.

Jun 2, 2008

Cannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.   

View 1 Replies View Related

How To Return FTS Results From Varchar(MAX) Or Text Data Type Column?

Aug 15, 2007

I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).

For instance this works, and will return all applicable results.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](1000) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED


SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);

And this does not. It returns zero results what so ever.


CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](MAX) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED


SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);

Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?

View 1 Replies View Related

Full Text And Text Columns

Oct 21, 2007

Can you not add a text column to a full text index?? If I change it to a nvarchar it works fine but if I change it to a text column it wont index. Anyone know how to fix this?

View 1 Replies View Related

Can Text Be Included In Full Text

Mar 29, 2007

I have a text column which my users use extensively for like queries. The table has 1.3 Millon rows and has seen som eperformance issue ie it does a table scan and blocks other processes.

I was wondering if anyone how to handle this coulmn can I create indexes or better can text column support full text indexing.

Anyone who can shed any ideas as what might be the best possible solution will be great

AdAnup

View 1 Replies View Related

Update On Large Table - Change Data Type For Text Column

Dec 10, 2014

I need to update a large table, about 55 million rows, without filling the transaction log, in the shortest time as possible. The goal is to alter the table and change the data type for Text column from VARCHAR(7900) to NVARCHAR(MAX).

Since I cannot do it with an ALTER TABLE statement (it would fill up the transaction log) I'm thinking to:

- rename column Text in Text_OLD
- add Text column of type NVARCHAR(MAX)
- copy values in batches from Text_OLD to Text

The table is defined like:

create table DATATEXT(
rID INTEGER NOT NULL,
sID INTEGER NOT NULL,
pID INTEGER NOT NULL,
cID INTEGER NOT NULL,
err TINYINT NOT NULL,

[Code] ....

I've thought about a stored procedure doing this but how to copy values in batch from Text_OLD to Text.

The code I would start with (doing just this part) is the following, but maybe there are more efficient ways to do it, or at least there's a better way to select @startSeq in the WHILE loop (avoiding to select a bunch of 100000 sequences and later selecting the max).

declare @startSeq timestamp
declare @lastSeq timestamp
select @lastSeq = MAX(sequence) from [DATATEXT] where [Text] is null
select @startSeq = MIN(Sequence) FROM [DATATEXT] where [Text]is null
BEGIN TRANSACTION T1
WHILE @startSeq < @lastSeq

[Code] ....

View 1 Replies View Related

TEXT Data Type Column: Replacing Chars : Why Isn't This Routine Working?

Jul 20, 2005

Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae

View 2 Replies View Related

Integration Services :: SSIS - Data Load To Excel - How To Retain Trailing Spaces In Text Column

May 8, 2015

I am loading data using SSIS 2008 from a table in SQL Server 2008 DB to excel 97 sheet pre-defined with column headers. All the columns in excel is has 'Text' format property and the columns in the SQL Server table are defined as nVarchar. One of the columns has trailing spaces in few rows in DB but after exporting to excel 97, the spaces are gone. We need to retain the whitespaces in the column values. How can we do that.

View 3 Replies View Related

Full Text

Oct 15, 2007

i am tring to create the full text catalog I am following the direction at http://technet.microsoft.com/en-us/library/ms189520.aspx but on the last command it gives me an error

'PK_problem' 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.

also I tried the wizard in the managment express but it does not have a option when I right click on the table for Full-Text Index

what am I doing wrong???

View 11 Replies View Related

Full Text

Oct 18, 2007

I created a new catalog and a new index but when I start a search the grid come back empty I didnt insert any data into the table until after I created the catalog and index I dont think it has indexed the data yet. How do I force this or make it???? I have another example where I inserted data and then created the catalog and index and did a search and the proper rows came back but then I inserted new data and searched on a keyword that should have brought it up but nothing showed?

View 1 Replies View Related

SQL Parameters With Full-Text

Jun 24, 2006

Hi,
I have a SQL statement that works great when I don't use a SQL Parameter, but when I do it just takes the @Searchfor as literal text "@SearchFor" instead of the string @SearchFor represents.  Any ideas?  Below is the two versions of the sql statements
sqlComm.Parameters.Add(new SqlParameter("@SearchFor", strSearchFor));
sqlComm.CommandText = "SELECT RANK, intID,  chTitle, chDescription "
"FROM FREETEXTTABLE( tblItems, *, 'ISABOUT("+ strSearchFor +" WEIGHT(1.0))') a " +
"JOIN tblItems b on a.[KEY] = b.intID ORDER BY RANK DESC; ";
 
sqlComm.Parameters.Add(new SqlParameter("@SearchFor", strSearchFor));
sqlComm.CommandText = "SELECT RANK, intID,  chTitle, chDescription " +
"FROM FREETEXTTABLE( tblItems, *, 'ISABOUT(@SearchFor WEIGHT(1.0))') a " +
"JOIN tblItems b on a.[KEY] = b.intID ORDER BY RANK DESC; ";

View 1 Replies View Related

Full-Text CONTAINS Null

Jul 16, 2006

I have a Full-text search that is being performed on a variable (@Description)  see part of querie below:
WHERE (CONTAINS([Description], @Description)
This search only seems to work when a text fo 3 or greater characters is used Ball, but not for "an" or "a". it also does not search on part of a word i.e. "Gard" of "Garden"
Two things:
1) How do I perform the CONTAINS search for part of a word or "a".
2) How do I perform a search that returns all values, when I leave the input feild blank it returns no records.
Many thanks in advance

View 5 Replies View Related

Full Text Index

Feb 15, 2007

hello

in Full Text Search
Are there method when add record in Field for properties "Full Text Index " , update catalogs ?

thanks

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







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