Hardware Setup For A Database Server With Full-text Index
Oct 15, 2007
Hello Everyone,
I am developing a asp.net application using sql server 2005 with full-text searching.
Question
I am looking for a recommnded hardware setup for the databases server. I know there is no one answer but I am wondeirng if there is a microsoft guide that I could use.
If anyone has any experience, i would welcome your comments as well.
I am estiamting that the full text index will be about 2-4 gb in size, most of the activity will be full-text searches.
Also, any good benchmarks out there for full-text searching or sql server tps ?
Thank you for your time!!
View 1 Replies
ADVERTISEMENT
Sep 1, 2006
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 1 Replies
View Related
Nov 10, 2007
Is there any way to move a db with full-text indexes from one physical machine to another without having to drop catalog/indexes and the re-create them?
Cheers!
/Eskil
View 2 Replies
View Related
Feb 24, 2000
I had my database in 6.5 which i upgraded to 7.0 using SQL Server upgrade wizard.Then I created full text catalog. When I say incremental population It gives me warning that You can create full text indexes but can not execute queries against it as the database is still in SQL server 6.5 mode.What is the reason behind this?
View 1 Replies
View Related
Jul 30, 2007
I am using-- exec sp_fulltext_database 'enable'
to enable fulltext indexing on a database but query does not return any results, it just keeps on executing. I waited for about an hour and then cancelled. I dont know what is wrong. Can anybody please help me....
i am using SQL server 2000 on Windows Server 2003
SELECT SERVERPROPERTY('productversion')retured 8.00.194
SELECT SERVERPROPERTY('productlevel')returned RTM
SELECT SERVERPROPERTY('IsFullTextInstalled')returned 1
SELECT DatabaseProperty(db_name(), 'IsFulltextEnabled')returned 0
Previously it was enabled and everyting was working fine.
View 5 Replies
View Related
Nov 18, 2007
Hi,
I have visual studio 2005 with the sql server express edition that is installed by default when installing vs2005.
I am used to work from within vs when I want to create and work with databases for my web sites (I dont have visual manager installed).
I would like to enable full text search on a table's column.
Is there an easy way to do that from within vs or is it necessary to install another copy of sql server express with advanced services as well as sql server managment studio express?
How can I know if full text search is enabled?
Thanx in advance!
View 1 Replies
View Related
Apr 14, 2007
Hi all..
I tried too much to create FULL TEXT INDEX by using SQL Server 2005 Management Studio Express, it returns this ERR MSG:
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search
This problem dos not come when I use Microsoft SQL Server 2005 Management Studio, to create FULL TEXT INDEX!
My DB collation is: "Arabic_CI_AS"
But I don€™t need this, I can use English Language.
Please, what can I do?
View 4 Replies
View Related
Sep 30, 2015
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
View 0 Replies
View Related
Aug 6, 2015
I need to restore a SQL 2008 db on SQL 2014 instance with out upgrading the database(changing compatibility level).
This database has full text enabled. I see one full text catalog an 2 full text indexes.
Do I need to worry about anything or can I perform a clean restore from the backup with full text import?
Do I need to rebuild the catalog in this case?
View 1 Replies
View Related
Jun 18, 2008
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 2 Replies
View Related
Jan 1, 2007
Hi,
I have a database developed using SQL Server Management Studio Express with Full-Text searching. I can go into the Object Explorer and right click on my database and choose Properties, select File and the 'Use-full-ext indexing' is checked.
I'm not sure how I create an index for a table, can someone help? I want to create an index for table 'tblLogNotes' and have the following columns:
note_id = (PK, int, not null)
note = (varchar(3000), not null)
log_id = (FK, int, not null)
operatorid = (int, not null)
note_created = (datetime, not null)
I know I did something but not sure what I did, but on one of my tables, when I right click on table and choose 'log_note', I can see that Full-text Specification = yes, (is full-text indexed = yes), full-text type column = <none> and language = English (United States).
Table 'tblLog' has the following columns:
log_id - (PK, int - not null)
log_note (varchar(3000), not null)
tag_number(varchar(50), not null)
However, when I try the following query, I get nothing:
SELECT *
FROM tblLog
WHERE CONTAINS(Log_note, '"pump*"')
I've also tried: 'pump'
What am I doing wrong?
Thanks,
View 1 Replies
View Related
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
Dec 4, 2007
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 4 Replies
View Related
Apr 10, 2006
What is a full-text index? Please be gentle. Sorry for not looking itup in the help or on the Web. Be kind.
View 1 Replies
View Related
Oct 7, 2007
Could Full Index option only be configured during installation? When Itry sp_fulltext_table on a table, I get the message that full text isnot enabled for the system.--sharif
View 1 Replies
View Related
Jul 26, 2007
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 1 Replies
View Related
Jan 16, 2006
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 1 Replies
View Related
Aug 25, 2000
I was wondering if anyone has successfully removed the Full Text Index service?
View 2 Replies
View Related
Dec 13, 2005
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 5 Replies
View Related
Mar 18, 2008
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 6 Replies
View Related
Oct 29, 2007
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 5 Replies
View Related
Feb 11, 2008
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 10 Replies
View Related
Oct 19, 2007
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 1 Replies
View Related
Mar 30, 2006
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 6 Replies
View Related
Dec 13, 2007
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 3 Replies
View Related
Mar 18, 2007
heloo..
if I want to use full text search in my program, can I include Full Text Search service in my program setup silently in the same way as including SQL Express and .Net Framework to the setup project, Or I have to Install MS Sql Server 2005 on my customer computer?
View 1 Replies
View Related
Apr 11, 2000
Are there any examples of maintenance(ReBuild FULL or Incremental) for Full-Text indexes? Are there any index integrity checks that can be done? What is the best way to backup a full-text index?
View 2 Replies
View Related
Oct 13, 2005
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 1 Replies
View Related
Apr 11, 2007
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 1 Replies
View Related
Mar 8, 2008
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 1 Replies
View Related
Aug 6, 2015
I have recently upgraded our Database server from 2005 Standard to 2008 R2 Standard.I am having a problem while replicating Full Text Index in New Infrastructure.
Full text Index was working fine in old infrastructure.
Replication scenario for Old infrastructure
Publisher: SQL Server 2005 Standard
Distributer: SQL Server 2005 Standard
Subscriber: SQL Server 2005 Express with Advance Services
Replication scenario for New Infrastructure
Publisher: SQL Server 2008R2 Standard
Distributor: SQL Server 2008R2 Standard
Subscriber: SQL Server 2005 Express with Advance Services/ SQL Server 2008R2 Standard
Whenever I try to replicate Full text Index by selecting  "Copy Full Text Indexes"= "True" article property in Replication and create snapshot it will automatically set to "Copy Full Text Indexes"= "False" whenever I reopened publication properties or snapshot is created.Is SQL Server 2008 R2 Supports full text Index replication to SQL Server 2005.Do I missed some settings while setting up publication for Full Text Index.
View 3 Replies
View Related
Dec 17, 2007
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 3 Replies
View Related
Jul 6, 2006
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 8 Replies
View Related
