MS SQL Server :: Searching Problem In Myanmar Text Database

Searching Database Text W/o Using Full-text Indexing
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 Replies ! View Related

Searching For Text Anywhere In A Database
Maybe a totally newbie question - I need to find some text in SQL database, I have no idea in which table it may be. Can I do it through SQL Management Studio 2005 or do I need some other utility? What would you suggest then?

Thanks.

View Replies ! View Related

Text Searching
Hi again!

I have a products table with product attributes in a second table,
together they describe a full product. I have a product title, a
list of providers, description text, and keywords. I would like
to do a search across these fields, and so far my research has shown
that the Full Text Search component of SQL Server is the way to
go. However, I am not sure this will be possible based on what is
installed on the hosted server, so I am wondering if there is a unique,
cool way of doing this without Full Text Search?

Thanks,

jr.

View Replies ! View Related

Text Searching In SPs
Hi:

I need to search all user written SPs which have particular text in them. One way to do it is to open each SP in some notepad or word processor and search for the particular text.Is there any efficient way to do it ??

Rnathan

View Replies ! View Related

Full-text Searching
how can enabled full-text searching for table columns for sql server 2005 enterprise edition?

View Replies ! View Related

Full-text Searching
how can enabled full-text searching in sql server 2005 on windows XP?
Thanks, mohsen

View Replies ! View Related

Full-text Searching
do support full-text searching for sql 2005 express edition? thanks,mohsen

View Replies ! View Related

Full Text Searching
how can install full text searching in SQL SERVER 2005
By mohsen

View Replies ! View Related

Full Text Searching
how can TRUE full text indexed property in column property in SQL server 2005?
by mohsen

View Replies ! View Related

Full Text Searching
I am trying to run a full text serach on one field, a Varchar 2000.
say the field contains:
(before you break the seal of your new product box, please be careful to read all the instructions) ...for example
I search for keywords that may be in this field
Like:
product box
seal
instructions
and this row is included in the result set

but I would like to leave out words like all pronouns and 'a' and 'I' ...words that aren't going to matter to the search.

Does someone know where I can stgart in doing this full text searching?

Thanks,
Eric

View Replies ! View Related

Searching Text In A Db Field
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')

View Replies ! View Related

Full Text Searching
I've assumed responsibilty for a sqlserver 2000 that has many databases and has only 1 database that uses full text searching. That 1 application now wishes for me to update the noise words file to remove the single characters.

As far as I can tell, no other application is using the full text searching since I don't see any catalogs created for any of them.

So, if no other databases and applications in this sqlserver have catalogs created for them, can I safely assume that changing the noise words file will not impact any of them even if the other databases do have text fields in some tables?

View Replies ! View Related

Searching In TEXT Field
Hi,
We are using SQL Server 6.5 and have some TEXT datatype columns in the database which we need to search, based on keywords. Does anyone have experience in using TEXT datatype or can anyone give me some pointers. Also I need to know what is the performance difference between searching thru a text field or searching thru a series of varchar(255) fields, is it easier to search thru the text field by breaking it into a series of varchar(255) character fields or is it okay to search thru the text field. This is urgent.

View Replies ! View Related

Full Text Searching
I have defined a full text index on the firstname column for the employee table. I am using SQL Server 2000 on Windows 2000.

I enter a query using SQL Analyzer such as

select * from employee where contains (*, 'Brian')

I receive the error message :

Server meg 7619.level 16, state 1 , line 2
Language/Database cache file could not be found. Does anyone have any ideas

Dave

View Replies ! View Related

Full-Text Searching
I`ve just starting playing around with version 7.0 and was wondering if anyone knows how to implement the Full-text Searching feature. Its sound like a neat feature if I could just get it to work.
Thanks in advance
Joe

View Replies ! View Related

Free-Text Searching
Hi i'm Free-Text Searching a field in the database, is there any way i can pull back all the ignored words (the,a,and,etc..) otherwise how do you check for them. If a user enters one it just errors with a OLE DB error message, not very nice!
Cheers

View Replies ! View Related

Searching Formatted Text
hi guys,

i have a problem and i need some help please, i have a column in my database which comes from a column of formatted text (which i dont have any control over

Code:

Analysis of Receipt 41129 Amount 2.35 Dated 02/01/2002

Type Date Ref1 Ref2 Original Amnt Receipt Discount Balance

Adj 02/01/2002 INV41129 O/Balance 2.35 2.35 0.00 0.00

Thing is i need to search the database and pick out all values of the Ref1 column - in the case INV41129

Appreciate any and all help guys

Thanks

Andy

View Replies ! View Related

Full Text Searching
Hi i am really new to full text searching, I have created a catalog using:
sp_fulltext_catalog 'textcatalog','create'

and now i want to add a table with:
sp_fulltext_table 'Product', 'create','textcatalog', 'ProductID'

where product is a table in my database and productID is the primary key to that table. The primary key cannot be null,

But i get an error:
'ProductID' is not a valid index to enforce a full-text search key. You must specify a unique, non-nullable, single-column index.

Can any one point me in the right direction?

Thanks in advance

Tuppers!

View Replies ! View Related

Searching Procedure Text
Everyone seems to think this needs something elaborate:

CREATE PROCEDURE sp_FindText @text varchar(8000), @findtype varchar(1)='P' AS
SET NOCOUNT ON
IF @findtype='P' SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line,
--PatIndex('%' + @text + '%', text) AS Position,
OBJECT_NAME(id) AS ProcName
FROM syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line

IF @findtype='C' EXEC('SELECT TABLE_NAME + ''.'' + COLUMN_NAME AS TableColumn FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%'' ORDER BY TableColumn')

IF @findtype='T' EXEC('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%' + @text + '%'' ORDER BY TABLE_NAME')
GO

It not only searches procedure and view definition text, it will also find tables, views, and column names:

EXEC sp_FindText 'myTable' --or-- EXEC sp_FindText 'myTable', 'P' --finds procedures/views containing 'myTable' in their definition/code
EXEC sp_FindText 'myTable', 'T' --finds tables/views containing 'myTable' in their name
EXEC sp_FindText 'myCol', 'C' --finds columns containing 'myCol' in their name

It's pretty easy to modify and extend, and probably doesn't need dynamic SQL either. It returns line numbers for views and procedures, but these can be incorrect if the procedure or view contains over 4000 characters of code. It does not search tables for values, it's strictly for finding object names.

View Replies ! View Related

Searching Large Text
I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on?

SQL 2005

View Replies ! View Related

Full-text Searching
Are there any plans to add full-text searching capability to SQLCE? I know it's available in SQL Server Express but it would still be useful for an embedded/desktop application -- SQLite recently added this.

View Replies ! View Related

CONTAINS In Full Text Searching
Hi

I've used CONTAINS on a varchar field.
in SQL SERVER 2000

the query was "Select name from description where CONTAINS(name,' "donot*" ')

say if I search for "Select name from description where CONTAINS(name,' "donot a*" ')
---it doesn't return rows.

as might be it is seeing that 'a' as the starting letter of AND key word which is used in CONTAINS
but how to tell that it is my next letter in the search

Really I need more clarity on this...

Thanxs in advance

View Replies ! View Related

Full Text Searching With Paramters
i have a query in SQL Server 2005 that uses a parameter like so:ALTER Procedure [dbo].[p_FullTextEquipmentSearch]

@search as nvarchar(50),

as

SELECT * FROM EquipmentView

where contains(*, @search);

what i want to so is add more parameters to the query so that the user can choose either 1 or many for the search can anyone help me on this

View Replies ! View Related

Full Text Not Searching All Fields
I have a major issue with an application that I wrote last year.
The business I wrote it for changed from a Win2000 Server to Win2003 SBS. They installed SQL 2000, and we moved over the application. It was previously using SQL7 on the 2000 machine.
Anyway, the application searches through the fields and finds data on their film projects. The idea being that they can lease some stock footage, or even reuse some they may have already. It used to work great.
Now, however, when they do a search looking for "all of" in the fields, it returns 0 results. The "any of" works still. The query had to change based on the differences of the server, but it works just great. The query analyzer works, and returns 300-or-so results...which is the desired effect. However, in the application, it does not. I know it's right, because I can search on some items, and I can get some limited results, but they seem to only be finding words in the "description" field.
It appears that it is only searching through the description of the footage shots, but not the medium (for example: film or video) or the actual show they were filming it for. All of those fields (columns) are in the FT catalog, and I've had another person watch to make sure I included them when I rebuilt it.
Now, here's the thing. The EXACT same coding works on an EXACT version of SQL 2000, but it's on a Win2003 Standard Server (not SBS). The Standard server is not theirs, so it can't stay there. The same data is in that DB, and it works just great. So I know it's not my coding, I know it's not my Query, and I know it's not the data.
Is there any reason that this SBS server might not search all the fields or all the fields may not populate properly in the Full Text Catalog?
This has been going on for months, and I'm completely out of ideas. Can anyone suggest anything?
Thanks for any and all thoughts.

rob

View Replies ! View Related

Problem With Full Text Searching
I'm wrtiting a local site search egine but I need to make use of functions like FREETEXTABLE
for instance. In order to use this I have to have the' Full Text Search
table' enabled for a table. Now the Microsoft Search Service is running
in MSDE; of that I'm sure. However when I try to enable the full text
searching on a table or database the option that I'm supposed to choose
is grayed out which means I can't select it. What's wrong? What do I
have to do to be able to use that option? I' short of time and would
appreciate n answer a.s.a.p. Thanks

View Replies ! View Related

Question About Full Text Searching
Hi,

I was wondering when is it worth using Full Text Searching as opposed to using LIKE? Also, since I will be using third party for hosting my site, generally, do hosts support Full Text Searching?

Thanks!

View Replies ! View Related

Full Text Searching Problem
Kind of a no code question, Is it possible to add a word to the index catalog of MS SQL Server 2000 directly? The reason for this is that a web based search engine we are working on fails to return results when given a certain hyphenated acronyms such as "4-H" this string is broken as 4,h and resolved as a noise word and thus no indexed when there are numerous pages contained in the database which have this string. I was wondering if it's possible to just insert special cases of strings like that into the catalog? or do I have to create a custom wordbreaker?

View Replies ! View Related

Full-Text Searching Issue
The Microsoft SQL full-text indexing seems
to be working fine, however why does it only
work when I insert a value and it fails to
work when I insert the @txt varaible like
the original StoredProc below ?

Replaced "name like '%' + @txt + '%'" with
1. FREETEXT(NAME, 'ginger') --> works
2. FREETEXT(NAME, 'txt') --> fails
3. FREETEXT(NAME, @txt) --> fails
4. FREETEXT(NAME, '@txt') --> fails
------------------------------------------
** Originial **
CREATE PROCEDURE p_SEARCH_PRODUCTS
@TXT VARCHAR(500)
AS

DECLARE @today DECIMAL

set @today = DBO.DATETOMS(GETDATE())
SELECT
0 CATEGORY_ID,
P.PRODUCT_ID,
0 CAT_COUNT,
P.NAME,
P.SHORT_DESC,
P.THUMBNAIL,
MIN(S.PRICE) PRICE,
P.NAME_EXT,
0 PACKAGE_FLAG
INTO #OUT

FROM
PRODUCT P,
SKU S
WHERE
P.PRODUCT_ID IN (
select PRODUCT_ID from product where
name like '%' + @txt + '%'
)
AND P.PRODUCT_ID=S.PRODUCT_ID
AND @today between P.DATE_ACTIVATE AND P.DATE_DEACTIVATE
AND P.ACTIVE = 1 AND (P.DELETE_FLAG = 0 OR P.DELETE_FLAG IS NULL)
GROUP BY P.PRODUCT_ID,P.NAME,P.SHORT_DESC,P.THUMBNAIL,P.NAM E_EXT

UPDATE #OUT SET PACKAGE_FLAG = 1 WHERE PRODUCT_ID IN (
SELECT PRODUCT_GROUP_PRODUCT_ID FROM PRODUCT_GROUP G WHERE
PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
) -- 1 IS QUALITIES USED FOR PACKAGE OPTIONS
OR PRODUCT_ID IN(
SELECT PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
AND PRODUCT_ID <> PRODUCT_GROUP_PRODUCT_ID
)

SELECT top 100 * FROM #OUT
GO

View Replies ! View Related

Searching Throug TEXT Fields
I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes.

When I was using access I did it like this:

where upper(searchtext) like ('%SEARCHSTRING%')

However UPPER doesn't work on text fields (I want the search to be case insensitive)

I tried this:
where patindex(searchtext, '%SEARCHSTRING%') <> 0

but that is not case-insensitive...
Help me SQL gurus, you are my only hope

View Replies ! View Related

Full-Text Searching Of Symbols
I have successfully set up a full-text catalog for a blob field in one of our tables. I am trying to search for words like 'C++' and 'A++' or 'C'. I know who to accomplish the single letter part, by removing it from the NOISE.ENU file, but how do I get it to recognize the ++ part? I need to be able to search on -, _, = and +. If anyone knows anything, it is much appreciated.

Jake Massey

View Replies ! View Related

Indexing,full Text Searching
hello friends..

do indexing and full text searching needs cideamon.exe ?

cideamon exe eats much cpu.I want to disable it but I am afraid that disabling cideamon.exe affects mssql indexes to work.

thanks

View Replies ! View Related

PROBLEMS WITH FULL TEXT SEARCHING
I am very new to SQL and Access projects.

I have created the full text catalogs for my tables, so that I can search all the tables at the same time, from any of the fields in the catalogs.

I have created the following procedure:-

create procedure usp_full_text(@findtext varchar(255))
as
select set_id as URN, input_date as date_of_record from set_records where freetext (*, @findtext)
There are a few union selects under this, but

If I search for perhapse 'SMITH' from within access, then this will bring back all the records which have smith in them somewhere, which is good, however if I repeat the search for 'smit' then I get no records returned.

How can I change the code to find the parts of words, or string of charactors, and how can I set this to do a soundex search?

Hopefully all this is possible, i just need pointers on how to achieve this, or I could be sat there for days.

Many thanks for any help you can offer.

View Replies ! View Related

Full Text Searching For Express Edition
do support full-text searching for sql server 2005 express edition?
thanks,mohsen

View Replies ! View Related

Is Column Weighting In Full Text Searching Possible?
Hello, I'm using containstable to do full text searching. I know that you can provide weights on the actual search term entered by the user. However, is it possible to add weights/priorities on certain columns that are being searched? My containstable statement is currently the following: ... containstable(Articles, (Title, Abstract, Keywords, Body), @SearchTerm) ...However, I want the rows that match on the "Title" column to be ranked higher than the other rows.

View Replies ! View Related

Full Text Searching....THOUSANDS Of Records!
Hope I am in the correct section.

I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)

Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!

So, basically, I am streaming 12 megs across the pipe and I want to reduce that.

I would like the system to detect over maybe 500 records and cancel the search.

I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?

Thanks!

cbmeeks

View Replies ! View Related

Full-Text Searching In SQL Server 2000
I am trying to create a full-text searching in SQL Server 2000, after trying different ways still failed.

I use a Windows login to start both MSSQLServer and SQLAgent services, created a full-text catalog in pubs database employee table in EM. Start full population but the status is always idle and never get populated.

My Windows login is a member of sysadmin role.

I extracted the gatherer log:

- The gatherer has started
- The initialization has completed
- Started Full crawl
- MSSAL75://SQLServer/75d7831f Add Error fetching URL, (800700e9 - No
process is on the other end of the pipe)
- Completed Full crawl

As I have been struggling in doing this for several days, if anyone has successfully doing a full-text search, please give me your experience and steps on how you did it successfully.

Thanks

View Replies ! View Related

Full Text Searching Eventually Utilizes 100% CPU
For one day, this SPROC executes very quickly to return results on a Full Text catalog.

ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch
(
@ORKeywords varchar(4000) = 'xxxx',
@ANDKeywords varchar(4000) = 'xxxx',
@NOTKeywords varchar(4000) = 'xxxx',
@SourceID int = -1,
@IsHidden bit = null
)
As
set nocount on

SELECT HHL.HeadlineID,
HHL.Title,
HHL.Link,
HHL.[Description],
HHL.PubDate,
HHL.GMTDateAdded,
RSSSources.SourceTitle,
RSSSources.SourceLink
FROM RSSHarvestedHeadlines HHL
INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
WHERE HHL.PublishedFlag = 0
AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
AND (
@NOTKeywords = 'xxxx'
OR (
(NOT CONTAINS(HHL.Title, @NOTKeywords)
AND
NOT CONTAINS(HHL.Description, @NOTKeywords))
)
)

ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC

But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.

When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.

But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.

By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.

Note, over night I am adding about 1000 records to the table.

Would automatic updates to the FT Catalog choke on 1000 records?

Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?

Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?

I am at a loss.

The following code will recreate the table:

CREATE TABLE [dbo].[RSSHarvestedHeadlines](
[HeadlineID] [int] IDENTITY(1,1) NOT NULL,
[SourceID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PubDate] [datetime] NULL,
[GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()),
[GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()),
[HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)),
[PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)),
[EditStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED
(
[HeadlineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

View Replies ! View Related

Efficiency Of Sql Server On Searching On Text Field
Hi

We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?

The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?

Please advise, thanks for your help!

Li

View Replies ! View Related

Full Text Not Searching Properly Whe Using Multiple Columns
Hi everyone,

I have full text enabled on one a products table with multiple columns set for full-text searching. I am having a problem with the results and it seems that there is bug with SQL. However, I read somewhere on Microsoft's site a while back that this bug was fixed in SQL Server 2000 (not in Sql Server 7). I am running SQL Server 2000 with all the latest service packs on a Windows 2003 Server.

Here is the problem:

When a search is performed, SQL is supposed to search all the columns enabled for the keywords specified. It does that to a certain extent.

SQL is only searching on the first column that it finds a match for. For example:

If my table has columns ProductName, PartNumber, and Keywords enabled for full text searching, and it has the following data:

Row 1:
ProductName = 'XYZ Cable for the Sony M9999 TV'
PartNumber = 'xyz'
Keywords = 'tv cable, something else'

Row 2:
ProductName = 'Sony 100" Color Television'
PartNumber = 'M9999'
Keywords = 'sony tv, m9999'

Row 3:
ProductName = 'White t-shirt with drawing'
PartNumber = '1212'
Keywords = 'tv cable, something else, m9999'

Now, with the data above, SQL is suppose to find all 3 records when doing a search for the keyword 'm9999' because all rows contain the keyword - in different columns but since all columns are enabled for full text searching, they should all be searched on every record.

Instead, what it does is once it finds a match on one column, it only searches that column on the rest of the records. So if I search for 'm9999', it will find a match in ProductName for the first row and it will only search the ProductName on the remainder of the records which will result in only 1 record being returned because they contain the keywords in other columns. Obviously, these are not the results that I want especially because it may leave out the most important rows - in this case the most important may be row 2.

Does anyone know if there is a fix for this issue or has anyone experienced this before? If you need me to explain more, let me know.

Please help! Thanks!
- EM

View Replies ! View Related

Full-text Searching And Access Denied Errors.
I continually get access denied errors when trying a simple full-text search through ASP. Does anyone know anything about how SQL7 and the MS Search service interact? And associated file permissions?

Any help, links or potent liquids appreciated.

Thanks

sorCrer

View Replies ! View Related

Full Text Searching Using Values From Multiple Tables
Hi All-

I have a bit of an issue and hope that someone can shed some insight into the matter. Basically, I had created a very simple search application that searched a database using full text. What I did originally with mySQL was to take the fields from the tables with which I wanted to search (had criteria that I wanted to search upon) and then join those fields, creating a new table called search_table. This table had a full text index on it and made searching very fast and accurate. It would allow me to search for a string and spit back the records related to that string whose accuracy was calculated via the joined table's full text index.

Now, I need to search the same fields with a MS SQL database. Unfortunately, this time around I don't have the luxury of being able to create new table just for searching. From what I understand I can not create a full text index on a "view", but I was reading an article that indicated that it might be possible to create a full text index that encompasses different fields from different tables and calculates relevancy based upon that. Does anyone have any ideas as to how I might go about modifying this search to make it work? Thanks in advance.

infinity003

View Replies ! View Related

Full Text Searching With SQL 2005 Express Advanced
I'm using the Full text functionality of SQL 2005 Express Advanced and it works well.

However, the first time a query is run it take 20 seconds or so to actually return the search results. Thereafter, the search results are almost immediate.

That would be fine, except that if no further search is performed for approx 15 minutes or so, then we are back to the first scenario where it takes 20 seconds to return the results.

I assume this means that the full text catalog is not being held in cache at the server? Is there anyway to force it to do this?

My first thoughts were that Full Text within 2005 was an excellent tool, but if the first search always takes a long time then I will have to re-think as I can be sure that customers will get extremely annoyed at the response.

Do you know if there is any way around this or if there are any fixes imminent for this problem.....indeed, is it a problem or am I missing a fundamental setup issue?

What would be the implications if I created a small console application to perform a full text search once every minute at the server to keep the index cached?

many thanks

View Replies ! View Related

Full Text Searching In Advanced Services Edition
I have installed the advanced services edition but cannot find out how to build and populate full text catalogs. Nothing shows on the MSSMSE tool and when I try to use a sql command to create a new catalog it says that the full text service is not installed. Am I missing something?

View Replies ! View Related

Take 1 Dynamic Sql Query, Add Some Free Text Searching, And A Dash Of Custom Paging
Hey guys,

I've got a question that can only be explained with an example, BTW im using MSSQL2005:

I have a web app that allows users to search for other users on a wide range of criteria. Some are set values from dropdowns and others are text areas. My SPROC creates a dynamic sql query with optional where clauses and uses FREETEXT(...) for any criteria that comes from a text area.

My problem is 3 fold:
1) I'd like to order my data by the RANK provided by the FREETEXTTABLE(..) but I have 5 parameters that are 'text' so that means i'll get 5 different FREETEXTTABLES with 5 different RANKs and im not sure the best way of combining the RANKS...maybe an average but id like some ideas??

2) Assuming I implement the above and now i'm getting a result set ordered by RANK id like to implement some custom paging. I see two ways to do this:

a) I use ROW_NUMBER() OVER (ORDER BY [MyRankingCol]) and then SELECT * (excluding the ROW_NUMBER() col) that fall between my range.
The problem i forsee with this is how do i get the total number of records returned so I can display it on the webpage? I'd like to use @@ROWCOUNT but this wont return what i need. That means im going to have to run the exact same query again but instead use COUNT(*). So for every page change im performing 2 searches...not fond of that.

As an optimization on the above I could only run the COUNT(*) query once and keep track of that in the web app but i run the risk of having inaccurate results...so im not going that way.

Does anyone know another a way where I can get the rowcount before I do the paging?

b) the second way I can attack this is by creating a temp table (i've looked at the alternatives like using ROWCOUNT but they all force you to order by an ID field..this wont work in my case since im ordering by rank and they wont be unique). With the temp table i can have a synthetic ID that I can then use my between clause on.
The problem with this is that im create a temp table for every page change.

So thoes are the issues....can anyone offer some suggetions? I hope my problem description was clear if not let me know what is fuzzy and I can clarify some more.

View Replies ! View Related

Searching For A Word In A Text File And Retuning It Using Script Taks In Ssis
does any one how to search for a word in a text file and return it back using the script task in ssis?

the file may contain data like this

POSITION   SMSMSS20051230000

S   ,,751600   ,,20051110,,20051230,20051230

S   ,,751600   ,,20051110,,20051230,20051230

S   ,,751600   ,,20051110,,20051230,20051230

S   ,,751600   ,,20051110,,20051230,20051230

S   ,,751600   ,,20051110,,20051230,20051230

what i am looking for is to be able to parse and get the date which is present in the first line "POSITION   SMSMSS20051230000" as "20051230"

and then return that as a variable ..

Thanks for any help in advance

smathew

View Replies ! View Related

Searching Database
i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description,           Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName,          3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+          3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+          3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+          3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+          3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)           AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,   Product.ImageALT, Artist.ArtistName,          (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) *          CASE              WHEN @Word2 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)          END *          CASE              WHEN @Word3 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)          END *          CASE              WHEN @Word4 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)          END *          CASE              WHEN @Word5 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)          END          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1                     AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0 BEGIN     SELECT @NextSpacePos = CHARINDEX(' ', @Phrase)     IF @NextSpacePos = 0       BEGIN         SELECT @ExtractedWord = @Phrase         SELECT @Phrase=''       END     ELSE       BEGIN         SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1)         SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos)       END
     IF @SoundexWord = SOUNDEX(@ExtractedWord)       SELECT @Matches = @Matches + 1 END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?

View Replies ! View Related

Searching A Database...
Hi,

This is the first database I have ever created, so please bear with me.

I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).

I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)

SELECT
word
FROM dbo.words
WHERE word= 'hello'

This finds the word hello.

In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)

This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.

What I am trying to do is then take all of these possibilities and compare them against the dictionary.

I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:

WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'

I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.

I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.

Thanks in advance

View Replies ! View Related

Searching A Database
Im trying to do a database query based on user input from a text field. I pulled apart the string that was
entered into the search form and stored it in textArray. The problem I
am having is when I include commmon words such as the, near, view and so
on into the search field I end up with zero results. when I only search
for less common words such as rocky and argentina for example the search
works fine. at first I thought that maybe it was generating too many
results and was screwing up but when i search for rocky it works and
when i search for near rocky it doesnt.

I realize the code might not be the most efficient but here it is...

@searches_pages, @searches = paginate(:searches, :per_page => 10,
:conditions => getSearch(textArray,params[:country]))

def getSearch(textArray,selectedCountry )
result = []
string = ""
if selectedCountry != "Optional Field"
string << "country = (?) and "
end
textArray.each do |x|
if textArray[textArray.length - 1] == x
string << "match(country,caption, keywords, notes) against
(?) "
else
string << "match(country, caption, keywords, notes) against
(?) and "
end
end
result << string
if selectedCountry != "Optional Field"
result << selectedCountry
end
textArray.each do |x|
result << x
end
result
end

Im not sure if i supplied enough information but I am trying to finish
this project soon so any type of responses would help. Also, if there
is an easier way to do an sql search based off of what is entered into
the search field please let me know. The reason I did this is because I
wasn't sure how many words the user would be entering into the field.
And without knowing this I could not hard code the conditions => so I
wrote a helper method.

View Replies ! View Related