I've got fresh Installed SQLExpress 2005 Adv. with a following:
WinXP Pro, Developer PC, Admin/Full rights, Antivirus PC-Cilin was switched off for the installation.Enable User Instance - 0 (clear check box during installation)SQL Collation: Dictionary order case sensetive for1252 Char setSELECT @@language: us_englishcreate fulltext catalog testAPFullTextcatalog,
Sp_help_FullText_catalogs: 5 testAPFullTextcatalog C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData estAPFullTextcatalog 0 0create unique index indexName_Comment on dbo.FullTextSearchTestTable(RecordID)
sp_configure:
allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user instances enabled 0 1 0 0
user options 0 32767 0 0CREATE FULLTEXT INDEX ON dbo.FullTextSearchTestTable(Comment)
KEY INDEX indexName_Comment ON testAPFullTextcatalog
WITH CHANGE_TRACKING AUTO
Result:
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1Default full-text index language is not a language supported by full-text search.
I couldn't overcome the issue. Please help.
Alexei
P.S. May be Important: During installation (on Management Studio Express installation stage) a had to click twice the "Ignore" button for the following popUp message:
"Could not write value to key:
SoftwareClasses.xdropenWithProgIdsShared - verify, if you have sufficient access to hot key or contact your support.
I'm about ready to smash my computer. I've installed SQL Server 2005 Express Edition with Advanced Services on Win XP Pro SP2. I made sure to enable the Fulltext service at install. Installed to the default instance name, and made sure the fulltext service is running from the CM. I made sure to enable full text indexing on my database from the properties window. I can create the fulltext catalog, but not the index!
----
create fulltext index on [person.contact] (Lastname)
key index [PK_Person.Contact] on myfulltextcatalog
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.
----------------------------------------------
select * from sys.fulltext_catalogs
returns the catalog
----------------------------------------------
sp_fulltext_service 'update_languages'
Informational: No full-text supported languages found.
----------------------------------------------
select @@language
us_english
----------------------------------------------
select * from sys.fulltext_languages
Returns an empty table
----------------------------------------------
SELECT * FROM sys.configurations
ORDER BY name ;
1126 default full-text language 1033 0 2147483647 1033 default full-text language 1 1
----------------------------------------------
Does anyone have any idea what's going on? I've already removed and re-installed a couple of times to different directories, used different instance names, and still no luck. Any help would be greatly appreciated.
Hello all,I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :SELECT ci.itemNameFROM Content_Items ciINNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemIdINNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :A unique column must be defined on this table/view.How do I create a unique column within a view?Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :
SELECT ci.itemName FROM Content_Items ci INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]
However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.
So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.
It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error : A unique column must be defined on this table/view.
How do I create a unique column within a view?
Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :
SELECT ci.itemName FROM Content_Items ci INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]
However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.
So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.
It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error : A unique column must be defined on this table/view.
How do I create a unique column within a view?
Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
Hello, i have a database with about 300.000 entries. The database gets about 30 new entries every day. The Database has an FulltextIndex on several columns. This FulltextIndex will be updated every night. But now i have found out, that the fulltextsearch doesn't work anymore for all entries that where added after April 2006. When i for example make following sql-statementSELECT id,date FROM MyTable WHERE (CONTAINS((columnA),' "mykeyword" '))
i only get results that have a date after April 2006 (although there are matching entries after that date). What can the reason for that be? According to Management Studio the last Update of the FulltextCatalog has been made on 1st of December 2007. Everything looks normal and I didn't find any logs that are saying that there has been any errors. Where do I have to look to be sure if the FullTextIndex does work? Specs: SQL Server 2005 Microsoft SQL Server Management Studio 9.00.1399.00Microsoft Analysis Services-Clienttools 2005.090.1399.00Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 6.0.3790.3959Microsoft .NET Framework 2.0.50727.832Operating System 5.2.3790
SQL Server 2005: Anyone know how best to rewrite this SQL string to perform a weighted search on my table? here's my code: SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')" the idea is to get a list of recipes most pertinant to the ingredients entered. table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.
at the moment it returns the correct recipes but not in the right order. If I use 'CONTAINS' it returns nothing. hope that's not too vague!
I just tried to backup a database I recently deployed to to SQL Server 2005 box, and got the following error:
The backup of full-text catalog 'myCatalog' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
I don't need the index so I tried to drop it with both:
DROP fulltext catalog 'myCatalog'
and
DROP catalog 'myCatalog'
but both give me a syntax error.
How can I get rid of this index so I can backup my database?
We are designing a scenario in which we will have a central publisher and several subscribers in a server to server environment using Merge replication. We also will have a fulltext index for one of the tables being replicated and synchronized. What are your recommendations for the index? Would auto change tracking reduce the impact? We are building for a possible 1 billion row table but do not anticipate more than 100K rows at any time of replication.
Our production DBs are SQL Server 2000(Enterprise Edition) on Windows2003. Can anybody tell me how can I check whether a table has a FullText Index or not?
I am running a DB with 250Gb of documents, the fulltext index just keeps growing and growing. The files in the MssearchCatalogDir folder is currently taking up 106Gb, it was only 74Gb this morning. The full text catalog size property says its only 53.6Gb and this is remaining steady while the files in the MssearchCatalogDir folder seem to be balloning out of control. I ran a reorg on the fulltext catalog and it did not alter the file space (actually increased it).
There is one *.ci file that is doing most of the growing, its about three times as big as the second biggest one and is expanding before my eyes.
Should I have turned population off when I did the reorg?
Manamgement studio has an Optimize catalog option on the fulltext catalog properties dialog, is this different from a fulltext catalog reorg? Should I run this.
Should I run a shrink file on the filegroup containing the fulltext catalog (the filegroup itself is very small, all the space is in the MssearchCatalogDir folder)?
I have 140Gb left on this drive, is it just going to keep on expanding until I'm out of room, I just dont know what I should do.
Abit more info.....
I was rebuilding the index from scratch (needed to move it to another filegroup).
When I first started the rebuild, msftesql.exe and msftefd.exe were both taking alot of cpu and the overall cpu usage was high. Now after 12 hours, only msftesql.exe is running and is taking up 5-10% cpu. Could it be that the rebuild is not complete yet? Although the Item Count property of the full text catalog does seem to indicate that all documents have been processed, the processes keeps running and disk space keeps going down.
I have a problem restoring a Read-Only filegroup in a database. I've restored the Read/Write filegroups first without any problem.
Then, I am able to recover Read-Only filegroups which do not contain fulltext indexes with the command : RESTORE DATABASE GSM FILEGROUP = 'FG_RO_20080214' WITH RECOVERY
However, for filegroups containing FullText indexes (Fulltext indexes are in the same filegroup as data used for this index), I always get the following error :
Server: Msg 3149, Level 16, State 6, Line 1 The file or filegroup "sysft_CAT_SM_20080127" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
If I'm looking at sys.database_files catalog view, I can see the following name type_desc state_desc ------------------------ ----------- ----------------- sysft_CAT_MS_20080127 FULLTEXT RECOVERY_PENDING
For demonstration I created a fulltext index on table employee in Northwind database.
The following query gives an error:
SELECT * FROM employees WHERE CONTAINS (FirstName, 'Barbe')
Replacing 'Barbe' by 'Barb' or other words it works fine.
The error message is (I have a french version of SQL installed, here the translation: "A clause in the query contains only ignored words" Une clause de la requête ne contient que des mots ignorés)
Language for wordbreak in fulltext index is French and the error happens only with French, with English it works.
How can i enable my fulltex change-tracking and update-index in my table? I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them. Thanks in advance
I am using SSIS to replace set of tables daily. One of the table has primary, unique, foreign keys and full-text index. Before truncating, I am dropping the foreign key constraints (to truncate the parent table), truncating the tables and recreating the foreign keys.
I have few questions:
1) Do I need to drop and recreate the unique key as well? (I am not dropping the primary key) - Unique key is identity column created just for the full-text indexing since it was mentioned that key on integer is better than key on varchar and my pk is a varchar.
2) Do I need to drop and recreate the full-text index or just rebuild/repopulate it every time the table is loaded.
This is the first time i am using full text index and I was able to learn a lot about it from the sites. I would like to understand the correct approach while loading the tables.
We have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
We observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
HTML Code: select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
To search for the same above word using FTS,I have used the query as below
HTML Code: SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
I have attached one sql script file for your ref that contains create logic and index schema properties
Hello.. When I used Microsoft SQL Server 2005 Management Studio Express to Create FULL TEXT INDEX by this code:
CREATE FULLTEXT INDEX ON txtfilestbl(txtfile) KEY INDEX PK_txtfilestbl ON ForumsArchiveLibCtlg WITH CHANGE_TRACKING AUTO
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.
I Use same this code to create FULL TEXT INDEX by using Microsoft SQL Server 2005 Management Studio, and it was working properly. What I have to do?
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
/****** Object: Stored Procedure dbo.carr_summary_Datewise Script Date: 5/15/2008 10:20:37 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[carr_summary_Datewise]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[carr_summary_Datewise] GO
set @pDate1 = dbo.AsString(@pYear,@pMonth,@pDay1,0,0,0) set @pDate2= dbo.AsString(@pYear,@pMonth,@pDay2,23,59,59) --print @pDate1 --print @pDate2
select @Operator=SystemName,@tDiff=timeDiff from Report..Carriers where DisplayName=@pOperator select @Service=serviceName from Report..Carriers where SystemName=@Operator --print @tDiff
set @pD1=dateadd(hh,@tDiff,(cast(@pDate1 as datetime))) set @pD2=dateadd(hh,@tDiff,(cast(@pDate2 as datetime))) --print @pD1 --print @pD2
/*set @sD=datepart(dd,@pD1) set @eD=datepart(dd,@pD2) set @eM=datepart(mm,@pD1)*/ --print @eM
if @pDay1=1 begin set @sD=1 end else begin set @sD=datepart(dd,@pD1) end
if @pDay2=30 begin set @eD=30 end else begin set @eD=datepart(dd,@pD2) end
if @pDay2=31 begin set @eD=31 end --else -- begin -- set @eD=datepart(dd,@pD2) -- end
if @pMonth=1 begin set @tb1='ob_Jan08' end if @pMonth=2 begin set @tb1='ob_Feb08' end if @pMonth=3 begin set @tb1='ob_Mar08' end if @pMonth=4 begin set @tb1='ob_Apr08' end if @pMonth=5 begin set @tb1='ob_May08' end if @pMonth=6 begin set @tb1='ob_Jun08' end if @pMonth=7 begin set @tb1='ob_Jul08' end if @pMonth=8 begin set @tb1='ob_Aug08' end if @pMonth=9 begin set @tb1='ob_Sep08' end if @pMonth=10 begin set @tb1='ob_Oct08' end if @pMonth=11 begin set @tb1='ob_Nov08' end if @pMonth=12 begin set @tb1='ob_Dec08' end
select Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0) CallDate,Routepfx,Operatorout, cast(sum(Talktime/60.) as decimal(10,2)) Talktime,Cost from Report.dbo.'+@tb1+' (nolock) where Operatorout=''' + @Operator + ''' and Callyy=' + ltrim(str(@pYear)) + ' and Callmm=' + ltrim(str(@pMonth)) + ' and calldd between ' + ltrim(str(@sD)) + ' and ' + ltrim(str(@eD)) + ' group by Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0), Routepfx,Operatorout,Cost )x where CallDate between ''' + convert(varchar(20),@pD1,120) + ''' and ''' + convert(varchar(20),@pD2,120) + ''' ' --print(@sql) --exec (@sql)
set @sql= 'select Callyy,Callmm,Calldd,Routepfx Prefix,case when zone is not null then zone else ''--NOT FOUND--'' end zone, case when TimeCls is not null then TimeCls else ''--NOT FOUND--'' end TimeCls, ''' + @Operator+ ''' Operatorout,Sum(Talktime) Talktime,Cost from ('+@sql+') x group by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls,Cost order by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls ' --print (@sql) exec (@sql) end
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
this procedure takes more time to run
we need make it fastly
what should i need
if i create one index how it works
where should i create index please write one index for me
Recently we are having issues with one of the indexes during our maintenance. There is one index that which usually would have taken 10-15 mins continues to run even after 3 hrs. Other indexes on the same table finish nicely. Just this one gets stuck. Our index creation scripts drops existing index and creates new one..
CREATE INDEX ... ON ... WITH DROP_EXISTING, SORT_IN_TEMPDB ...etc
So last week when I got woken up because of this, I tried to drop the index explicitly and re create the index (by removing the DROP_EXISTING in above script). Even though it took about 45 mins it still finished in time during the maintenance window.
We had the same issue this week. I was looking at sp_who2 during the index creation, I noticed very low CPU activity and high DiskIO. example: CPU : 4k cycles, DiskIO 50000.
There is 20% of free space on the index file group. Generally index creation takes higher CPU and lower DiskIO.
How do I find out what the issue is with this index? The table has 40 million rows. this is a non-clustered index. There are 2 other indexes on the table - one clustered. they both finished properly in less than 10 mins.
How to create index when there is a SQL statement like Select count(1) as [Total], sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected] from Table1
I am trying to create an index on a view that joins two tables.
I get the classic error of course: 'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'
Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.
Code is below and any help greatly appreciated.
CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS SELECTt_number FROM dbo.ticket_cancellations RIGHT OUTER JOIN dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number WHERE dbo.tickets.t_cancelled <> - 1 OR -- Add all cancellation codes that are to be excluded from the NET view below (dbo.ticket_cancellations.tc_cancellation_code <> 83943 AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)
GO -- Create a clustered index, it MUST be unique CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
I was asked to import some data from excel into a table within a sqlserver 2000 db the import was complaining about an index so then I deleted the index imported the data succesfilly but I'm now unable to re create the index, please see the error that I'm getting below. Can someone please help. Thanks
- Unable to create index 'trainingGo'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 32. Most significant primary key is '439'. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.
Hi, all, I want to re-create an index on a production table. I got an error 644 "could not find index entry...". The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1 Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36. DBCC results for 'Mfg_DFSFNSF'. There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be: DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss) But that has to put the db under single user mode.
