fulltext, distinct, order by

I have a problem. I use Constainstable to make a fulltextsearch over 4
tables.
Now I want to order the result set. The problem is that if I insert the
column all_ranks in the Select part I have some double results. That is
because some keywords are 2 times or 3 times related to an expert.
So If I use this statement I have double information:
Select distinct inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
What I want to use is a Statement of this kind:
Select distinct inQuery.employeeid, inQuery.firstname,
inQuery.familyname, inQuery.Qualifications, inQuery.Title,
inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
Without the inQuery.all_rank in the Select part because this part
insert the double information.
This is the error Message:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
HELP!!!
I can't find a way !!
Thank you very much!
Cheers
Sebastian
Whenever you see an or you should ask yourself is it possible that the
values coming from the or condition could be duplicated.
So if it is a gender thing where there are three cases, M, F, and U
(unknown) there will never be any overlap in the OR condition.
And you can do union alls and get better performance.
If there is an overlap, ie where PK>10 or Gender='M' and you could have
Males with a PK >10 which would be duplicates you would have to do some sort
of funky group by like this:
--I really need to see your data to figure this out though
Select inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, sum(ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0)) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]
group by e.EMPLID, e.FIRST_NAME ,
e.FAMILY_NAME, e.QUALIFICATIONS,
e.TITLE, e.LINK,d.NAME) as inQuery
order by inQuery.all_rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sebastian" <C_o_z_m_o@.gmx.de> wrote in message
news:1163990663.770428.118290@.m73g2000cwd.googlegr oups.com...
>I have a problem. I use Constainstable to make a fulltextsearch over 4
> tables.
> Now I want to order the result set. The problem is that if I insert the
> column all_ranks in the Select part I have some double results. That is
> because some keywords are 2 times or 3 times related to an expert.
> So If I use this statement I have double information:
> Select distinct inQuery.all_rank, inQuery.employeeid,
> inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
> inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
> What I want to use is a Statement of this kind:
>
> Select distinct inQuery.employeeid, inQuery.firstname,
> inQuery.familyname, inQuery.Qualifications, inQuery.Title,
> inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
>
> Without the inQuery.all_rank in the Select part because this part
> insert the double information.
>
> This is the error Message:
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> HELP!!!
> I can't find a way !!
> Thank you very much!
> Cheers
> Sebastian
>

Fulltext Thesaurus

My understanding of the fulltext search using the thesaurus function is that
it will not work until the TSxxx.XML file is populated (in my case the
tsENU.xml file.)
Is this true?
We rae migrating from SQL 2000 and I could have sworn this was working there
.
Maybe I am mistaken.
I do see where the MS Office products use the MSTH3AM.LEX & MSTH3BR.LEX
dictionary files to support the thesaurus lookup there. Can these files
somehow be migrated over to SQL Server 2005 for use there or somehow be read
and converted to an XML thesaurus?
Thomas MannHere's a thought......I believe MS Word was installed on the machine that
had SQL 2000 on it.
I know that when I set up a new desktop, spellcheck & thesaurus are not
available to other applications (i.e. Outlook Express) until after MS Word i
s
installed.
Could this be the reason the Thesaurus funtion worked on the previous
machine and not the machine running SQL 2005?
--
Thomas Mann|||I have opened a trouble ticket on this issue and will continue to post
updates as this progresses (so you don't have to.)
What I have found thus far is that the standard advice of removing the
commented xml code in the tsENU.xml file does not cut it. Once you do this,
the xml tries to reference the schema (tsSchema.xml) which is missing from
the SQL Server 2005 install. I located a copy of the file from an earlier
version (SQL 2000 SP3) and that solved my missing schema problem but still
the Thesaurus function would not work (even after adding the substituion
params in tsENU.xml.)
I burned one of my support incidents from my MSDN subscription and contacted
the support center. They checked several of the machines there and could not
find the missing schema file amoungst the installed files either.
Bottom line, it sounds like this might be an install problem (or an
undocumented feature.)
I will post the fix here when I find out more.
--
Thomas Mann

Fulltext Stops (Deadlocks Occurs)

How can I determine if I need to use the SQL Server
Fulltext services? This error messages occurs when the
CPU is greater than 80% for extended period of time.
Usually the error message occurs when the system has a
major deadlock and is using all the resources to resolve
the deadlock.
Please help me resolve this issue.
Thank You,
Mike
Error Messages from Cluster:
JobID = 881
KSName = 1838:General_EventLogV1-DBSpecial
MC MachineName = GHLDBB01A
Object Name = <NT_MachineFolder:GHLDBB01A>
EventMsg = 10 NT Events from System - batch 7
LongMsg = Typemm/dd/yyyy hh:mm:ssSource
CategoryEventUserMachineName
Description
Error4/17/2004 7:34:45 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:34:45 PMService Control Manager
None7031N/AGHLDBB01A
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
Error4/17/2004 7:34:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:36:39 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:36:49 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:36:50 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:23 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:38:32 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:38:38 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Mike,
First of all, can I assume that this is a SQL Server 2000 clustered
environment? Could you post the full output of SELECT @.@.version -- as this
is helpful in troubleshooting SQL FTS issues.
Secondly, could you post any "Microsoft Search" or MssCi source events
(warnings & errors) from your server JSQL_OLTP that occurred at or near the
time of the below error messages? As well as any Microsoft Search entries in
the cluster.log file.
Yes, the "Microsoft Search" (mssearch.exe) service and does use up to 90% of
CPU usage, for brief periods of time, either during shadow merge or master
merge that occurs during either Full or Incremental Populations or at
midnight. How long is the "extended period of time" that the CPU is greater
than 80%? Does the MSSearch service ever hit 100% CPU usage or does it just
peak at 80% and then go down to normal usage levels?
Also, do this server have multiple CPU's? If so, then the MSSearch service
can be "assigned" or bound to a specific or set of specific CPU's, while SQL
Server 2000 can be set to a specific set of CPU's (via sp_configure), such
that the MSSearch CPU usage will not affect your SQL Server 2000 processing.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
> How can I determine if I need to use the SQL Server
> Fulltext services? This error messages occurs when the
> CPU is greater than 80% for extended period of time.
> Usually the error message occurs when the system has a
> major deadlock and is using all the resources to resolve
> the deadlock.
> Please help me resolve this issue.
> Thank You,
> Mike
>
> Error Messages from Cluster:
> JobID = 881
> KSName = 1838:General_EventLogV1-DBSpecial
> MC MachineName = GHLDBB01A
> Object Name = <NT_MachineFolder:GHLDBB01A>
> EventMsg = 10 NT Events from System - batch 7
> LongMsg = Type mm/dd/yyyy hh:mm:ss Source
> Category Event User MachineName
> Description
> Error 4/17/2004 7:34:45 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:34:45 PM Service Control Manager
> None 7031 N/A GHLDBB01A
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
>
> Error 4/17/2004 7:34:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:36:39 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:36:49 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:36:50 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:23 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:38:32 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:38:38 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
|||I tried to answer several questions listed below.
Thank You,
The server CPU can be greater than 90% for 5 - 7 minutes.
The server has 8 processors at 2 GHZs.
__________________________________________________ _________
Event Viewer Errors
Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
DCOM got error "The service did not respond to the start
or control request in a timely fashion. " attempting to
start the service mssearch with arguments "" in order to
run the server:
{C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
__________________________________________________ _________
Select @.@.Version
Output:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
(1 row(s) affected)

>--Original Message--
>Mike,
>First of all, can I assume that this is a SQL Server 2000
clustered
>environment? Could you post the full output of SELECT
@.@.version -- as this
>is helpful in troubleshooting SQL FTS issues.
>Secondly, could you post any "Microsoft Search" or MssCi
source events
>(warnings & errors) from your server JSQL_OLTP that
occurred at or near the
>time of the below error messages? As well as any
Microsoft Search entries in
>the cluster.log file.
>Yes, the "Microsoft Search" (mssearch.exe) service and
does use up to 90% of
>CPU usage, for brief periods of time, either during
shadow merge or master
>merge that occurs during either Full or Incremental
Populations or at
>midnight. How long is the "extended period of time" that
the CPU is greater
>than 80%? Does the MSSearch service ever hit 100% CPU
usage or does it just
>peak at 80% and then go down to normal usage levels?
>Also, do this server have multiple CPU's? If so, then the
MSSearch service
>can be "assigned" or bound to a specific or set of
specific CPU's, while SQL
>Server 2000 can be set to a specific set of CPU's (via
sp_configure), such
>that the MSSearch CPU usage will not affect your SQL
Server 2000 processing.
>Regards,
>John
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
failed.[vbcol=seagreen]
It[vbcol=seagreen]
action[vbcol=seagreen]
failed.[vbcol=seagreen]
failed.
>
>.
>
|||You're welcome, Mike,
Relative to DCOM on this server, have you made any changes to the default
DCOM configuration? Specifically, confirm that the 'Default Authentication
Level' in the DCOMCNFG's Default Properties tab is set to "None" and that
the 'Default Impersonation Level' (on the same tab) is set to "Anonymous".
Both are the default DCOM settings.
Also, I should of been more specific with the event log messages... The
"Microsoft Search" and MssCi are only recorded in the Application event log
and not the System log. Please, review the Application event log as well for
related messages.
Finally, the server's CPU usage of greater than 90% for 5 - 7 minutes, is
normal and expected during either the "shadow merge" or "Master Merge"
processes that the MSSearch service does to merge new "word lists" into it's
file system and then at the end of this process or at midnight (controllable
via a registry key). This process occurs during either a Full or Incremental
Population. Do you have frequently scheduled SQLServerAgent jobs that
execute either a Full or Incremental Population? If so, you may want to
either reduce the frequency or checkout the new SQL Server 2000 feature
"change tracking" and "update index in background" that will give you near
real-time updates of the FT Catalog when the FT-enable table(s) column(s)
are updated. Review the SQL Server 2000 BOL for more info on CT and UIiB.
Once the "Microsoft Search service terminated unexpectedly" issue is
identified and resolved, and if the MSSearch CPU usage is affecting your SQL
Server process, I can show you how to set the MSSearch process to one or
more of your 8 procs.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:138a01c4265b$cb6eeb60$a401280a@.phx.gbl...[vbcol=seagreen]
> I tried to answer several questions listed below.
> Thank You,
>
> The server CPU can be greater than 90% for 5 - 7 minutes.
> The server has 8 processors at 2 GHZs.
> __________________________________________________ _________
> Event Viewer Errors
> Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
> DCOM got error "The service did not respond to the start
> or control request in a timely fashion. " attempting to
> start the service mssearch with arguments "" in order to
> run the server:
> {C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
> __________________________________________________ _________
> Select @.@.Version
> Output:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
>
> (1 row(s) affected)
>
>
>
> clustered
> @.@.version -- as this
> source events
> occurred at or near the
> Microsoft Search entries in
> does use up to 90% of
> shadow merge or master
> Populations or at
> the CPU is greater
> usage or does it just
> MSSearch service
> specific CPU's, while SQL
> sp_configure), such
> Server 2000 processing.
> message
> failed.
> It
> action
> failed.
> failed.

fulltext statistics

Hi all,
Is it possible to get statistics of any sort from the full text engine?
My understanding is SQL Server treats the the fulltext engine as a remote
server and performs distributed queries against it via oledb. AFAIK, oledb
has optional interfaces to obtain stats (cardinality, distribution) which the
fulltext engine presumably supports since it comes up with reasonable
estimates in estimated query plans involving containstable.
So using an estimated plan is one way to get cardinality estimates for a
particular query, but I'm wondering if there's a more direct approach -- e.g.
I'd really like to be able to just get a histogram of terms. Perhaps it's
possible to connect directly to the full text engine via oledb? If so, what
would the connection string look like? or...?
Thanks for any ideas.
-Geoff
This is supposed to ship in SQL 2008.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:95516FF5-7316-437D-9278-0463823A028B@.microsoft.com...
> Hi all,
> Is it possible to get statistics of any sort from the full text engine?
> My understanding is SQL Server treats the the fulltext engine as a remote
> server and performs distributed queries against it via oledb. AFAIK, oledb
> has optional interfaces to obtain stats (cardinality, distribution) which
> the
> fulltext engine presumably supports since it comes up with reasonable
> estimates in estimated query plans involving containstable.
> So using an estimated plan is one way to get cardinality estimates for a
> particular query, but I'm wondering if there's a more direct approach --
> e.g.
> I'd really like to be able to just get a histogram of terms. Perhaps it's
> possible to connect directly to the full text engine via oledb? If so,
> what
> would the connection string look like? or...?
> Thanks for any ideas.
> -Geoff
>
>
>

Fulltext Search, Contains, Varbinary, AdvantureWorks

Hello all

I am trying to full text search in the Documnt table in AdvantureWorks DB.

as in the video sample microsoft provided.

I create the unique index on DocumentID column and created the FullTextIndex:

create fulltext index on Production.Document

(

Document

Type column FileExtension

Language 0X0

)

Key index ui_ProductionDocument on MyFullTextCatalog

With change_tracking auto

Now I am trying to run the next search:

select * from Production.Document

where Contains(Document,'?????')

and I get nothing back, what do I do wrong?

I was having a pending restart that fixed the all thing.

Don't know why....

Fulltext search won't repopulate when running on batteries

Here's one that had me stumped for a good couple of hours.
While working on my laptop, I was trying to rebuild a fulltext search
catalog. I tried numerous times repopulating, deleting and rebuilding.
whenever I tried to start it populating, I got messages in the event
log saying the crawl had started, but no sign of any disk or CPU
activity.
Eventually, I just had a hunch to try and plug it into the mains power
and all of a sudden the indexing sprung into life. Seens it won't
start the indexing process when running on batteries!!
I've searched all over the documentation and the web but I can't find
any other references to this feature.
Andy
Your observation is correct. The indexing process does pause when your
computer is on batteries.
Have a look at
www.microsoft.com/exchange/ techinfo/deployment/2000/bestindexing.doc
Which is for exchange which uses the same indexing and querying engine that
SQL FTS uses. It has this reference.
Microsoft Gatherer: Reason to back off This counter shows the code
describing why the gathering service halted the population.
0 - Up and running
1 - High IO rate
4 - Back off on user activity (by default this is disabled in server
install)
5 - Battery low (currently, if running on battery, not on AC power)
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:c925c3dc.0501090219.7dda3d6a@.posting.google.c om...
> Here's one that had me stumped for a good couple of hours.
> While working on my laptop, I was trying to rebuild a fulltext search
> catalog. I tried numerous times repopulating, deleting and rebuilding.
> whenever I tried to start it populating, I got messages in the event
> log saying the crawl had started, but no sign of any disk or CPU
> activity.
> Eventually, I just had a hunch to try and plug it into the mains power
> and all of a sudden the indexing sprung into life. Seens it won't
> start the indexing process when running on batteries!!
> I've searched all over the documentation and the web but I can't find
> any other references to this feature.
> Andy
|||Andy,
You might want to checkout the links under "SQL Server 2000 Full-Text Search
Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Specifically, 323739 "INF: SQL Server 2000 Full-Text Search Deployment White
Paper"
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:c925c3dc.0501090219.7dda3d6a@.posting.google.c om...
> Here's one that had me stumped for a good couple of hours.
> While working on my laptop, I was trying to rebuild a fulltext search
> catalog. I tried numerous times repopulating, deleting and rebuilding.
> whenever I tried to start it populating, I got messages in the event
> log saying the crawl had started, but no sign of any disk or CPU
> activity.
> Eventually, I just had a hunch to try and plug it into the mains power
> and all of a sudden the indexing sprung into life. Seens it won't
> start the indexing process when running on batteries!!
> I've searched all over the documentation and the web but I can't find
> any other references to this feature.
> Andy
|||Hmm, so to find out why it's stopped gathering, I'm expected to look in
performance monitor - now that is a bit obscure :-)
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ueyAY5o9EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Andy,
> You might want to checkout the links under "SQL Server 2000 Full-Text
> Search
> Resources and Links" at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
> Specifically, 323739 "INF: SQL Server 2000 Full-Text Search Deployment
> White
> Paper"
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:c925c3dc.0501090219.7dda3d6a@.posting.google.c om...
>
|||Andy,
Yep, as before this paper was published (I was a *contributor*), there was
no other documentation on monitoring the MSSearch gathering process, so
obscure or not, it is at least public now! Look for more blog entries on
this topic at my blog!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O9bO1TJ#EHA.2568@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hmm, so to find out why it's stopped gathering, I'm expected to look in
> performance monitor - now that is a bit obscure :-)
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ueyAY5o9EHA.1264@.TK2MSFTNGP12.phx.gbl...
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
>

FullText Search SQLserver2005 using VS2005 Server Explorer

Hello,

I am using SQLServer Express and Visual Studio 2005 to create the website. I would like to implement FullText search, but have never done it before. I have looked at the msdn documentation on FullText search in SQLServer 2005 herehttp://msdn2.microsoft.com/en-us/library/ms142519.aspx.

I cannot seem to figure out how to use a FT search using Visual Studio. Can someone please help me configure my database and then explain how I can run queries based on user input to return data?

Hi KJAK,

KJAK:

I am using SQLServer Express and Visual Studio 2005 to create the website.

In the SQL Express Edition, you have to do some more work the in the other SQL editions.

KJAK:

I cannot seem to figure out how to use a FT search using Visual Studio.

Use the Sql Server Management Studio Express. Open the Template Explorer, select the Full-text folder and select theCreate Full-text Index template. Fill in the needed information and run it. Then select theCreate Full-text Catalog template and do the same.

BTW. Sql-BOL is your friend

|||

What I have now figured out is that I will not be able to implement FULL-TEXT search on this particular site. I am using a shared hosting provider and they do not provide me access via the Sql Server Management Studio Express because they do not permit remote connections. I have some documentation that may help me get around this issue to configure the database, but since the database is provided by the host, it may not work.

I have marked your reply as answered as a way to thank you for your help and will keep this post in mind when I am working with dedicated server websites.

Thanks for your reply!
KJAK

|||

KJAK:

I have some documentation that may help me get around this issue to configure the database, but since the database is provided by the host, it may not work.

Is your DataBase transmitted to the ISP sql server, or its a dynamically attached DataBase in your App_Data folder?

In the second case, AFAIK Full-text search wount works, because you need access to the Fulltext-Catalog directory to populate your index.

|||

The database is located on the hosts network. They provide a client that I connect to in which I can define tables, stored procedures, schema's, et cetera. It does not provide me access to the catalogs and is limited in what I can do all together. I have not yet spoken to the host to see if they can explain how to do it or whether I can do it or not.

|||

KJAK:

It does not provide me access to the catalogs and is limited in what I can do all together.

You may change to a ISP they will give you a dedicated SQL server. Or live with this restrictions

Fulltext Search or Like Search

Hi,
I need to search a Items catalog that has a field that is key word
seperated by commas. Would a fulltext search be more productive than a like
searcn. If the use keys in Bic Black Pens then that is all it should bring
up but if they key in pens it should only get pens. With the like I have been
getting despensers. I don't know exactly how Fulltext search would work for
this.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200611/1
Hello MattIrwin
Full text would treat words as words and not return misses as in you case.
You can also use full text to use a thesaurus and have much more complicated
searches, i.e. Pen AND NOT Fountain. Which is much more difficult with LIKE
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> I need to search a Items catalog that has a field that is key word
> seperated by commas. Would a fulltext search be more productive than a
> like
> searcn. If the use keys in Bic Black Pens then that is all it should
> bring up but if they key in pens it should only get pens. With the
> like I have been getting despensers. I don't know exactly how Fulltext
> search would work for this.
>
|||Full text for English will break words at white space. So bic black pen
would be broken as three words, bic, black and pen. Dispensers would be
broken as dispensers. In a free text search this would match with
dispensers, dispenser, dispenser's, and dispensers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MattIrwin via droptable.com" <u28166@.uwe> wrote in message
news:6926b9c20e067@.uwe...
> Hi,
> I need to search a Items catalog that has a field that is key word
> seperated by commas. Would a fulltext search be more productive than a
> like
> searcn. If the use keys in Bic Black Pens then that is all it should
> bring
> up but if they key in pens it should only get pens. With the like I have
> been
> getting despensers. I don't know exactly how Fulltext search would work
> for
> this.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200611/1
>
|||You need to use a word breaker which breaks the words in the form you want,
Greek I take it. Otherwise I think like is your best bet.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23aVerjWNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uo8ZEAyBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> I would like to do the opposite.
> When searching for "pdagog" I also want to return results that contain
> "dagplejepdagog" or "pdagogmedhjlper" is ther no other way to
> accomplish this than using LIKE '%pdagog%'.
> Performance using LIKE is very poor, how do I improve performance?
> Kind Regards,
> Allan Ebdrup
>
|||Allan wrote on Thu, 11 Jan 2007 11:11:51 +0100:

> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uo8ZEAyBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> I would like to do the opposite.
> When searching for "pdagog" I also want to return results that contain
> "dagplejepdagog" or "pdagogmedhjlper" is ther no other way to
> accomplish this than using LIKE '%pdagog%'.
> Performance using LIKE is very poor, how do I improve performance?
The second example is already possible:
SELECT * FROM Table WHERE CONTAINS(*,'pdagog*')
However, the first example isn't. As Hilary has pointed out, there may be a
language dependent wordbreaker that will split the words as you require.
Another option, if you don't mind increasing the storage requirements, is to
index on a reversed version of the data too. Store a copy of the data in
another column in reverse order, such as
KEY Word RWord
1 dagplejepdagog gogadpejelpgad
and create the FTI on both Word and RWord, then you could use:
SELECT * FROM Table WHERE CONTAINS(*,'pdagog* or gogadp*')
However, this won't work when the term you are searching for is in the
middle of a word.
Dan
|||You could be correct here, my knowledge of these languages is not great. The
ligature or digraph commonly occurs in Greek words transliterated into
English, but these do appear more German or Scandinavian.
You might want to check out this document for more info.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:%23rGPgouOHHA.3544@.TK2MSFTNGP03.phx.gbl...
> "Greek"? looks a bit more like a scandinavian language, doesn't it?
> i think Allan is looking for inflectional forms or compound words
> containing a stem-word in question. i'm interested too (in my case for
> German).
> can anyone point us to good documentation on which languages are supported
> and other details? there is some info here:
> http://msdn2.microsoft.com/en-US/library/ms142507.aspx
> but i would want to learn more.
> cheers,
> Tim Hanson
>

FullText Search on Multiple Language

Hi All,
I am trying to implement a FullText search for a table which contains
translations for many different languages. However, different languages
utilized different word breaker for the FullText search to work properly for
that language but I can only assign a single word breaker to a table (for
example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
chinese uses Chinese(PRC), etc).
To overcome this problem, I used horizontal partitioning to split the
table into smaller tables according to its various languages; each assigned
with the word breader for that language. And I try to insert, update, delete
the data in all the tables thru a view which union all the smaller tables.
This seems to work well.
Next, I created a storeprocedure to do a full-text search by searching
each smaller table and appending the result.to a temp table
Example
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
:
The problem is when I do a full-text search for a chinese string in the
table with german word break, it will give the error#7619 ('A clause of the
query contained only ignored words.') and terminate the storeprocedure
immediately. There doesn't seem to be anyway to ignore the error and
continue to the search in the next table(s).
1. Does anyone has anyway to handle the error#7619 in such a way that it
will not terminate the Storeprocedure but continue to search the next table?
OR
2. Is way any other method to implement a full-text search for a table
containing multiple languages (with word breaker correctly implemented)
other then the one described above?
Many thanks in advance
Royston
Royston,
Could you post the full output of -- SELECT @.@.version -- as this would be
most helpful in understanding your environment and providing you with
answers.
First of all, and assuming (for now) that you're using SQL Server 2000, you
do not need to split your table into multiple smaller tables, (one for each
language), as SQL Server 2000 supports multiple collation per column and SQL
Full Text Indexing supports different "Languages for Word Breaker" per
column. Therefore, you can have one column per language and set the
collation and "Language for Word Breaker" at a per column level in one
table. This is one reason I'm requesting the @.@.version info.
In regards to error 7619, when FT Searching for a Chinese string in the
table with German word break, you will need to capture the language before
you issue the FTS query and then run it against the appropriate table (or
column) to avoid this error. Note, that in SQL Server 2005 (currently, still
in beta), you will be able to store multiple languages in one column, and
then issue language specific queries based upon the LCID of the language.
Just curious, why are you running a FT Search query for a Chinese string in
the table with German?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Royston" <royston@.earth9.com> wrote in message
news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to implement a FullText search for a table which contains
> translations for many different languages. However, different languages
> utilized different word breaker for the FullText search to work properly
for
> that language but I can only assign a single word breaker to a table (for
> example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
> chinese uses Chinese(PRC), etc).
> To overcome this problem, I used horizontal partitioning to split the
> table into smaller tables according to its various languages; each
assigned
> with the word breader for that language. And I try to insert, update,
delete
> the data in all the tables thru a view which union all the smaller tables.
> This seems to work well.
> Next, I created a storeprocedure to do a full-text search by searching
> each smaller table and appending the result.to a temp table
> Example
> CREATE TABLE #Temp
> (
> searchresult ntext
> )
> INSERT INTO #Temp (searchresult)
> SELECT translation FROM translation_german WHERE CONTAINS(*,
@.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
> :
> The problem is when I do a full-text search for a chinese string in
the
> table with german word break, it will give the error#7619 ('A clause of
the
> query contained only ignored words.') and terminate the storeprocedure
> immediately. There doesn't seem to be anyway to ignore the error and
> continue to the search in the next table(s).
> 1. Does anyone has anyway to handle the error#7619 in such a way that
it
> will not terminate the Storeprocedure but continue to search the next
table?
> OR
> 2. Is way any other method to implement a full-text search for a table
> containing multiple languages (with word breaker correctly implemented)
> other then the one described above?
> Many thanks in advance
> Royston
>
>
>
>
|||Hi John,
Yes, I am using SQL Server 2000. I am currently using a single column to
store all the various translations, therefore I need to do a FT search for a
Chinese string in the German table (actually, all the smaller tables). The
logic for insertion, update, and delete is simpler with this method (normal
query thru a view will do). Adding of new language requires only addition of
a new smaller table and a quick update to the view. There's no change to the
existing storeprocedures as the storeprocedures access the translation thru
a view.
Here's my simplified sample schema,
Create Table [English_Table]
(
string_id int primary key,
english_text ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
)
Create Table [Translation_German_Table]
(
string_id int,
culture varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_german Primary Key (string_id., culture)
Check (culture = 'de-DE')
)
Create Table [Translation_Chinese_Table]
(
string_id int,
lang_id varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_chinese Primary Key (string_id, culture)
Check (culture = 'zh-CN')
)
Create View [Translation]
(
Select * From [Translation_German_Table]
Union All
Select * From [Translation_Chinese_Table]
)
Storing each language per column will require more complex logic on the
Client side to determine the current language of the translation and to
insert into/ update the appropriate column (addition of new language will
require adding a new column and may also require changes to existing logic).
I will still have to check the language of the search string to implement
the FT search on the correct column (this may pose a problem if the search
string is user input and I don't know what language the user is inputing).
Thanks,
Royston
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Royston,
> Could you post the full output of -- SELECT @.@.version -- as this would be
> most helpful in understanding your environment and providing you with
> answers.
> First of all, and assuming (for now) that you're using SQL Server 2000,
you
> do not need to split your table into multiple smaller tables, (one for
each
> language), as SQL Server 2000 supports multiple collation per column and
SQL
> Full Text Indexing supports different "Languages for Word Breaker" per
> column. Therefore, you can have one column per language and set the
> collation and "Language for Word Breaker" at a per column level in one
> table. This is one reason I'm requesting the @.@.version info.
> In regards to error 7619, when FT Searching for a Chinese string in the
> table with German word break, you will need to capture the language before
> you issue the FTS query and then run it against the appropriate table (or
> column) to avoid this error. Note, that in SQL Server 2005 (currently,
still
> in beta), you will be able to store multiple languages in one column, and
> then issue language specific queries based upon the LCID of the language.
> Just curious, why are you running a FT Search query for a Chinese string
in[vbcol=seagreen]
> the table with German?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Royston" <royston@.earth9.com> wrote in message
> news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
contains[vbcol=seagreen]
> for
(for[vbcol=seagreen]
simplified[vbcol=seagreen]
the[vbcol=seagreen]
> assigned
> delete
tables.[vbcol=seagreen]
searching[vbcol=seagreen]
> @.searchstring)
> the
> the
> it
> table?
table
>
|||Royston, I am not sure what you suggest would work.
First, I don't think you can do Full text search on view, in other word, do
select * from View1 where contains (data,'some search string')
Second, you would still have to choose ONE word breaker on the indexed data
column, so if you have Germany and Chinese in the same column, What word
breaker can you choose?
I am running the similar problem, in my case, I want to full text search on
chinese and english,but struggling to find a solution for it.
Any suggest are welcome.
--Xin Chen
"Royston" <royston@.earth9.com> wrote in message
news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hi John,
> Yes, I am using SQL Server 2000. I am currently using a single column
to
> store all the various translations, therefore I need to do a FT search for
a
> Chinese string in the German table (actually, all the smaller tables). The
> logic for insertion, update, and delete is simpler with this method
(normal
> query thru a view will do). Adding of new language requires only addition
of
> a new smaller table and a quick update to the view. There's no change to
the
> existing storeprocedures as the storeprocedures access the translation
thru
> a view.
> Here's my simplified sample schema,
> Create Table [English_Table]
> (
> string_id int primary key,
> english_text ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> )
> Create Table [Translation_German_Table]
> (
> string_id int,
> culture varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_german Primary Key (string_id., culture)
> Check (culture = 'de-DE')
> )
> Create Table [Translation_Chinese_Table]
> (
> string_id int,
> lang_id varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_chinese Primary Key (string_id, culture)
> Check (culture = 'zh-CN')
> )
> Create View [Translation]
> (
> Select * From [Translation_German_Table]
> Union All
> Select * From [Translation_Chinese_Table]
> )
> Storing each language per column will require more complex logic on
the
> Client side to determine the current language of the translation and to
> insert into/ update the appropriate column (addition of new language will
> require adding a new column and may also require changes to existing
logic).[vbcol=seagreen]
> I will still have to check the language of the search string to implement
> the FT search on the correct column (this may pose a problem if the search
> string is user input and I don't know what language the user is inputing).
> Thanks,
> Royston
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
> you
> each
> SQL
before[vbcol=seagreen]
(or[vbcol=seagreen]
> still
and[vbcol=seagreen]
language.[vbcol=seagreen]
string[vbcol=seagreen]
> in
> contains
languages[vbcol=seagreen]
properly[vbcol=seagreen]
> (for
> simplified
> the
> tables.
> searching
in[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]
> table
implemented)
>
|||Hi Xin Chen,
No, I am not doing the FT search on the view. My solution is to utilize
horizontal partitioning to split my translation table into multiple child
tables each storing only a particular language, and then assign a different
word breaker to each of the child table according to the language it is
storing. I am using a view to union all the child tables so that I can
access all the child tables thru the view as though it is a single table
(that is, I can insert, update, delete via the view instead of referencing
the child tables). Note that I implemented a check on the primary key column
of each of the child table, this will allow the view to know where to
insert/ update or delete the row referenced in the view. I have tried this
and it seems to work.
However, to implement FT search, I created a temporary table and for each
child table I will do a FT search with the search string and append the
result to the temporay table (like the example below). The problem I
encountered is if the search string contain a particular language string
like chinese string, and the storeprocedure is doing a FT search on a child
table with german word breaker, the storeprocedure will generate an error
#7619 ('A clause of the query contained only ignored words.') and terminate
execution. There doesn't seem to be any way I can catch the exception in the
storeprocedure so as to ignore the error and continue the search on the next
child table (that is, the one with the correct word breaker). As suggested
by John, it seems the only way to resolve this is to check the language of
the search string beforehand somehow and direct the FT search to the
respective child table. This shall work, but I am hoping if anyone know how
to catch the error#7619 exception and prevent the storeprocedure from
terminating since it may be difficult to determine the language of the
search string without restricting the user input.
Simplified Sample Code Example,
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*,@.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
Regards,
Royston
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:eNm4kjfKFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Royston, I am not sure what you suggest would work.
> First, I don't think you can do Full text search on view, in other word,
do
> select * from View1 where contains (data,'some search string')
> Second, you would still have to choose ONE word breaker on the indexed
data
> column, so if you have Germany and Chinese in the same column, What word
> breaker can you choose?
> I am running the similar problem, in my case, I want to full text search
on[vbcol=seagreen]
> chinese and english,but struggling to find a solution for it.
> Any suggest are welcome.
> --Xin Chen
> "Royston" <royston@.earth9.com> wrote in message
> news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
column[vbcol=seagreen]
> to
for[vbcol=seagreen]
> a
The[vbcol=seagreen]
> (normal
addition[vbcol=seagreen]
> of
> the
> thru
> the
will[vbcol=seagreen]
> logic).
implement[vbcol=seagreen]
search[vbcol=seagreen]
inputing).[vbcol=seagreen]
> be
2000,[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
> before
> (or
> and
> language.
> string
> languages
> properly
split[vbcol=seagreen]
update,[vbcol=seagreen]
> in
> of
storeprocedure[vbcol=seagreen]
> that
next
> implemented)
>

fulltext search not working

HI!,
Fulltext search is not working, i have two databases, fulltext search
functionality was working fine on both of them till now. but suddenly
fulltext search on one of the databases started giving out errors.
when i try to populate, rebuild was giving out errors.
when i said sp_fulltext_database 'enable' on the db it gave out the error
"An unknown full-text failure (80004005) occurred in function EnumCatalogs
on full-text catalog ''."
when i said sp_help_fulltext_catalogs , it gave out this error
Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable Full-Text Search.
whereas the fulltext catalog functionality is working fine on the other
database.
So, please help me resolve the problem.
The sql server was down for want of space. when space was cleared and sql
server restarted the fulltext catalog problem started.
Has the fulltext catalog problem got anything to do with sql server being
down.
Any help would be greatly appreciated.
Regards,
Prudhvi
I would try to rebuild the catalog.
Also the 80004005 is a generic access denied error message.
Consult this kb article for more information
http://support.microsoft.com/default...b;en-us;295772
"Prudhvi Raju" <PrudhviRaju@.discussions.microsoft.com> wrote in message
news:311D7D42-311B-41EC-9262-93402B8867ED@.microsoft.com...
> HI!,
> Fulltext search is not working, i have two databases, fulltext search
> functionality was working fine on both of them till now. but suddenly
> fulltext search on one of the databases started giving out errors.
> when i try to populate, rebuild was giving out errors.
> when i said sp_fulltext_database 'enable' on the db it gave out the error
> "An unknown full-text failure (80004005) occurred in function EnumCatalogs
> on full-text catalog ''."
> when i said sp_help_fulltext_catalogs , it gave out this error
> Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable Full-Text Search.
> whereas the fulltext catalog functionality is working fine on the other
> database.
> So, please help me resolve the problem.
> The sql server was down for want of space. when space was cleared and sql
> server restarted the fulltext catalog problem started.
> Has the fulltext catalog problem got anything to do with sql server being
> down.
> Any help would be greatly appreciated.
> Regards,
> Prudhvi
>
>
>
|||Prudhvi,
Could you post the full output of -- select @.@.version -- as this is most
important information needed to troubleshoot this FTS error message.
Depending upon the version of SQL Server, you are using, you may want to
review KB article: 295772 (Q295772) "How to debug full-text search when a
7608 (0x80004005) error message occurs in SQL Server" at:
http://support.microsoft.com/default...b;EN-US;295772
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Prudhvi Raju" <PrudhviRaju@.discussions.microsoft.com> wrote in message
news:311D7D42-311B-41EC-9262-93402B8867ED@.microsoft.com...
> HI!,
> Fulltext search is not working, i have two databases, fulltext search
> functionality was working fine on both of them till now. but suddenly
> fulltext search on one of the databases started giving out errors.
> when i try to populate, rebuild was giving out errors.
> when i said sp_fulltext_database 'enable' on the db it gave out the error
> "An unknown full-text failure (80004005) occurred in function EnumCatalogs
> on full-text catalog ''."
> when i said sp_help_fulltext_catalogs , it gave out this error
> Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable Full-Text Search.
> whereas the fulltext catalog functionality is working fine on the other
> database.
> So, please help me resolve the problem.
> The sql server was down for want of space. when space was cleared and sql
> server restarted the fulltext catalog problem started.
> Has the fulltext catalog problem got anything to do with sql server being
> down.
> Any help would be greatly appreciated.
> Regards,
> Prudhvi
>
>
>
|||John,
output of select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
It doesn't allow me to even rebuild.
Can detaching and re-attaching the db solve my probs.
regards,
Prudhvi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Prudhvi,
Thanks for providing the @.@.version info as that allows me to provide you
with a solution to error . Basically, this is a FTS component or registry
key issue, that cannot be resolved by detaching & re-attaching the database
as this problem is at the SQL Server level and not database specific.
What you must do is to "re-install" the SQL Server 2000 Full-text Search
components via your SQL Server 2000 setup CD. As the FTS components are
already installed you will need to force the removal of the FTS installed
checkmark via the removal or renaming of the following tracking registry
key. (If you're not using a named instance, remove "<Instance_Name>\".)
NOTE: be sure to be logged on to the server as either Administrator or as a
member of the server's Admin Group before deleting the below registry key
and stop both the MSSQLServer and the MSSearch services.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<Instance_Name>\Tracking\
{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
Once you're done removed (renamed) the above tracking key, then delete the
MSSearch directory from either:
drive_letter:\Program Files\Common Files\Microsoft Shared\
or
drive_letter::\Program Files\Common Files\System\
Then using your SQL Server 2000 installation CD re-install via "Custom
Installation" the Full-Text Search component (it should be un-checked). When
this completes find and save these files: SearchSetup.log (usually under
\windows or \winnt folders) and sqlsp.log. If any problems, please post
these files.
Re-install the service pack that you may have applied to SQL Server 2000, so
that the newly re-installed MSSearch components are upgraded to SP3 levels
and then re-boot &/or restart the MSSearch and MSSQLServer services.
You may also want to consult or perform the procedures document in the
following Kb article: 827449 "How to manually reinstall the Microsoft Search
service for an instance of SQL 2000" at:
http://support.microsoft.com/default...b;EN-US;827449
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Prudhvi Manthena" <prudhvi.m@.gmail.com> wrote in message
news:uqzgeGjGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> John,
> output of select @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> It doesn't allow me to even rebuild.
> Can detaching and re-attaching the db solve my probs.
> regards,
> Prudhvi
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||John,
As i told you in my first post, the FTS component is working fine on another
database in the same instance of sql server.
i was just wondering if this issue can be sorted out without re-installing
the FTS component as the server is a production database and is hosted on a
remote server.
Regards,
Prudhvi.
"John Kane" wrote:

> Prudhvi,
> Thanks for providing the @.@.version info as that allows me to provide you
> with a solution to error . Basically, this is a FTS component or registry
> key issue, that cannot be resolved by detaching & re-attaching the database
> as this problem is at the SQL Server level and not database specific.
> What you must do is to "re-install" the SQL Server 2000 Full-text Search
> components via your SQL Server 2000 setup CD. As the FTS components are
> already installed you will need to force the removal of the FTS installed
> checkmark via the removal or renaming of the following tracking registry
> key. (If you're not using a named instance, remove "<Instance_Name>\".)
> NOTE: be sure to be logged on to the server as either Administrator or as a
> member of the server's Admin Group before deleting the below registry key
> and stop both the MSSQLServer and the MSSearch services.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
> Server\<Instance_Name>\Tracking\
> {E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
> Once you're done removed (renamed) the above tracking key, then delete the
> MSSearch directory from either:
> drive_letter:\Program Files\Common Files\Microsoft Shared\
> or
> drive_letter::\Program Files\Common Files\System\
> Then using your SQL Server 2000 installation CD re-install via "Custom
> Installation" the Full-Text Search component (it should be un-checked). When
> this completes find and save these files: SearchSetup.log (usually under
> \windows or \winnt folders) and sqlsp.log. If any problems, please post
> these files.
> Re-install the service pack that you may have applied to SQL Server 2000, so
> that the newly re-installed MSSearch components are upgraded to SP3 levels
> and then re-boot &/or restart the MSSearch and MSSQLServer services.
> You may also want to consult or perform the procedures document in the
> following Kb article: 827449 "How to manually reinstall the Microsoft Search
> service for an instance of SQL 2000" at:
> http://support.microsoft.com/default...b;EN-US;827449
> Hope that helps!
> John
>
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Prudhvi Manthena" <prudhvi.m@.gmail.com> wrote in message
> news:uqzgeGjGFHA.1528@.TK2MSFTNGP09.phx.gbl...
>
>
|||Prudhvi,
Sorry, I missed that info in the first post... Usually, this error (An
unknown full-text failure (80004005)...) indicates a problem with the
MSSearch service components or registry key &/or values at the server level
and generally not specific to one database on a server. However, it may be
that specific FT Catalog registry keys/values maybe mis-configured or
missing that are linked to one database. However, you would need to open a
support case with Microsoft PSS SQL Server support as they have methods for
doing extended debugging of this issue to identify the exact cause of this
issue for you. If you cannot re-install the FTS components per the KB
article or the below method, then I'd recommend that you open a support case
with Microsoft SQL Server support.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Prudhvi Raju" <PrudhviRaju@.discussions.microsoft.com> wrote in message
news:11DA5ABC-19AD-460C-84D1-94E3646BC34D@.microsoft.com...
> John,
> As i told you in my first post, the FTS component is working fine on
another
> database in the same instance of sql server.
> i was just wondering if this issue can be sorted out without re-installing
> the FTS component as the server is a production database and is hosted on
a[vbcol=seagreen]
> remote server.
> Regards,
> Prudhvi.
>
> "John Kane" wrote:
registry[vbcol=seagreen]
database[vbcol=seagreen]
installed[vbcol=seagreen]
as a[vbcol=seagreen]
key[vbcol=seagreen]
the[vbcol=seagreen]
When[vbcol=seagreen]
2000, so[vbcol=seagreen]
levels[vbcol=seagreen]
Search[vbcol=seagreen]

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
Regards
Henrik JuelHi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
--
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:

> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
--
Regards
Henrik JuelHi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
--
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:
> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
Regards
Henrik Juel
Hi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:

> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

fulltext search in sql express 2005

I had problem when change database from sqlserver 2000 to sql express 2005.

The fulltext index does not create automatically.

Example:

i use sqldatasource to insert new name to my table.

and then i find it by query like this : select count(*) from mytable where contains(mycol,'newname')

the result is 0

but if i run query to start full index : exec sp_fulltext_catalog 'myfulltext','start_full' and run query

select count(*) from mytable where contains(mycol,'newname')

again. The result is 1.

So i alway run exec sp_fulltext_catalog 'myfulltext','start_full' after insert or update, delete to create fulltext index. When i use sql server 2000 , i didn't need do that, it automatic.

Pls help me !!! how to make fulltext index create auto in sql express 2005 .

You need to set "Change Tracking" to automatic.

The index update will not be instantaneous, but it will be automatic.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=680500&SiteID=1

http://technet.microsoft.com/en-us/library/ms187317.aspx

fulltext search HTML problem

Hi,

currently we use text field in table to store HTML, and we use fulltext index to search this field, but we can not always get right results, for example, given text - "<P><STRONG>Access High Resorts<BR>", I can not get result if I search for "Access" or "cce", but if fine if I search for "High" or 'Resor", since I use CONTAINS, and CONTAINS can not search for postfix.

I know I can create another image data type field, and seach will filter HTML tag, but it will take long time to do.

Is there a way I can search postfix or mid-field of word in fulltext search? any idea or suggestion?

Thanks in advancethe sample string should like this - "<P><STRONG>Access High Resorst<BR>"

FullText Search Error 1075: The dependency service does not exist or

Hi Guys,
Can you please help me with the following problem:
My website keeps crashing and the FullText Search cannot start. I keep
getting errors 7003 in the Event Log with the following message:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7003
Date: 3/8/2008
Time: 3:18:24 PM
User: N/A
Computer: MASTER
Description:
The SQL Server FullText Search (MSSQLSERVER) service depends on the
following nonexistent service: NTLMSSP
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
When trying to start the service from Services window I get the
following error:
FullText Search Error 1075: The dependency service does not exist or
has been marked for deletion
Checked the Dependencies in Registry and sqlserver has the following
dependencies: RPCSS NTLMSSP
I have had to restart the server multiple times and cannot figure out
what the problem is.
Windows Server 2003 Standart Edition
SQL Server 2005 Workgroup Edition
Please help
AlbanoOn Mar 9, 2:56=A0am, alstef...@.gmail.com wrote:
> Hi Guys,
> Can you please help me with the following problem:
> My website keeps crashing and the FullText Search cannot start. I keep
> getting errors 7003 in the Event Log with the following message:
> Event Type: Error
> Event Source: Service Control Manager
> Event Category: None
> Event ID: 7003
> Date: =A03/8/2008
> Time: =A03:18:24 PM
> User: =A0N/A
> Computer: MASTER
> Description:
> The SQL Server FullText Search (MSSQLSERVER) service depends on the
> following nonexistent service: NTLMSSP
> For more information, see Help and Support Center athttp://go.microsoft.co=
m/fwlink/events.asp.
> When trying to start the service from Services window I get the
> following error:
> FullText Search Error 1075: The dependency service does not exist or
> has been marked for deletion
> Checked the Dependencies in Registry and sqlserver has the following
> dependencies: RPCSS NTLMSSP
> I have had to restart the server multiple times and cannot figure out
> what the problem is.
> Windows Server 2003 Standart Edition
> SQL Server 2005 Workgroup Edition
> Please help
> Albano
Try this
1. Open the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet
\Services\msftesql
2. Rename the value DependOnService to anything
3. Restart the server
This have helped me.

FullText Search data model for speed

Which method is faster for full-text search
One row big varchar field
comment VARCHAR(1000)
on lots of small varchar fieds
like 10 rows comment VARCHAR(100)
Message posted via http://www.sqlmonster.com
Kuido,
Could you post the full output of the below SQL code as this is very helpful
to understanding your environment as well as troubleshooting SQL FTS issues
as both SQL Server version and the OS platform play a part in FTS
performance tuning:
use <your_database_name>
SELECT @.@.version
SELECT @.@.language
SELECT count(*) from <your_true_FT-enabled_table_name>
The biggest factor in both FT Indexing and FT Searching is the number of
rows in your FT-enable table. Specifically, for a table with one row of
large text will be just as fast as 10 rows with smaller text. Furthermore,
in this situation (1 row table vs. 10 row table), T-SQL LIKE will be faster
as with very small tables all the rows will fit in one or a couple of data
pages, while the CONTAINS FTS queries will have to use the external MSSearch
service.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||SQL FTS query performance is most sensitive to the number of rows returned
in a query. So if you can limit the number of rows returned you will get
better performance. So 1 big varchar field would probably offer better
performance.
However if you can partition your table into sub tables, you will get even
better performance this way as long as you are only doing a single hit on
MSSearch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||I'm just planning database application
SQL-server will be Microsoft SQL Server 2000
Language - eesti (Estonian)
and there will be 15 000 000 rows in database
Message posted via http://www.sqlmonster.com
|||Kuido,
Then you should review all the SQL FTS links and resources at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
You should also review SQL Server 2000 Books Online (BOL) and using the
search tab, search on "full text" (with the double quotes) and especially
the BOL title: "Full-text Search Recommendations". Additional, since your
language will be Estonian, you will need to use the "neutral wordbreaker" as
Estonian is not one of the subset of languages supported by SQL FTS.
Specifically, for each of your FT-enabled columns, set the "Language for
Word Breaker" to Neutral.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:677e90c6408e4927ad6a290c05d61745@.SQLMonster.c om...
> I'm just planning database application
> SQL-server will be Microsoft SQL Server 2000
> Language - eesti (Estonian)
> and there will be 15 000 000 rows in database
> --
> Message posted via http://www.sqlmonster.com