I have a handful of databases that are enabled for Full-Text search. After investigating some recent performance issues, I discovered the FullText Catalogs needed to be reorganized. This is a task I knew I wanted to automate, without having to hard-code db names or catalog names. My first thought was to use sp_executesql with dynamic tsql strings. I was quite disappointed to realize that I couldn't use fully qualified names to run either of these commands:
ALTER FULLTEXT CATALOG [DBName].[SchemaName].[CatalogName] REORGANIZE
ALTER FULLTEXT CATALOG [DBName]..[CatalogName] REORGANIZE
My next thought was to create a stored proc on each user db that would do the re-orgs. Then I could have a sql job iterate through the db's and run the sp on each db. Thinking...Hmm...That's do-able, but I don't like it. Add a new db to the server, and I have to remember to create the sp. Relying on my memory to do something isn't always a good idea. Plus, if I have to fix/edit/enhance the sp, I get the pleasure of doing it multiple times on multiple servers. Too much work.
I came up with some code that would dynamically reorganize all the catalogs, but I had to run it while connected to a specific db. How do I run the code while connected to [master], but in the context of a different db? The undocumented proc [sp_MSforeachdb] came to mind. I'd never used it, and was reluctant to do so after reading about other dba's experiences with it. So I came up with my own derivitive, just for this one purpose. The code is below.
CREATE PROCEDURE dba.ReorganizeFullTextCatalogs
AS
/*
Purpose:
Reorganizes the FullText Catalogs (as needed) on all user databases.
Inputs: None
History:
02/25/2014DMasonCreated
*/
--This is the tsql statement that get executed on each db.
DECLARE @InnerSql NVARCHAR(MAX) =
'DECLARE @Tsql NVARCHAR(MAX)
We are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
looking for some views about where to place these catalogs. The manual says they work more effiecntly on a different drive to the database, which is understandable.
I'm in the processes of designing a new database application which is to be avaiable via the internet, and extranet app. But as I'm planning to host the app and the main database on a shared server but to have the catalogs on another server held in house. The app will have search facilities as its main use. They will obviously be some performace issues, but is this a good idea?
When I create a full text catalog via sql code, does the account the sql server is running under need any special permissions, since some files are created?
Or is it just the same as running any other sql code?
SQL Server 2000 is pretty well documented with the limit of 256 (see http://msdn2.microsoft.com/en-us/library/aa214780(SQL.80).aspx) but I can find no documentation anywhere that discusses the limit on SQL Server 2005.
I've searched the forum and saw that this question has been asked several times. However, none of the responses point to any documentation about this limit. We've got different groups bickering over this ("There is no limit!" "Yes there is!" "No there isn't!") and it would help to have a link on the MSDN or a response from Microsoft to this question to put this to rest once and for all. Also, if there is a limit, is that per Sql Server instance or per physical server?
Secondly, we currently have over 400 catalogs. We've noticed it takes 4-5 minutes for new data to show up in search results. Does the number of catalogs affect performance, or is it the amount of data, irrespective of catalog count?
Full Text Searches are working on my test server, but not on my production server. My test scenario is as follows:
CREATE FULLTEXT CATALOG FTC_Test AS DEFAULT AUTHORIZATION dbo
CREATE FULLTEXT INDEX ON guest.FtsTest(FullName) KEY INDEX PK_FtsTest ON FTC_TestI wait briefly and then check to see if the index has been populated: SELECT * FROM sys.fulltext_indexescrawl_end_date is not null,
So I'm assuming I don't have to wait anymore before I try some FTS searches. Right? I can't get any queries to return anything, though.
The following tells me the full text item count for the table is zero:
DECLARE @TableId INT SELECT @TableId = id FROM sys.sysobjects WHERE [Name] = 'FtsTest' SELECT OBJECTPROPERTYEX(@TableId, 'TableFulltextItemCount') AS TableFulltextItemCount
As mentioned, the full text search works on my test server. Both of them are SQL 20012 SP1 (11.0.3000) x64 running on WinServer 2008 R2 SP1.
I'm using the Full Text Search with the neutral language in the indexed columns. I have no stoplists so everything counts. We changed to neutral language because our customers don't have the need for language specific criteria. They just want raw text search.Everything was working great but then they found a term that they can't search and I can't figure out what's wrong. It comes down to this:
SELECT * FROM sys.dm_fts_parser('"1.2.3.4"', 1033, NULL, 0); SELECT * FROM sys.dm_fts_parser('"1.2.3.4"', 0, NULL, 0);
In the neutral language, the parser will return "3.4", differently than the English language parsing.In the 1033 language, the parser will break all the numbers into words and the result will show correctly.I really don't want to change the language from neutral because it's working for every other query.
I am new to Full-Text Searches and FileTables. I am working on a resume search system. I created a FileTable and the Share which contains about 51,000 resumes in Word (DOC/DOCX), PDF and TXT formats that were copied over from our main file server. I followed the instructions on adding the Microsoft Filter Pack 2.0, and all searchable file types are listed in sys.fulltext.document_types.
I have a program that searches resumes by First and Last Name and/or E-mail Address. For a while, we thought it was working fine because we were getting hits, but then we noticed some Names and E-mail Addresses we new existed were not showing up in my CONTAINS queries -- but we verified the documents do exist and the Names exist in the File Name and inside the document along with the E-Mail address.
One example I'm working on now cannot find the First/Last Name with CONTAINS(name,"lastname") in a Word DOC file, but I can find the document using LEFT(name,5) = 'Smith'. It's a Word DOC file. For testing purposes, I opened the DOC file in Word, then used Save As to create a TXT and DOCX file within the same folder. Now, both of those are showing up in the CONTAINS but not the original DOC file.
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF display_term: END OF FILE column_id: 2 document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
I'm standardizing street addresses and i think full text search, maybe, work fine with my problem. My problem is that I have 2 millon of errors and using "LIKE" dont work correctly. This is what i have now:
UPDATE addresses SET standarAddress = REPLACE (addresses.streetAddress, errors.streetAddress, corrects.streetAddress) FROM corrects INNER JOIN errors ON corrects.id = errors.idCorrects
I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:
Select CustomerID, customername, from CustomerTable where CONTAINS ((CustomerID), '"430*")
the query result only return all customer id which has space in front 430 or start with 430 ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"
but did not return customerID which doesn't has space in front of it Ex: x430xxx
If I use LIKE '%430%', the result return are both have space in front and don't have space in front.
We're having a bizarre issue with installing our SQL 2012 cluster. On the stand-alone instances, we're able to choose Replication and Data Quality Services from the Database Engine Services without choosing Full-Tex and Semantic Extractions for Search. But when installing the cluster, it won't let us choose the other two without it. In fact, if we click on either Replication or Data Quality, it auto-checks ALL of the features.
My coworker discovered this problem and I was able to replicate it. We're using the SP1 install msi.
Why this is happening with a cluster install but not a stand-alone install?
My install appears to be hanging at the as_cluster_ip_address_cluster_config_Cpu64 part and I'm wondering if it's because it's trying to install Full Text.
So the cluster install forced us to install Full-Text on our cluster (which we don't want to use, but I guess we don't have a choice about installing it). Now I'm going through to update the services from Manual to Automatic (on the appropriate nodes) and I see SQL Full-text Filter Daemon Launcher running.
What is the harm in turning it off if we're not using Full-Text capabilities?
In SQL Server 2005, the SQL Server full text engine (i.e., Microsoft Full Text Filter Daemon Launcher windows service) was installed as a Generic Service cluster resource in a Windows clustered environment.
However, in SQL Server 2012, the full text engine is not appearing as a "resource" within "Failover Cluster Manager" after installation. Do we need to manually configure the full text engine as a Failover Cluster Manager "resource" for SQL 2012? Or is this not necessary? If it needs to be configured, how do you go about doing it (i.e., what resource dependencies would you setup, etc.)
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
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.
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.
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
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.
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
'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()
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?
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
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
I have been reworking my index maintenance jobs from my old SQL 2000 table and view references to the DMV's and System Tables in SQL 2005, and I noted that some of my indexes end up being more fragmented after a reorganization and or rebuild. That doesn't make much sense to me at all. The code I am executing is:
Code Block print ' ' print '************* Beginning Index Updates for '+db_name()+' *************' print ' '
DECLARE @tablename varchar(250),
@indexname varchar(250), @fragpcnt decimal(18,1), @indexid int, @dbID int
-- Determine DB ID SELECT @dbID = DB_ID()
DECLARE tnames_cursor CURSOR FOR
SELECT b.name, c.name, a.avg_fragmentation_in_percent, a.index_id FROM sys.dm_db_index_physical_stats (@dbID, NULL, NULL, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id JOIN Sys.objects c ON b.object_id = c.object_id WHERE a.index_id > 0 ORDER by a.page_count DESC
OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid WHILE (@@fetch_status = 0)
BEGIN
-- Declare and determine the tablename ID declare @tablenameID int select @tablenameID = object_id(@tablename)
IF @fragpcnt > 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REBUILD') PRINT '***************************************************' PRINT 'Index '+@indexname+' was rebuilt.' PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id JOIN Sys.objects c ON b.object_id = c.object_id
PRINT 'Post Rebuild fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%' PRINT '' END ELSE IF @fragpcnt BETWEEN 5 AND 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REORGANIZE') PRINT '***************************************************' PRINT 'Index '+@indexname+' was Reorganized.' PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id JOIN Sys.objects c ON b.object_id = c.object_id
PRINT '***************************************************' PRINT 'Index '+@indexname+' was left alone.' PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%' PRINT '' END FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid END print ' ' print '************* NO MORE TABLES TO INDEX *************' PRINT 'All indexes for the '+db_name()+' database have been updated.' print ' ' DEALLOCATE tnames_cursor
Below are some snipits of the output:
*************************************************** Index _dta_index_wuci_history_8_1123587141__K2_K5 was rebuilt. Original framentation Percent: 58.3% Post Rebuild fragmentation Percent: 58.3%
*************************************************** Index PK__batchjob__776C5C84 was left alone. Original framentation Percent: 0.0%
*************************************************** Index PK__ContactWebDetail__116A8EFB was rebuilt. Original framentation Percent: 44.4% Post Rebuild fragmentation Percent: 77.8%
*************************************************** Index PK__managed_object_s__5DCAEF64 was left alone. Original framentation Percent: 0.0%
*************************************************** Index kb_IX_kb_scope_scope_role was rebuilt. Original framentation Percent: 75.0% Post Rebuild fragmentation Percent: 87.5%
*************************************************** Index PK__query__09A971A2 was left alone. Original framentation Percent: 0.0%
*************************************************** Index PK__email_message__38996AB5 was rebuilt. Original framentation Percent: 85.7% Post Rebuild fragmentation Percent: 0.0%
If the index begins with PK, then it is the primary key index which is generally the clustered index on the table, but not always. If it has an IX on it, it is generally a non-clustered index on the table, but again not always. In the case of the above, the PK is a clustered index, and the IX is a non-clustered index.
Anyone have any ideas why this is functioning in this manner?
I'm currently running a reorganize on a large table of ~60 gig. I started the reorganize when I noticed the fragmentation was 97.95%!!!
Well the reorganize has been running for 25 hours now and apparently bulk inserts cannot happen during this time since my SSIS package just bombed trying to prepare for bulk insert.
Anyway, my question is can I cancel this reorg? I didn't start the reorg through the query analyzer. I saw this cute little reorganize button when I right clicked my indexes, properties, fragmentation in the SQL 2005 management studio. I clicked it and then clicked ok.
I know I should have done an alter rebuild but I wasn't comfortable with the process and did the one click solution that is now killing me.
What happens if I go into task manager and shut down the process? Am I risking a serious side effect of corruption or will SQL just stop so I can rebuild the index properly?
