Full text stuck in a rut - error msg 7619

I have a full-text index on a production db. The catalog is currently empty. When I try to populate it, I get a message saying I must enable full text for this database. When I try to run sp_fulltext_database 'enable', I get the following message:
Server: Msg 7619, Level 16, State 1, Procedure sp_fulltext_database, Line 61
Execution of a full-text operation failed. The process cannot access the file because it is being used by another process.
Our OS team could not find any processes other than sqlserver.exe using files in the FTData dir. MSSearch is currently owned by the localsystem account, and ownership has not been changed since the SQL Server install.
I am using SQL 2000 EE. on a Windows 2000 cluster. SP3A is installed.
Has anyone else come across this error? Any advice would be appreciated!

Quote:

Originally posted by lisanova22
I have a full-text index on a production db. The catalog is currently empty. When I try to populate it, I get a message saying I must enable full text for this database. When I try to run sp_fulltext_database 'enable', I get the following message:
Server: Msg 7619, Level 16, State 1, Procedure sp_fulltext_database, Line 61
Execution of a full-text operation failed. The process cannot access the file because it is being used by another process.
Our OS team could not find any processes other than sqlserver.exe using files in the FTData dir. MSSearch is currently owned by the localsystem account, and ownership has not been changed since the SQL Server install.
I am using SQL 2000 EE. on a Windows 2000 cluster. SP3A is installed.
Has anyone else come across this error? Any advice would be appreciated!

Full Text shutdown status

I have a large full text index (30 millions records) that is
populating on a live server (they are just doing without the full text
search stuff for now) and the index goes into shutdown mode. What is
that? Last time it did this i stopped and started the mssearch
service and it started back up again. The only problem is that when
it completed last time the number of records in the index was like 8
million. I did a test and a bunch of the rows did not get into the
population even though it supposedly finished correctly. So I am
hesitant to restart the service since I'm afriad it will not
completely fill the index again.
This is SQL 2000 on a Windows 2003 entprise box. But not in a
cluster. It's a stand alone machine. 4 proc, 16 gigs on the machine.
13 of the gigs used by sql. The box does stay pretty busy but not
completely bogged down.
Ryan
are there any event messages in the application log from MSSearch or MSSCI?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"RyanOly" <ryanoly@.gmail.com> wrote in message
news:661760f9.0407141123.66388c39@.posting.google.c om...
> I have a large full text index (30 millions records) that is
> populating on a live server (they are just doing without the full text
> search stuff for now) and the index goes into shutdown mode. What is
> that? Last time it did this i stopped and started the mssearch
> service and it started back up again. The only problem is that when
> it completed last time the number of records in the index was like 8
> million. I did a test and a bunch of the rows did not get into the
> population even though it supposedly finished correctly. So I am
> hesitant to restart the service since I'm afriad it will not
> completely fill the index again.
> This is SQL 2000 on a Windows 2003 entprise box. But not in a
> cluster. It's a stand alone machine. 4 proc, 16 gigs on the machine.
> 13 of the gigs used by sql. The box does stay pretty busy but not
> completely bogged down.
> Ryan

Full Text Searching

I want to use full text indexing. I have read you need to leave 50MB spare
on the server. Is this per index,database or instance of SQL Server and does
it increase with the size of the table or is just a static amount virtual
memory to be put aside. Regards.Where did you read? Could you show us a link? Is that RAM or hard disk
space?
Full-Text search service, indexes the data in your tables, in its own index
files outside SQL Server. So, the bigger your tables, the bigger the full
text catalog file size.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chris Kennedy" <nospam@.microsoft.com> wrote in message
news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
I want to use full text indexing. I have read you need to leave 50MB spare
on the server. Is this per index,database or instance of SQL Server and does
it increase with the size of the table or is just a static amount virtual
memory to be put aside. Regards.|||I read it in Inside SQL Server. I may have got it wrong. I read 5O MB plus
about 30% of the table size.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
| Where did you read? Could you show us a link? Is that RAM or hard disk
| space?
|
| Full-Text search service, indexes the data in your tables, in its own
index
| files outside SQL Server. So, the bigger your tables, the bigger the full
| text catalog file size.
| --
| HTH,
| Vyas, MVP (SQL Server)
| http://vyaskn.tripod.com/
| Is .NET important for a database professional?
| http://vyaskn.tripod.com/poll.htm
|
|
|
|
| "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
| I want to use full text indexing. I have read you need to leave 50MB spare
| on the server. Is this per index,database or instance of SQL Server and
does
| it increase with the size of the table or is just a static amount virtual
| memory to be put aside. Regards.
|
|
|
||||Chris, you are referencing Kalen's "Inside SQL Server 2000" book on page583
as she states that at least 50MB of hard drive space must be available to
create a FT Catalog on the disk drive. I don't have disk small enough to
test this <g>, but if you are FT Indexing via the FT Wizard and placing the
FT Catalog on a disk drive of less that 50MB, you will get an error.
Although, I found no reference to 30% of anything. Could you clarify more
what your concerns are?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Chris Kennedy" <nospam@.microsoft.com> wrote in message
news:#$7LI#33DHA.2544@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I read it in Inside SQL Server. I may have got it wrong. I read 5O MB plus
> about 30% of the table size.
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
> | Where did you read? Could you show us a link? Is that RAM or hard disk
> | space?
> |
> | Full-Text search service, indexes the data in your tables, in its own
> index
> | files outside SQL Server. So, the bigger your tables, the bigger the

---

full

quote:

---

> | text catalog file size.
> | --
> | HTH,
> | Vyas, MVP (SQL Server)
> | http://vyaskn.tripod.com/
> | Is .NET important for a database professional?
> | http://vyaskn.tripod.com/poll.htm
> |
> |
> |
> |
> | "Chris Kennedy" <nospam@.microsoft.com> wrote in message
> | news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
> | I want to use full text indexing. I have read you need to leave 50MB

---

spare

quote:

---

> | on the server. Is this per index,database or instance of SQL Server and
> does
> | it increase with the size of the table or is just a static amount

---

virtual

quote:

---

> | memory to be put aside. Regards.
> |
> |
> |
> |
>

---

|||What amount of space will a full text index take in e. g. a 10 MB table.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OBnfOx53DHA.2460@.TK2MSFTNGP10.phx.gbl...
| Chris, you are referencing Kalen's "Inside SQL Server 2000" book on
page583
| as she states that at least 50MB of hard drive space must be available to
| create a FT Catalog on the disk drive. I don't have disk small enough to
| test this <g>, but if you are FT Indexing via the FT Wizard and placing
the
| FT Catalog on a disk drive of less that 50MB, you will get an error.
| Although, I found no reference to 30% of anything. Could you clarify more
| what your concerns are?
|
| Note, you can also post FTS related questions to the newsgroup:
| microsoft.public.sqlserver.fulltext
| Regards,
| John
|
|
|
| "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| news:#$7LI#33DHA.2544@.TK2MSFTNGP10.phx.gbl...
| > I read it in Inside SQL Server. I may have got it wrong. I read 5O MB
plus
| > about 30% of the table size.
| >
| > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
| > news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
| > | Where did you read? Could you show us a link? Is that RAM or hard disk
| > | space?
| > |
| > | Full-Text search service, indexes the data in your tables, in its own
| > index
| > | files outside SQL Server. So, the bigger your tables, the bigger the
| full
| > | text catalog file size.
| > | --
| > | HTH,
| > | Vyas, MVP (SQL Server)
| > | http://vyaskn.tripod.com/
| > | Is .NET important for a database professional?
| > | http://vyaskn.tripod.com/poll.htm
| > |
| > |
| > |
| > |
| > | "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| > | news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
| > | I want to use full text indexing. I have read you need to leave 50MB
| spare
| > | on the server. Is this per index,database or instance of SQL Server
and
| > does
| > | it increase with the size of the table or is just a static amount
| virtual
| > | memory to be put aside. Regards.
| > |
| > |
| > |
| > |
| >
| >
|
|

Full Text Searching

I want to use full text indexing. I have read you need to leave 50MB spare
on the server. Is this per index,database or instance of SQL Server and does
it increase with the size of the table or is just a static amount virtual
memory to be put aside. Regards.Where did you read? Could you show us a link? Is that RAM or hard disk
space?
Full-Text search service, indexes the data in your tables, in its own index
files outside SQL Server. So, the bigger your tables, the bigger the full
text catalog file size.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chris Kennedy" <nospam@.microsoft.com> wrote in message
news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
I want to use full text indexing. I have read you need to leave 50MB spare
on the server. Is this per index,database or instance of SQL Server and does
it increase with the size of the table or is just a static amount virtual
memory to be put aside. Regards.|||I read it in Inside SQL Server. I may have got it wrong. I read 5O MB plus
about 30% of the table size.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
| Where did you read? Could you show us a link? Is that RAM or hard disk
| space?
|
| Full-Text search service, indexes the data in your tables, in its own
index
| files outside SQL Server. So, the bigger your tables, the bigger the full
| text catalog file size.
| --
| HTH,
| Vyas, MVP (SQL Server)
| http://vyaskn.tripod.com/
| Is .NET important for a database professional?
| http://vyaskn.tripod.com/poll.htm
|
|
|
|
| "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
| I want to use full text indexing. I have read you need to leave 50MB spare
| on the server. Is this per index,database or instance of SQL Server and
does
| it increase with the size of the table or is just a static amount virtual
| memory to be put aside. Regards.
|
|
|
||||Chris, you are referencing Kalen's "Inside SQL Server 2000" book on page583
as she states that at least 50MB of hard drive space must be available to
create a FT Catalog on the disk drive. I don't have disk small enough to
test this <g>, but if you are FT Indexing via the FT Wizard and placing the
FT Catalog on a disk drive of less that 50MB, you will get an error.
Although, I found no reference to 30% of anything. Could you clarify more
what your concerns are?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Chris Kennedy" <nospam@.microsoft.com> wrote in message
news:#$7LI#33DHA.2544@.TK2MSFTNGP10.phx.gbl...
> I read it in Inside SQL Server. I may have got it wrong. I read 5O MB plus
> about 30% of the table size.
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
> | Where did you read? Could you show us a link? Is that RAM or hard disk
> | space?
> |
> | Full-Text search service, indexes the data in your tables, in its own
> index
> | files outside SQL Server. So, the bigger your tables, the bigger the
full
> | text catalog file size.
> | --
> | HTH,
> | Vyas, MVP (SQL Server)
> | http://vyaskn.tripod.com/
> | Is .NET important for a database professional?
> | http://vyaskn.tripod.com/poll.htm
> |
> |
> |
> |
> | "Chris Kennedy" <nospam@.microsoft.com> wrote in message
> | news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
> | I want to use full text indexing. I have read you need to leave 50MB
spare
> | on the server. Is this per index,database or instance of SQL Server and
> does
> | it increase with the size of the table or is just a static amount
virtual
> | memory to be put aside. Regards.
> |
> |
> |
> |
>|||What amount of space will a full text index take in e. g. a 10 MB table.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OBnfOx53DHA.2460@.TK2MSFTNGP10.phx.gbl...
| Chris, you are referencing Kalen's "Inside SQL Server 2000" book on
page583
| as she states that at least 50MB of hard drive space must be available to
| create a FT Catalog on the disk drive. I don't have disk small enough to
| test this <g>, but if you are FT Indexing via the FT Wizard and placing
the
| FT Catalog on a disk drive of less that 50MB, you will get an error.
| Although, I found no reference to 30% of anything. Could you clarify more
| what your concerns are?
|
| Note, you can also post FTS related questions to the newsgroup:
| microsoft.public.sqlserver.fulltext
| Regards,
| John
|
|
|
| "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| news:#$7LI#33DHA.2544@.TK2MSFTNGP10.phx.gbl...
| > I read it in Inside SQL Server. I may have got it wrong. I read 5O MB
plus
| > about 30% of the table size.
| >
| > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
| > news:#FAAcBs3DHA.484@.TK2MSFTNGP10.phx.gbl...
| > | Where did you read? Could you show us a link? Is that RAM or hard disk
| > | space?
| > |
| > | Full-Text search service, indexes the data in your tables, in its own
| > index
| > | files outside SQL Server. So, the bigger your tables, the bigger the
| full
| > | text catalog file size.
| > | --
| > | HTH,
| > | Vyas, MVP (SQL Server)
| > | http://vyaskn.tripod.com/
| > | Is .NET important for a database professional?
| > | http://vyaskn.tripod.com/poll.htm
| > |
| > |
| > |
| > |
| > | "Chris Kennedy" <nospam@.microsoft.com> wrote in message
| > | news:uzfFfzq3DHA.2888@.tk2msftngp13.phx.gbl...
| > | I want to use full text indexing. I have read you need to leave 50MB
| spare
| > | on the server. Is this per index,database or instance of SQL Server
and
| > does
| > | it increase with the size of the table or is just a static amount
| virtual
| > | memory to be put aside. Regards.
| > |
| > |
| > |
| > |
| >
| >
|
|

Full Text Search Performance

Hello,
I have full text search enabled on two large tables, the first one has 3
million records (FT index column length is around 512-1025 char), and the
other one has around 1.6 records (FT index column length is around 50-120
char)
The simplest query that uses the smiplest form of FT on the first table FT
index search needs 23-30 seconds to complete and from 10-15 seconds on the
seconds table as it the indexed column is smaller.
SQL server is deployed on 4 P servers with 8 GB of memory and attached to a
very powerful SAN system. Memory usage on the server is not very high so I
believe there is enough memory for the mssearch service to use.
I just wonder if this is the normal throughput of the Full Text search of
SQL Server. If not, I will appreciate any tips and hints that might be the
reason of the system.
Ali Salem
That depends. SQL FTS performance is most sensitive to the number of rows
you are returning. You should limit your results set as much as possible - I
think you will find that the practical limit for most applications is around
100-200 rows.
Limit it by using the the top_n_by_rank operator in ContainsTable or
FreeTextTable, ie
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'test',200
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||thanks for your reply.
I cannot limit the search in this way, as there are other parameters in my
query that can affect the returned result set and that are ourside the FTS.
I am asking for any figures about FTS performance? What should I expect from
it? Is this behavior am getting normal for the data size or, I am having
something wrong
"Hilary Cotter" wrote:

> That depends. SQL FTS performance is most sensitive to the number of rows
> you are returning. You should limit your results set as much as possible - I
> think you will find that the practical limit for most applications is around
> 100-200 rows.
> Limit it by using the the top_n_by_rank operator in ContainsTable or
> FreeTextTable, ie
> USE Northwind
> GO
> SELECT FT_TBL.Description,
> FT_TBL.CategoryName,
> KEY_TBL.RANK
> FROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> 'test',200
> ) AS KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> a
>
>
|||No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> thanks for your reply.
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
> I am asking for any figures about FTS performance? What should I expect
from[vbcol=seagreen]
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
> "Hilary Cotter" wrote:
rows[vbcol=seagreen]
possible - I[vbcol=seagreen]
around[vbcol=seagreen]
3[vbcol=seagreen]
the[vbcol=seagreen]
50-120[vbcol=seagreen]
table FT[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
so I[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
|||Ali Salem,
Could you provide the full output of the following SQL script as it is very
helpful in troubleshooting SQL FTS issue and understanding your environment
and issues!
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage')
go
Additionally, where is your FT Catalog located as by default it is created
under the \FTDATA directory where you have SQL Server installed as well as
where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located
relative to the location of your FT Catalog folder? If your 'ResourceUsage
is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service
'resource_usage' <value>, where <value> is 5.
Thanks,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||Thanks.
- We are using enlgihs language.
- Returned number of rows should be very large (thousands, in some cases it
can be 100, 000). this from the Full-Text Seach, other SQL filters will be
applied as well, but from mssearch this is what will be returned. Please note
that the table size is around 3million records.
- The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1,
'ABCD') need around 30 seconds complete.
I do appreciate any help.
thank you
"Hilary Cotter" wrote:

> No, this is highly abnormal. Performance should be sub second. However I
> need to know what your queries look like, ie how many search arguments, how
> many rows are returned, and what language you are querying in.
>
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> FTS.
> from
> rows
> possible - I
> around
> 3
> the
> 50-120
> table FT
> the
> to
> so I
> of
> the
>
>
|||Below are the result of the script you requested me to run it:
My FT files are stored on SAN storage. So IO should be performing well.
Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql
server performance! I dont want to make the full text search fast by slowing
down SQL Server itself.
I have splitted the result into two posts so that I can post it here.
Thank you for you help
------
us_english
(1 row(s) affected)
------
------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
------
------
-- -- --
5 ICSubject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
6 Subject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
7 Names
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
(3 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- -- --
----
dbo
IC_SUBJECTS
PK_IC_SUBJECTS
1 1 ICSubject
dbo
MAIN
PK_INOUT_MAIN
1 1 Subject
dbo
PERSONS
PK_PERSONS
1 1 Names
(3 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID
FULLTEXT_LANGUAGE
------
-- --
-----
-----
-- --
--- --
dbo
2069582411 IC_SUBJECTS
ITEM_DESCRIPTION_AR
3 NULL
NULL 0
dbo
274100017 MAIN
SUBJECT
42 NULL
NULL 0
dbo
466100701 PERSONS
FIRSTNAME_AR
4 NULL
NULL 0
dbo
466100701 PERSONS
LASTNAME_AR
8 NULL
NULL 0
dbo
466100701 PERSONS
FULL_NAME
9 NULL
NULL 0
dbo
466100701 PERSONS
FATHERNAME_AR
12 NULL
NULL 0
dbo
466100701 PERSONS
GRANDFATHERNAME_AR
14 NULL
NULL 0
(7 row(s) affected)
Name
Owner
Type Created_datetime
------
------
-- --
MAIN
dbo
user table 2004-10-14 18:14:31.457
|||The Rest of the script result, one more post is required
Column_name
Type
Computed Length Prec Scale Nullable
TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- -- --
-- --
-- --
DOCID
int
no 4 10 0 no
(n/a)
(n/a) NULL
DESCRIPTION
varchar
no 255 yes
no
no Arabic_CI_AS
DOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
RDOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
EXT_PARTY_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
EXT_PARTY_REP_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBCAT_ID
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
STATUS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CONFID_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REFERENCE_NO
varchar
no 50 yes
no
no Arabic_CI_AS
FORWARD_TO
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FORWARD_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
TO_REMIND
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
REMIND_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
REMARKS
varchar
no 1024 yes
no
no Arabic_CI_AS
REVISION_NO
varchar
no 50 yes
no
no Arabic_CI_AS
TRAN_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
SYSTEM_ID
varchar
no 50 yes
no
no Arabic_CI_AS
IP
varchar
no 50 yes
no
no Arabic_CI_AS
CHECK_SUM
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
SUBMIT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT
nvarchar
no 8000 yes
(n/a)
(n/a) Arabic_CI_AS
PRIORITY_NO
int
no 4 10 0 yes
(n/a)
(n/a) NULL
PROCESS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REMIND_TIME
varchar
no 20 yes
no
no Arabic_CI_AS
WAITING_FOR_REPLY
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SITE_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
DELIVERY_METHOD_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
GROUPID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
LINK_STATUS
int
no 4 10 0 yes
(n/a)
(n/a) NULL
INITIAL_PROCEDURE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CREATOR_USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
IS_SPLITTED
bit
no 1 yes
(n/a)
(n/a) NULL
CHAR_FIELD2
varchar
no 500 yes
no
no Arabic_CI_AS
CHAR_FIELD5_AR
nvarchar
no 100 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD1
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
CHAR_FIELD66_AR
nvarchar
no 2000 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD6
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD1
int
no 4 10 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD2
int
no 4 10 0 yes
(n/a)
(n/a) NULL
NUM_FIELD2
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD3
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD4
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD5
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
DATE_FIELD1
datetime
no 8 yes
(n/a)
(n/a) NULL
DATE_FIELD2
datetime
no 8 yes
(n/a)
(n/a) NULL
CHAR_FIELD1
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD3
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD4
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD5
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD1_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD2_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD4_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
Identity
Seed Increment
Not For Replication
------
-- ---
--- --
No identity column defined.
NULL NULL
NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
SECONDARY2
|||Last post of the script result.
index_name
index_description
index_keys
------
------
----- --
------
IX_Index1
nonclustered located on SECONDARY2
DOCID, STATUS_ID, CABID, YEAR,
CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
IX_Index2
nonclustered located on SECONDARY4
INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
YEAR, DOCDATE, OUTBOUND_REQUEST
IX_Index3
nonclustered located on SECONDARY2
GROUPID
IX_Index4
nonclustered located on SECONDARY4
YEAR, EXT_DOCNO, RDOCDATE_HJ,
EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
IX_Index5
nonclustered located on SECONDARY2
REQUEST_NO
IX_Index6
nonclustered located on SECONDARY4
STATUS_ID, CABID
PK_MAIN
clustered, unique, primary key located on SECONDARY2
DOCNO
constraint_type
constraint_name
delete_action update_action status_enabled
status_for_replication constraint_keys
------
-- -- -- --
-- --
------
-----
DEFAULT on column OUTBOUND_REQUEST
DF_MAIN_OUTBOUND_REQUEST
(n/a) (n/a) (n/a) (n/a)
(0)
FOREIGN KEY
FK_MAIN_EMPLOYEES
No Action No Action Enabled
Not_For_Replication USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES1
No Action No Action Enabled
Not_For_Replication WRITER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES2
No Action No Action Enabled
Not_For_Replication CREATOR_USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_LO_CONFIDENTIAL
No Action No Action Enabled
Not_For_Replication CONFID_ID
REFERENCES dbo.LO_CONFIDENTIAL (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_DOC_SOURCE_TYPE
No Action No Action Enabled
Not_For_Replication DOC_ORIGN_SOURCE
REFERENCES dbo.LO_DOC_SOURCE_TYPE
(TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS
No Action No Action Enabled
Not_For_Replication DELIVERY_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS1
No Action No Action Enabled
Not_For_Replication LINK_STATUS
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS4
No Action No Action Enabled
Not_For_Replication RECEIVE_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_PRIORITY
No Action No Action Enabled
Not_For_Replication PRIORITY_NO
REFERENCES dbo.LO_PRIORITY (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION
No Action No Action Enabled
Not_For_Replication EXT_PARTY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION1
No Action No Action Enabled
Not_For_Replication EXT_PARTY_REP_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION2
No Action No Action Enabled
Not_For_Replication CITY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SIGNATURE_TYPE
No Action No Action Enabled
Not_For_Replication DIWAN_REP_ID
REFERENCES dbo.LO_SIGNATURE_TYPE (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SITE
No Action No Action Enabled
Not_For_Replication DELIVERY_PLACE
REFERENCES dbo.LO_SITE (SITE_ID)
FOREIGN KEY
FK_MAIN_LO_STATUS
No Action No Action Enabled
Not_For_Replication STATUS_ID
REFERENCES dbo.LO_STATUS (TAB_ID)
FOREIGN KEY
FK_MAIN_MAIN_GROUP_HDR
No Action No Action Enabled
Not_For_Replication GROUPID
REFERENCES dbo.MAIN_GROUP_HDR (
GROUPID)
FOREIGN KEY
FK_MAIN_PROCESSES
No Action No Action Enabled
Not_For_Replication INITIAL_PROCEDURE
REFERENCES dbo.PROCESSES (
PROCESS_ID)
PRIMARY KEY (clustered)
PK_MAIN
(n/a) (n/a) (n/a) (n/a)
DOCNO
Table is referenced by foreign key
------
------
dbo.TASKS: FK_TASKS_MAIN
dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
dbo.COPY: FK_COPY_MAIN
dbo.INQUIRY: FK_INQUIRY_MAIN
dbo.LINKS_F: FK_LINKS_F_MAIN
dbo.CLASS: FK_CLASS_MAIN
dbo.XLASS: FK_CLASS_MAIN
dbo.REQUEST: FK_REQUEST_MAIN
dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
dbo.PERSONS: FK_PERSONS_MAIN
dbo.RESERVED: FK_RESERVED_MAIN
dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
Table is referenced by views
------
3
(1 row(s) affected)
|||Ali,
First of all, thank you for providing this info as it is most helpful! You
are using SQL Server 2000 SP2 on Windows 2000 Server SP3 and your default
language is US_English (1033). However, all of your char, varchar and
nvarchar columns using the Arabic_CI_AS collation with the FT-enabled column
"Language for Word Breaker" set to Neutral. Can I assume that these column
contain Arabic text?
Yes, I can understand why you want the Resource_Usage level to remain at 3,
but you might try setting to 5, as you can always set it lower if it is
affecting your other SQL Server processing. Although, this bump in the
MSSearch resource usage might not be significant. Note, it affect how much
memory MSSearch can use up to a max if 512MB RAM, if available as well as
the number of concurrent connections allowed.
What is the drive letter for your SAN storage? Could you also run the
following SQL query and post it's results?
sp_helpdb <full_text_enabled_database_name>
I'm not sure if the use of Arabic text is causing the poor query performance
(even with the Neutral wordbreaker), but language is a consideration with
FTS query performance issues.
Thanks again,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:D20604B8-21E5-496F-A957-1F1CF8949716@.microsoft.com...
> Last post of the script result.
>
>
> index_name
>
> index_description
>
> index_keys
>
>
>
> ----
----
> --
> ----

> ----
-- --
> ----
----
> ----
--
> IX_Index1
>
> nonclustered located on SECONDARY2
>
> DOCID, STATUS_ID, CABID, YEAR,
> CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
> IX_Index2
>
> nonclustered located on SECONDARY4
>
> INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
> YEAR, DOCDATE, OUTBOUND_REQUEST
> IX_Index3
>
> nonclustered located on SECONDARY2
>
> GROUPID
> IX_Index4
>
> nonclustered located on SECONDARY4
>
> YEAR, EXT_DOCNO, RDOCDATE_HJ,
> EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
> IX_Index5
>
> nonclustered located on SECONDARY2
>
> REQUEST_NO
> IX_Index6
>
> nonclustered located on SECONDARY4
>
> STATUS_ID, CABID
> PK_MAIN
>
> clustered, unique, primary key located on SECONDARY2
>
> DOCNO
>
> constraint_type
>
> constraint_name
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
> ----
----
> --
> ----
--
> -- -- -- --
> -- --
> ----
----
> ----
--
> DEFAULT on column OUTBOUND_REQUEST
>
> DF_MAIN_OUTBOUND_REQUEST
>
> (n/a) (n/a) (n/a) (n/a)
> (0)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES
>
> No Action No Action Enabled
> Not_For_Replication USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES1
>
> No Action No Action Enabled
> Not_For_Replication WRITER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES2
>
> No Action No Action Enabled
> Not_For_Replication CREATOR_USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_LO_CONFIDENTIAL
>
> No Action No Action Enabled
> Not_For_Replication CONFID_ID
>
>
>
> REFERENCES dbo.LO_CONFIDENTIAL (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_DOC_SOURCE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DOC_ORIGN_SOURCE
>
>
>
> REFERENCES dbo.LO_DOC_SOURCE_TYPE
> (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS1
>
> No Action No Action Enabled
> Not_For_Replication LINK_STATUS
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS4
>
> No Action No Action Enabled
> Not_For_Replication RECEIVE_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_PRIORITY
>
> No Action No Action Enabled
> Not_For_Replication PRIORITY_NO
>
>
>
> REFERENCES dbo.LO_PRIORITY (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION1
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_REP_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION2
>
> No Action No Action Enabled
> Not_For_Replication CITY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SIGNATURE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DIWAN_REP_ID
>
>
>
> REFERENCES dbo.LO_SIGNATURE_TYPE (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SITE
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_PLACE
>
>
>
> REFERENCES dbo.LO_SITE (SITE_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_STATUS
>
> No Action No Action Enabled
> Not_For_Replication STATUS_ID
>
>
>
> REFERENCES dbo.LO_STATUS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_MAIN_GROUP_HDR
>
> No Action No Action Enabled
> Not_For_Replication GROUPID
>
>
>
> REFERENCES dbo.MAIN_GROUP_HDR (
> GROUPID)
> FOREIGN KEY
>
> FK_MAIN_PROCESSES
>
> No Action No Action Enabled
> Not_For_Replication INITIAL_PROCEDURE
>
>
>
> REFERENCES dbo.PROCESSES (
> PROCESS_ID)
> PRIMARY KEY (clustered)
>
> PK_MAIN
>
> (n/a) (n/a) (n/a) (n/a)
> DOCNO
>
> Table is referenced by foreign key
>
>
>
> ----
----
> ----
----
> --
> dbo.TASKS: FK_TASKS_MAIN
> dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
> dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
> dbo.COPY: FK_COPY_MAIN
> dbo.INQUIRY: FK_INQUIRY_MAIN
> dbo.LINKS_F: FK_LINKS_F_MAIN
> dbo.CLASS: FK_CLASS_MAIN
> dbo.XLASS: FK_CLASS_MAIN
> dbo.REQUEST: FK_REQUEST_MAIN
> dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
> dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
> dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
> dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
> dbo.PERSONS: FK_PERSONS_MAIN
> dbo.RESERVED: FK_RESERVED_MAIN
> dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
> Table is referenced by views
>
> ----
----
> --
>
> --
> 3
> (1 row(s) affected)
>
sql

Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.
You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
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:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.
|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:

> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> 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:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>

Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to conver
t
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
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:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:

> You could create the ifilter and have it emit the text data which is store
d
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> 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 messag
e
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>

Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
--
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:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> 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:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> > I'm looking for suggestions on the best approach for creating a full text
> > search index on a varbinary column. The varbinary data is the binary
> > serialization of a custom clr datatype (but to be clear -- the column is
> > typed varbinary, not the custom type).
> >
> > I thought I might be able to create a computed column that converted the
> > varbinary to the custom type, then used methods on the custom type to
> > convert
> > to string. But it seems like I can't do that without persisting the
> > computed
> > column which I really don't want to do.
> >
> > Another approach I'm considering is to create an iFilter for the datatype,
> > then create a computed column that just returns the column type I'd
> > register
> > for the iFilter. That just seems a little heavy weight to me and has
> > deployment headaches.
> >
> > I can't help think I'm missing something obvious. Any ideas?
> >
> > Thanks.
>
>sql

Full Text Search 'Near'

using SQL server 2005 I have a table which has full text search index enabled

when I run a search using the 'Near' option it returns rows which meet the criteria, how ever the criteria are not 'near' each other they are several hundred words apart.

What is the problem

How near is near

thanks in advance

Glyn

Hi,

The NEAR operator is used to find words which are in close proximity to one another. For example, consider the following three rows in a table:

TextValue

==========

The product being used is SQL Server 2005 Enterprise Edition

We are using full text search in SQL Server 2005 Enterprise Edition

We are performing a test with full text search in SQL Server 2005 Enterprise Edition

Now if I have a query of the following type:

select TextValue

from dbo.sample_tbl

where contains (TextValue, 'search NEAR Server')

This will return the 2nd and the 3rd rows as both the words are approximately close to each other. The NEAR operator returns those rows which have both the words approximately close to each other. When several proximity terms are chained, all proximity terms must be near each other.

|||

Thanks but the search I have is not working

I have a text string

'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.'

SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');

It returns the row, however we believe it shouldn't

Glyn

Full text search microsoft index server on .NET using C#

Hi,
I used the following code for an index search on specific folder.
{
//create a connection object and command object, to connect the Index
Server
System.Data.OleDb.OleDbConnection odbSearch = new
System.Data.OleDb.OleDbConnection( "Provider=\"MSIDXS\";Data
Source=\"SearchFolder\";");
System.Data.OleDb.OleDbCommand cmdSearch = new
System.Data.OleDb.OleDbCommand();
//assign connection to command object cmdSearch
cmdSearch.Connection = odbSearch;
//Query to search a free text string in the catalog in the contents of
the indexed documents in the catalog
string searchText = txtSearch.Text.Replace("","");
cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
characterization from scope() where FREETEXT(Contents, "+ searchText
+") order by rank desc ";
odbSearch.Open();
I m able to get the TEXT search results. But full text search results
are reqiuired... like suppose if i search for experts release then i
need to get those documents where both the words exists..not only one
word.
Please let me know how to do this on .NET using C#.
Awaiting for the response.
Regards,
Rojasri.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Full-Text-se...ict228634.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=790451
use a contains based search for this.
ie
//Query to search a free text string in the catalog in the contents of
the indexed documents in the catalog
string searchText = txtSearch.Text.Replace("'","''");
cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
characterization from scope() where CONTAINS(Contents, '"+ searchText
+"') order by rank desc ";
BTW - this is an indexing services question. It should be posted in
Microsoft.public.inetserver.indexserver
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
"rojasree" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_790451_42f97e43956785fa721f58da989a05d8@.dbf orumz.com...
> Hi,
> I used the following code for an index search on specific folder.
> {
> //create a connection object and command object, to connect the Index
> Server
> System.Data.OleDb.OleDbConnection odbSearch = new
> System.Data.OleDb.OleDbConnection( "Provider=\"MSIDXS\";Data
> Source=\"SearchFolder\";");
> System.Data.OleDb.OleDbCommand cmdSearch = new
> System.Data.OleDb.OleDbCommand();
> //assign connection to command object cmdSearch
> cmdSearch.Connection = odbSearch;
> //Query to search a free text string in the catalog in the contents of
> the indexed documents in the catalog
> string searchText = txtSearch.Text.Replace("'","''");
> cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
> characterization from scope() where FREETEXT(Contents, '"+ searchText
> +"') order by rank desc ";
> odbSearch.Open();
>
> I m able to get the TEXT search results. But full text search results
> are reqiuired... like suppose if i search for 'experts release' then i
> need to get those documents where both the words exists..not only one
> word.
> Please let me know how to do this on .NET using C#.
> Awaiting for the response.
> Regards,
> Rojasri.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Full-Text-se...ict228634.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=790451
sql

Full Text Search Index build is running too long

I did a DTS insert from a .CSV file and loaded 10 million records into a
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.

Full text search help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full Text Search - How do I best update Index - Merge Replication

Hi,
How do I best update a Full Text Index on SQL2000 sp3, for Merge Replication
(Push Subscription)?
Here is the config:
Site 1:
I have one table with one column (ntext), which I have created a full text
index.
The table updated once per day, data is never updated, always insert new
rows.
The table is Read-Only.
Everyone in Site 1 is happy... full text working good.
Site2: - Branch Office
I have created a merge replication, (push subscription on Site1):
We now have a copy of this read-only database/table in site #2.
I created a full text index on it, this is also working good.
Now after 24 hours, Site 1 gets 1000 new rows of data.
Since I have a push subscription, these new 1000 rows of data will get
pushed to site 2.
How do I automatic the full text index so it will add these new rows (column
data) to the full text index?
Once again, these are not updates, always new rows of data.
I would like to avoid using the Full Text Scheduler.
If I configured a Trigger on Site 2, how would it know when the 1000 rows
where finished inserting rows?
Thanks
Russell Mangel
Las Vegas, NV
use change tracking with update index in background on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I best update a Full Text Index on SQL2000 sp3, for Merge
Replication
> (Push Subscription)?
> Here is the config:
> Site 1:
> I have one table with one column (ntext), which I have created a full text
> index.
> The table updated once per day, data is never updated, always insert new
> rows.
> The table is Read-Only.
> Everyone in Site 1 is happy... full text working good.
> Site2: - Branch Office
> I have created a merge replication, (push subscription on Site1):
> We now have a copy of this read-only database/table in site #2.
> I created a full text index on it, this is also working good.
> Now after 24 hours, Site 1 gets 1000 new rows of data.
> Since I have a push subscription, these new 1000 rows of data will get
> pushed to site 2.
> How do I automatic the full text index so it will add these new rows
(column
> data) to the full text index?
> Once again, these are not updates, always new rows of data.
> I would like to avoid using the Full Text Scheduler.
> If I configured a Trigger on Site 2, how would it know when the 1000 rows
> where finished inserting rows?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>
>
|||Thanks for your reply, I will try this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ub1IZWPeEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> use change tracking with update index in background on the subscriber.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Russell Mangel" <russell@.tymer.net> wrote in message
> news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Replication
text[vbcol=seagreen]
> (column
rows
>

Full Text Search - Conatins doesnot work

Our SQL Server 2000 (sp1) on a Windows 2000 system is configured for a full text search.

The ntext column having a full text index has a value as follows:

Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home

when I search it by

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Away"')

it displays the result as

NTEXT_COL
======================================
Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home

But the query

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Aw*"')

do not return any rows..

Can anybody give me the reason for this behaviour, or is it a known bug?

Thanks

Sajan'Get' is included in Noise-word file and therefore query does not return what do you expect. Just remove hyphen from Get-Away (GetAway), repopulate index, run query and you'll see.

SELECT NTEXT_COL
FROM FULL_TEXT_TABLE
WHERE CONTAINS(NTEXT_COL, N'"Tropical GetAw*"')

I agree it is stupid but I guess it was designed this way. You could update list of your Noise words but size of indexes will be increased...

Full Text Search - Auto Change Tracking

Id like to know a bit about the behaviour of CHANGE_TRACKING AUTO, specifically when the update of the index takes place. In the docs it states 'these changes may not be reflected immediately' but in general when can the changes be expected to appear. Is there merit to updating manually, although the user needs ALTER permission on the table in that case vs. letting SQL Server run it automatically.

Thanks for any help - MikeIn case you or anyone else has found any relevant information about this topic I'd also be interested to hear about it since the documentation doesn't really clear things up.

kind regards,
heinz

Full Text Search - Auto Change Tracking

Id like to know a bit about the behaviour of CHANGE_TRACKING AUTO, specifically when the update of the index takes place. In the docs it states 'these changes may not be reflected immediately' but in general when can the changes be expected to appear. Is there merit to updating manually, although the user needs ALTER permission on the table in that case vs. letting SQL Server run it automatically.

Thanks for any help - Mike
In case you or anyone else has found any relevant information about this topic I'd also be interested to hear about it since the documentation doesn't really clear things up.

kind regards,
heinz

Full text search

Hi everyone

I am using SQL server 2000 with SP3 on Windows 2003 server.

I have scheduled a job to populate a full text index incrementally. Everytime it runs the job to populate the index table it writes some messages into event log.
Like this:
1.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3019
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:1
The crawl on project <SQL Server SQL00002000009> has started.

2.
Event Type : Information
Event Source : Microsoft Search
Event Category: Gatherer
Event ID : 3047
Date :8/7/2004
Time :2:21:00 PM
User : N/A
Computer : ARC-03-DEV
Description:
The end of the incremental crawl for project <SQL Server SQL00002000009> has been detected. The Gatherer successfully processed 998 documents totaling 0K. It failed to filter 0 documents. 998 documents were modified. 0 URLs could not be reached or were denied access.

The event log is getting filled frequently.

Is there any way to stop this messages?
It is normal for Microsoft Search to write this message into EventLog?
Even though there is no chanage in the database it give the above messages.

Any one know the reason please help.

Quick reply is appreaciated.

Reagrda,

Atiq RahmanYes, there is a way to suppress the message, but it is a bad idea so I don't remember the details.

Yes, it is perfectly normal for the indexing service to issue those messages, even if it doesn't make any changes.

-PatP|||Hi Pat,

Thanks for the information.

I want to stop this entries getting into Eventlog. Even if it is bad, if you remember please let me know. I want to try it.

Regards,

Atiq Rahman

Full Text Query Starts Slow

I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.

The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.

Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.

I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.

I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.

Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.

In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql

Full Text Query Problem

Hi
We have a full text index on tblPages, the two fields that we wish to search
for 'server' are Symtoms and Whilst.
The problem is that we were running the join in the WHERE clause, but we
were not getting distinct results where 'server' was in both symtoms and
whilst.
So we have changed the join to an inner join, the only problem is that with
the current setup it is joining tblPages > A > B where as we want:
tblPages > A
> B
Any advice would be much appreciated.
Thanks
B
SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
tblPages.Whilst, A.RANK, B.RANK
from tblPages AS tblPages
INNER JOIN containstable(tblPages, Symptoms, 'server') as A on
A.[KEY] = tblPages.PageRef
INNER JOIN containstable(tblPages, Whilst, 'server') as B ON B.[KEY]
= tblPages.PageRef"Ben" <Ben@.NoSpam.com> wrote in message
news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a full text index on tblPages, the two fields that we wish to
> search for 'server' are Symtoms and Whilst.
> The problem is that we were running the join in the WHERE clause, but we
> were not getting distinct results where 'server' was in both symtoms and
> whilst.
> So we have changed the join to an inner join, the only problem is that
> with the current setup it is joining tblPages > A > B where as we want:
> tblPages > A
> Any advice would be much appreciated.
> Thanks
> B
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> INNER JOIN containstable(tblPages, Symptoms, 'server') as A on
> A.[KEY] = tblPages.PageRef
> INNER JOIN containstable(tblPages, Whilst, 'server') as B ON
> B.[KEY] = tblPages.PageRef
DISTINCT works across all columns, you can't specify it to only be for one
column, so those parentheses around pageref actually do nothing and are
ignored by the parser. If you have non-unique pageref values in the results
then you need to use a GROUP BY clause on pageref and summation functions on
each row (eg. min, max, avg, etc) to tell SQL Server what you want returned
for each unique pageref value. However, if pageref is the primary key then
you will not need to do this using the following.
Anyway, onto the join. By specifying inner for both A and B you are limiting
the results to only those rows that have the term in both Whilst and
Symptoms. From the wording of your post you actually want rows that are in
either, for this you can use left outer joins.
SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
tblPages.Whilst, A.RANK, B.RANK
from tblPages AS tblPages
LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
tblPages.PageRef = A.[KEY]
LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
tblPages.PageRef = B.[KEY]
where A.[KEY] is not null OR B.[KEY] is not null
This will return all rows from tblPages where there is a match in either A
or B, or both.
You can also use EXISTS to give the same results, although I'm not sure on
the performance difference. I've read that EXISTS might be quicker on tables
with a large number of rows as rather than joining all rows to determine
which should be returned an index lookup can be performed to determine if a
row matches. I've run some tests on my own data and so far I've found left
joins and exists to be similar in performance, but on your data you may well
get different results.
Dan|||Thanks Dan
Thats ideal
B
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> DISTINCT works across all columns, you can't specify it to only be for one
> column, so those parentheses around pageref actually do nothing and are
> ignored by the parser. If you have non-unique pageref values in the
> results then you need to use a GROUP BY clause on pageref and summation
> functions on each row (eg. min, max, avg, etc) to tell SQL Server what you
> want returned for each unique pageref value. However, if pageref is the
> primary key then you will not need to do this using the following.
> Anyway, onto the join. By specifying inner for both A and B you are
> limiting the results to only those rows that have the term in both Whilst
> and Symptoms. From the wording of your post you actually want rows that
> are in either, for this you can use left outer joins.
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
> tblPages.PageRef = A.[KEY]
> LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
> tblPages.PageRef = B.[KEY]
> where A.[KEY] is not null OR B.[KEY] is not null
> This will return all rows from tblPages where there is a match in either A
> or B, or both.
> You can also use EXISTS to give the same results, although I'm not sure on
> the performance difference. I've read that EXISTS might be quicker on
> tables with a large number of rows as rather than joining all rows to
> determine which should be returned an index lookup can be performed to
> determine if a row matches. I've run some tests on my own data and so far
> I've found left joins and exists to be similar in performance, but on your
> data you may well get different results.
> Dan
>|||Hi Dan
Sorry, one more question, a little different this time.
The Containstable function works fine with single words, but problems occur
as I will allow users to add free text.
When there is more than one word then AND, OR, AND NOT is needed.
I can do some basic checking such as replacing double spaces and spaces with
AND (or OR), but if they enter a comma etc more problems occur.
Is there any solid method of doing this or should I use basic
checking/replace statments?
THanks
B
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:Ou7X2thUFHA.2420@.TK2MSFTNGP12.phx.gbl...
> DISTINCT works across all columns, you can't specify it to only be for one
> column, so those parentheses around pageref actually do nothing and are
> ignored by the parser. If you have non-unique pageref values in the
> results then you need to use a GROUP BY clause on pageref and summation
> functions on each row (eg. min, max, avg, etc) to tell SQL Server what you
> want returned for each unique pageref value. However, if pageref is the
> primary key then you will not need to do this using the following.
> Anyway, onto the join. By specifying inner for both A and B you are
> limiting the results to only those rows that have the term in both Whilst
> and Symptoms. From the wording of your post you actually want rows that
> are in either, for this you can use left outer joins.
> SELECT distinct(pageref), tblPages.Location, tblPages.Symptoms,
> tblPages.Whilst, A.RANK, B.RANK
> from tblPages AS tblPages
> LEFT OUTER JOIN containstable(tblPages, Symptoms, 'server') as A ON
> tblPages.PageRef = A.[KEY]
> LEFT OUTER JOIN containstable(tblPages, Whilst, 'server') as B ON
> tblPages.PageRef = B.[KEY]
> where A.[KEY] is not null OR B.[KEY] is not null
> This will return all rows from tblPages where there is a match in either A
> or B, or both.
> You can also use EXISTS to give the same results, although I'm not sure on
> the performance difference. I've read that EXISTS might be quicker on
> tables with a large number of rows as rather than joining all rows to
> determine which should be returned an index lookup can be performed to
> determine if a row matches. I've run some tests on my own data and so far
> I've found left joins and exists to be similar in performance, but on your
> data you may well get different results.
> Dan
>|||"Ben" <Ben@.NoSpam.com> wrote in message
news:eyPByIjUFHA.2540@.tk2msftngp13.phx.gbl...
> Hi Dan
> Sorry, one more question, a little different this time.
> The Containstable function works fine with single words, but problems
> occur as I will allow users to add free text.
> When there is more than one word then AND, OR, AND NOT is needed.
> I can do some basic checking such as replacing double spaces and spaces
> with AND (or OR), but if they enter a comma etc more problems occur.
> Is there any solid method of doing this or should I use basic
> checking/replace statments?
There have been lots of posts in microsoft.public.sqlserver.fulltext with
solutions to this, many using regular expressions to quickly create clauses.
I myself use a lump of code I wrote about 10 years ago which deals with this
and parentheses and quoted phrases, but it's messy and I'd rather clean it
up before posting.
A search on google groups for fulltext parsing should pull up some useful
info, such as
http://groups.google.co.uk/group/mi...fulltext&hl=en
Dan
Dan|||Ben,
This is a somewhat common question in the fulltext newsgroup (I post replies
there often), and you may benefit from the following KB article: 246800
(Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
You may want to alter the below Northwind FTS query to fit your environment,
as it handles FTS across (AND) columns:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'BA') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that you can alter the AND between the KEY columns to an OR condition
and then use distinct in your select list.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ben" <Ben@.NoSpam.com> wrote in message
news:eyPByIjUFHA.2540@.tk2msftngp13.phx.gbl...
> Hi Dan
> Sorry, one more question, a little different this time.
> The Containstable function works fine with single words, but problems
occur
> as I will allow users to add free text.
> When there is more than one word then AND, OR, AND NOT is needed.
> I can do some basic checking such as replacing double spaces and spaces
with
> AND (or OR), but if they enter a comma etc more problems occur.
> Is there any solid method of doing this or should I use basic
> checking/replace statments?
> THanks
> B
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23wzMMIiUFHA.1044@.TK2MSFTNGP10.phx.gbl...
we
and
one
you
Whilst
ON
A
on
far
your
>

Full text manual, where are pending changes stored

Created a full text index on table met_db_prcd. This table has an id of 658101385.

CREATE FULLTEXT INDEX ON met_db_prcd(db_prcd_dsc, db_prcd_nm)
KEY INDEX Xmet_db_prcd2 on EMART_MET
WITH CHANGE_TRACKING MANUAL

I can see that I have pending changes by running the following command:

sp_fulltext_pendingchanges 658101385

1. Where are the pending changes stored?

In SQL Server 2005 the changes are stored in an Internal Map Table,unlike SQL Server 2000 which had a table sysfulltextnotify table. The stored procedure sp_fulltext_pendingchanges uses the underlining Map table to get relevent information

HTH

Vishal

|||Do you know what the name of the internal map table?