We had a sql 2000 db that had full text implemented on it. We upgrade that server to a 2005 server and now the full text searches that once worked don't. Most do work just the ones with ; or special characters in the query string don't work.
Anyone have the same behavior and how to get it to wrok again?
thanks
Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:
NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES
I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).
In example, the following would be returned for the data above:
NAME
----------------------
1FO
ABS
BRO
THO
VWC
Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:
NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES
I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).
In example, the following would be returned for the data above:
NAME
----------------------
1FO
ABS
BRO
THO
VWC
We just implemented a full-text index on our product master table,however the users are now screaming because they cannot search on someof the special characters that are commonly found in our productdescriptions, specifically the #, %, and period (.)These characters are not in the Noise file, so no luck in justdeleting them from there, but somehow, the full-text is automaticallyignoring those characters, and we would like for the full-text to notignore these characters.Any insight or help would be appreciated.Thanks
View 1 Replies View Related
Recently to help improve search times across one of our catalogs we implemented a full text index. So far this has worked wonders and improved our speeds 20 fold!
However recently we hit a snag with certian searches. Our catalog contains parts with descriptions such as 3'x2'. We have taken out single characters and numbers from our noise files, but they don't seem to contain special characters (ie . , " ' / ). So it seems that each one of these characters acts as a work breaker. Is there a way we can structure our search to include these characters, or is there another file to modify to allow these characters?
Currenty our call, simplified, looks like this:
select * from catalog where freetext(description,'3''x2''')
Thanks for the help!
Hello,
I have a problem....
In a table i have some descriptions and i notice that at least one is as follow:
"some product 'delay' to every"
now if i try to make a insert in another table with that description i get an error...
because of the char '
is there any way to make this insert?
the database is MSSQL 2000
Hi all,The command is as follows:BCP asdos+_reports..StageCONTACTINFO OUT C:16E8.tmp -w -b10000 -t"|" -r-Sblrkec28791dsqlserver -Uadmin -Padmin -eC:ERRCONTACTINFO.txtHere, since the first argument(database name) has a special char '+' init, the command gives a syntax error. How can i escape it? Double quotes"",[] didnt work!! Pls help!TIA
View 1 Replies View RelatedHi friends, I m facing an error while i m passing the values in query as special characters like (',"). anyone can help me?code will be more preferable.Thank you.
View 1 Replies View Related
Dears,
im using a rss script and the rs.exe tool to deploy about 100 reports to several reporting server. The problem now is, that a lot of the reports a named with special german chars like Ö Ä Ü.
I am using the folowing function:
Code Snippet
rs.CreateReport(reportName, parPath, True, definition, props)
If I deploy a report name "Verträge" (=contracts), i will get an error that the report definition could not be found. But after that the report i viewable in the report server but could not be rendered.
regs,
Oliver
I have some sql below..
SELECT
t.Doctor, t.LedgerAmount, t.TransactionDate,
ISNULL(lg.LedgerGrpDesc, 'No Sales Group') AS LedgerGroup
FROM
Transactions t
LEFT OUTER JOIN LedgerGroups lg ON t.LedgerDescription = lg.dbLedgerDesc
[Code] .....
My problem is that the data in t.LedgerDescription sometimes now has either leading/trailing white space or more likely special chars so the join against lg.dbLedgerDesc doesn't always work.
I can't change the source of the data to strip out special chars/white space so am stuck on how to deal with it.
I tried using LTRIM & RTRIM in the where clause but this doesn't seem to have had any effect...
LEFT OUTER JOIN LedgerGroups lg ON LTRIM(RTRIM(t.LedgerDescription)) = lg.dbLedgerDesc
Hello everyone,
I've got a little question - wich drives me absolutely nuts by now :).
I'm trying to optimize my full-text search since a while now.
Scenario:
Full-Text Table:
id - int - primary key
srcID - varchar(30)
srchField - varchar(150) - FULL-TEXT INDEXED
src - varchar(15)
updateStamp - timestamp
Records in Table: ~ 4480000
To get my results I have to link the srcID-Field to another table.
But for performance Testing, two queries
LIKE VERSION:
SELECT srcID FROM fulltexttable WHERE srchField LIKE '%an%' GROUP BY srcID ODER BY srcID DESC
1. Running Time: 5 seconds, returns 921315 rows
2. Running Time: 5 seconds, returns 921315 rows
FT VERSION:
SELECT srcID FROM fulltexttable WHERE CONTAINS(srchField,' "an*" ') GROUP BY srcID ODER BY srcID DESC
1. Running Time: 14 seconds, returns 443676 rows
2. Running Time: 8 seconds, returns 443676 rows
If i do a search wich returns less records, full-text is a lot faster - but on big recordcounts it seems the LIKE query is faster? Is that possible???
Anyone any ideas?
Thanks in advance
How do you insert more than 255 characters into a text field? or am I using READTEXT and DATALENGTH incorrectly?
I am trying to concatenate two 255 varchar strings. Both do have 255 characters of data.
I want to insert the concatenated strings into a field define as a text data type.
if I try to ' insert into tempTbl(textFieldDataType) values (@text1+@text2) it appears only the @text1 is inserted.
I have been told the data is really there, but I can only read the 256 position + with READTEXT.
DECLARE @textptr varbinary(16)
select @textptr = (select TEXTPTR(textFieldDataType) from tempTbl where pktempTbl = 33)
READTEXT tempTbl.textFieldDataType @textPtr 255 10
This should have read 10 text positions after the 255th position. However, I get msg
Msg 7124, Level 16, State 1
The offset and length specified in the READTEXT command is greater than the actual data length of 255.
If I use the datalength function, it appears to confirm that @text2 was never inserted
declare @length int
select @length = (select datalength(textFieldDataTypet) from tempTbl where pktempTbl = 33)
select @length
@length is returned as 255
Hello,I am learning SQL Server 2005 Expres Edition. I need to create fulltext index on Books.Remarks column in my database. I tried:CREATE FULLTEXT CATALOG FTC_Books AS DEFAULTCREATE FULLTEXT INDEX ON Books (Remarks) KEY INDEX IX_Books_Remarks ONFTC_BooksUnfortunately I got error:Full-Text Search is not installed, or a full-text component cannot beloaded.I don't understand this because I marked all options during setup.Please help/RAM/
View 5 Replies View RelatedHiya,
I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.
I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:
fieldID (int, primary key, identity seed)
fieldName (varchar), e.g. High School Field
favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12
I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:
SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')
Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?
Thanks very much,
Sam
Hi all,I have a variable called Description which is set from a database field - How do i set another variable to grab the first 200 chars from this var?
hope this makes sense
Hi
I defined the following table in MSSQL 2000
sql Code:
Original
- sql Code
CREATE TABLE saved_query (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(32) NOT NULL,
query_text VARCHAR(2048) NOT NULL
)
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL)
and whenever I insert or update the query_text field, it's always truncated to 255 characters. Why? I clearly specified a larger field size. I also tried defining query_text as a "TEXT" but got the same result.
Thanks for any help
Ken
I am following the how to in the sql studio management express and for text indexing it says
================================================
To enable a database for full-text indexing
In Object Explorer, expand the server group, right-click the database for which you want to enable full-text indexing, and then select Properties.
Select the Files page, and then select Use full-text indexing
This worked ok
==================================================
next it says to
==================================================
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.
in object explorer ,do right-click on database and is selecting preoperties and is selecting "files" page "use full-text indexing" ckeck box is disable.
how can enabled this check box?
thanks , mohsen
Hello !
When I try to search into a database table I get the following error :
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Customers' because it is not full-text indexed.
Please tell me how can I make the index.
I am using SQL Server Express 2005,VWD 2008 and WIN XP.
Thank You !
Hi Everybody,
Since SQL 2005 express doesn't support full text search... is there any third party that provides FTS for the express version?
Thank YOU
Hi,
I am putting this question here but I am not limiting it to sql server 2005 express edition.
I am developing an app on a local machine (winxp with sql server 2000 personal edition) however I came to find out that full-text does not work in this setup unless I use a server type machine.
This fouls up my development somewhat and I would like to know if there is
a) a work around for my sql server 200 setup
b) does full-text serach work in sql server 2005 express edition which I have installed on my PC ?
Thanks for any input on this issue.
Tuka
Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server. Although the concept of full text search is not new in SQL Server 2005, there are considerable enhancements in performance and manageability of SQL Server 2005. http://aspalliance.com/1512_Understanding_Full_Text_Search_in_SQL_Server_2005.all
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
Hello:
I am on a development machine with sql 2000 & sql 2005 installed. The full Text Search for sql 2005 is installed and running on this local machine.
However, I need to do a full text search with sql 2000. When I do:
sp_fulltext_database 'enable'
I get:
Full-Text Search is not installed, or a full-text component cannot be loaded.
Can I install full text search for both sql server 2000 & 2005. I read somewhere that this is not possible.
Since fulltext search loads with sql 2005 (I believe), does this mean I can't do an sql server 2000 full text search on a local machine (or server) that has both of sql2000 & sql 2005 installed.
Thanks for any help on this
I have conflicting info about whether SQL 2005 Express contains full-text search and catalogueing functionality. I have read several places online where it is stated that "SQL Server 2005 Express Edition with Advanced Services" contains full-text search, but that seems to conflict with the info presented here:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
...for clarification, see my screenshot:
http://www.builderdash.com/SQL2005.png
What's the real deal, and why the conficting info?
hi,
im pretty new to sql server
I created a database with full text enabled, I created the indexes and queried the db in managment studio express and everything worked great.
when i try the same thing from within vc# it tells me that i have to enable fulltext indexing and create an index before i can use CONTAINS
what do i need to do to make my already fully functional fulltext indexes work from within vc# express 2005
thanks
I have Googled the terms 'move path change full-text index catalog' and have come up with nothing that pertains to SQL 2005...only 2000 and 7.0....
So my question remains, how the heck do you change the location of the full-text catalog in SQL 2005? Must I delete and re-create? If so is there a good article on this process? I don't want to loose data or screw anything up....I can't imagine I am the first person who wants to do his?????
Posted - 09/19/2007 : 05:19:21
I have setup full text search on a table. Everything works fine. Now I want to find documents that contain the next string: 'T-150/04'
The query will give zero results. Is there a way to find documents that contain these kind of strings? (slahses and minus)
I use the following query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T-150/04"' )
If I do this query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"judge"' )
It will work fine. I know it has to do something with the interpunction things (slahs and minus), but I don't know what query may solve my problem.
Also a select * from containstable(jurisprudentie, text, '"T_150_04"' ) doesn't give me the proper result.
The minus sign and the slash are disturbing things. If i search for "150" or for "04" I will find the record, but I would like to speficically search for the exact string "T-150/04". Because these are references to case law issued by the court.
I hope someone can help me out.
Regards,
CBGE
Is it possible to do a full restore of a database without restoring the full-text catalogs? At the end of each month, we restore a copy of a database for quick access to month-end data with a different name. e.g. we restore the database DBN as DBN1107. Since upgrading to SQL 2005, the restore is failing because it it trying to restore the full-text catalog.
View 2 Replies View RelatedI'm running SQL Server 2K (sp3a) and when I run the following query (inquery analyzer):SELECT id, LEN(ForeignWord) as Length, ForeignWordFROM WordsWHERE Language ='Thai' and LEN(ForeignWord) > 300ORDER BY Length descI receive the following results:id Length ForeignWord------- ----------- -----------34756 445 เ#...truncated at 25534839 412 เ$...truncated at 25537613 350 โ#...truncated at 25537808 315 โ#...truncated at 25538140 315 โ#...truncated at 255(The ForeignWord field is defined as varchar(1000))Note that even though the server says that the lengths are in excess of255 characters, the results are all truncated at 255 characters.I read that SQL Server 6.5 and 7.0 had some issues related to nativeaccess versus OleDb or ODBC, but this is Server 2K patched with thelatest updates.Any idea why my varchar fields are being truncated?-- RickHere is my table definition:CREATE TABLE [Words] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[Language] [varchar] (50) NOT NULL ,[Module] [int] NOT NULL ,[Lesson] [int] NOT NULL ,[EnglishWord] [varchar] (1000) NOT NULL ,[ForeignWord] [varchar] (1000) NOT NULL ,[Note] [varchar] (2000) NULL ,[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Military]DEFAULT (0),[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Supplemental] DEFAULT (0),[SoundFileName] [varchar] (1000) NULL)
View 4 Replies View RelatedI installed SQL Server 2005 express with advanced services which is supposed to include full-text search capability but I can't get it to work. When I try to create a full-text catalog it gives me an error because it does not think the full-text service is installed or loaded. I can't seem to find a reference to the full-text search feature to enable or install it. any ideas?
View 3 Replies View RelatedHello, I have a database that has Full Text Search it works great under Management Studio Express. I can use the CONTAINS expression no problem. Now when I try using the same database in Visual Studio 2005 the CONTAINS statement it doesn't return any values and I don't get any error messages. The way I call my database from my web.config file is as follows: <add name="MyDB" connectionString="Data Source=.FULLTEXTSEARCH;Integrated Security=True;AttachDBFilename='D:My DocumentsVisual Studio 2005WebSitesApp_DataMyDataBase.mdf';User Instance=false" providerName="System.Data.SqlClient"/> Why doesn't Full Text Search work when I use it from my web application and it does work under SQL Server Management Studio Express? Please help, Louis
View 2 Replies View RelatedHi, There have been a number of posts on querying multiple tables using full text in SQL Server. I haven't quite found the answer I'm looking for, and I'm aware that in 2005 some full text functionality changed. I have an Events table which joins to a Venue table and also a many-to-many link (via a table called EventsToArtists) to an Artists table.When a user searches for "Rolling Stones Wembley" (without the apostrophes), I want the query to look in the following columns:Event.EventNameEvent.DescriptionArtists.ArtistNameVenue.VenueNameI have done quite a bit of testing on this, and haven't yet got the desired results. Rolling Stones will be found in the Artists table, and Wembley in the Venue table. At the moment, if I search for Rolling Stones, it behaves as expected. But if search for the venue name as well I get no results. I'm pretty sure this is because EventName wouldn't contain all key words and so the record is excluded from the results. At the moment I am using INNER JOINs between the tables....perhaps I should be executing a full text search on each table's catalogue and then do a UNION to join the resultsets?Hope you can point me in the right direction.Thanks
View 5 Replies View Relatedthe sql server documentation states that the use of wildcards is allowed by placing an '*' at the end of the search term. I can get this to work OK in the SQL Server 2005 query window, heres an example
select ID, SUBSTRING(Title, 1, 100) AS Title, Implemented, Published from Table1 where contains(title,'"Therap*"') ORDER BY Title
this works OK and returns a list ot titles with the word Therapy in the title
Im trying to implelemnt this functionalty in a web app with C#. The string is passed to a stored procedure. How on earth do I pass in the quotes ??
Ive tried building the string as normal then adding single quotes on the end, so I get something like
retval = txt + "*"; //txt contains the partial word im searching for, then add the wildcard
then retval = "'" + retval + "'"; // add the single quotes
and pass txt as a string parameter to my stored procedure. It doesnt work. Can anyone tell me what im doing wrong ??
the same query works fine in the SQL query window.