Document stored as image, problem with inflectional search

Hello,
I'm storing documents (txt, html, etc) as images in my database, and can
run regular 'contains' searches on my data just fine, but when I use
formsof(inflectional,word), it will only return results that contain the
word exactly, not any inflectional forms of it.
Is this some known issue about the way indexing is done or how SQL
processes images? Or could something be wrong on my end?
My @.@.version is:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks!
- Parhaum
Parhaum,
First of all, thank you for providing the @.@.version info as this is most
important in troubleshooting SQL FTS issues such as this one! Secondly, can
you post the exact formsof(inflectional,word) query & word or phrase that
you are having problems with along with the output of the following SQL
code?
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
There are several known words when used with the US English wordbreaker that
do not generate the correct or expected inflectional, see this thread
(http://www.webservertalk.com/archive.../t-965368.html) for more details.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Parhaum Toofanian via droptable.com" <forum@.nospam.droptable.com> wrote
in message news:141e567797a94b829e022eb7c3eb7ca2@.droptable.co m...
> Hello,
> I'm storing documents (txt, html, etc) as images in my database, and can
> run regular 'contains' searches on my data just fine, but when I use
> formsof(inflectional,word), it will only return results that contain the
> word exactly, not any inflectional forms of it.
> Is this some known issue about the way indexing is done or how SQL
> processes images? Or could something be wrong on my end?
> My @.@.version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> Thanks!
> - Parhaum
|||did you use the ms.locale metatag to indicate the type of language your html
docs are stored in?
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
"Parhaum Toofanian via droptable.com" <forum@.nospam.droptable.com> wrote
in message news:141e567797a94b829e022eb7c3eb7ca2@.droptable.co m...
> Hello,
> I'm storing documents (txt, html, etc) as images in my database, and can
> run regular 'contains' searches on my data just fine, but when I use
> formsof(inflectional,word), it will only return results that contain the
> word exactly, not any inflectional forms of it.
> Is this some known issue about the way indexing is done or how SQL
> processes images? Or could something be wrong on my end?
> My @.@.version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> Thanks!
> - Parhaum
|||First of all, thank you both for your quick replies! Regarding the locale,
I can probably check, but it's a database in my company that I'm developing
with, so I'd need to ask the DBA about it.
Second, background on the database. The table with the image data is
J_DOCU_CONTENT, and I have uploaded many files including 3 sample .txt
files with text like "park services" and "servicing the shuttle". There's
a table called T_DOCUMENT that contains some other information, but even
running the last query without the T_DOCUMENT information returns the same
result.
Trying to do an inflectional search on "service" returns 0 results. An
inflectional search on "services" returns the park services file, and so
on, as if it were a 'contains' exact match search.
:: Result of:
:: EXEC sp_help_fulltext_columns;
TABLE_OWNER<x>
TABLE_ID<id>
TABLE_NAMEJ_DOCU_CONTENT
FULLTEXT_COLUMN_NAMEDATA
FULLTEXT_COLID4
FULLTEXT_BLOBTP_COLNAMEDOCUMENT_TYPE
FULLTEXT_BLOBTP_COLID3
FULLTEXT_LANGUAGE0
:: Result of:
:: EXEC sp_help J_DOCU_CONTENT;
NameJ_DOCU_CONTENT
Owner<x>
Typeuser table
Created_datetime2005-04-14 15:38:09.013
Column_namePRIMARY_KEY
Typeint
Computedno
Length4
Prec10
Scale0
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
Column_nameVERSION
Typeint
Computedno
Length4
Prec10
Scale0
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
Column_nameDOCUMENT_TYPE
Typevarchar
Computedno
Length250
Prec
Scale
Nullableno
TrimTrailingBlanksno
FixedLenNullInSourceno
CollationSQL_Latin1_General_CP1_CI_AS
Column_nameDATA
Typeimage
Computedno
Length7000
Prec
Scale
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
No identity column defined.
No rowguidcol column defined.
constraint_type
PRIMARY KEY (clustered)
:: Result of:
:: SELECT t1.NAME FROM J_DOCU_CONTENT t0, T_DOCUMENT t1 WHERE
::(contains(t0.DATA, ' FORMSOF(INFLECTIONAL, service) ')
::AND
::(t0.PRIMARY_KEY = t1.CONTENT_ID))
NAME
(0 row(s) affected)
Message posted via http://www.droptable.com
|||Yikes. Tried to format it from the direct SQL output, and not sure now
which ended up looking worse...
Message posted via http://www.droptable.com

Do you regularly rebooting your server?

I would like to know if you reboot your SQL Server on a regular basis and why...

I do not regularly reboot our SQL Servers. They do get reboots when patches adn service packs are applied, but past that, I have found no need to reboot the servers.
|||Not by choice but monthly due to patches|||No. Only by office power cutoff (occurs time to time).|||

Never :-)

|||

If you don′t wanna have your data cache and your execution plans dropped every time you boot up the server, don′t do it. :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||If there weren't so dang many OS security patches, we would choose never. But, our company has opted for applying patches every other month. Many of our servers are using WSUS and unattended installs/reboots. We've had no troubles with this.
|||Come on, don't be shy, no one will judge you here. Or you could email me at dr_remove_sql@.hotmail.com if you don't want to tell the world.|||

Louis Davidson - SQL Server MVP wrote:

I would like to know if you reboot your SQL Server on a regular basis and why...

Funny you should ask...

Actually about a week ago our SQL 2005 server began to require a reboot about once per day. I'm no DB engineer or sys engineer so I can't tell you why. Our co-host is looking at he issue for us. I can tell you that there did not seem to be any irregularities in the logs and there's no storage issues. The websites just go down because the service fails, and I reboot it.

Drew

|||

We have our SQL server Servers rebooted every weekend if possible.

Why :

Patches

Some of our older SQL Servers are still on NT and SQL7 due to legacy application.

Development servers run applications that can have memory leaks.

If problems occure on reboot you will have a datum from when the server last rebooted successfully. Although servers are secured you can never be sure that someone has not been installing and not finished of with a reboot to ensure all that was working still works and no other errors have been introduced.

Prevents the 'worry' when a reboot is required and no ones done one for ages - we get this 'worry' with the unix server guys mostly. Rebooting apparently once introduced disk error problems due to power off on.

Cluster Servers - when failing over manually and rebooting the target before the switch ensures the servers are fully functioning and able to reboot. We have had problems with reboots in the past. -normally down to changes that have been made.

|||Hi, everybody.

Opened up a poll for this thread on http://www.sqlserver2005.de/Polls

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Awesome. Can't wait to see the results :)

Do you regularly reboot your server? (funny you should ask)

I would like to know if you reboot your SQL Server on a regular basis and why...

I do not regularly reboot our SQL Servers. They do get reboots when patches adn service packs are applied, but past that, I have found no need to reboot the servers.
|||Not by choice but monthly due to patches|||No. Only by office power cutoff (occurs time to time).|||

Never :-)

|||

If you don′t wanna have your data cache and your execution plans dropped every time you boot up the server, don′t do it. :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||If there weren't so dang many OS security patches, we would choose never. But, our company has opted for applying patches every other month. Many of our servers are using WSUS and unattended installs/reboots. We've had no troubles with this.
|||Come on, don't be shy, no one will judge you here. Or you could email me at dr_remove_sql@.hotmail.com if you don't want to tell the world.|||

Louis Davidson - SQL Server MVP wrote:

I would like to know if you reboot your SQL Server on a regular basis and why...

Funny you should ask...

Actually about a week ago our SQL 2005 server began to require a reboot about once per day. I'm no DB engineer or sys engineer so I can't tell you why. Our co-host is looking at he issue for us. I can tell you that there did not seem to be any irregularities in the logs and there's no storage issues. The websites just go down because the service fails, and I reboot it.

Drew

|||

We have our SQL server Servers rebooted every weekend if possible.

Why :

Patches

Some of our older SQL Servers are still on NT and SQL7 due to legacy application.

Development servers run applications that can have memory leaks.

If problems occure on reboot you will have a datum from when the server last rebooted successfully. Although servers are secured you can never be sure that someone has not been installing and not finished of with a reboot to ensure all that was working still works and no other errors have been introduced.

Prevents the 'worry' when a reboot is required and no ones done one for ages - we get this 'worry' with the unix server guys mostly. Rebooting apparently once introduced disk error problems due to power off on.

Cluster Servers - when failing over manually and rebooting the target before the switch ensures the servers are fully functioning and able to reboot. We have had problems with reboots in the past. -normally down to changes that have been made.

|||Hi, everybody.

Opened up a poll for this thread on http://www.sqlserver2005.de/Polls

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Awesome. Can't wait to see the results :)

Do you regularly reboot your server?

I would like to know if you reboot your SQL Server on a regular basis and why...

I do not regularly reboot our SQL Servers. They do get reboots when patches adn service packs are applied, but past that, I have found no need to reboot the servers.
|||Not by choice but monthly due to patches|||No. Only by office power cutoff (occurs time to time).|||

Never :-)

|||

If you don′t wanna have your data cache and your execution plans dropped every time you boot up the server, don′t do it. :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||If there weren't so dang many OS security patches, we would choose never. But, our company has opted for applying patches every other month. Many of our servers are using WSUS and unattended installs/reboots. We've had no troubles with this.
|||Come on, don't be shy, no one will judge you here. Or you could email me at dr_remove_sql@.hotmail.com if you don't want to tell the world.|||

Louis Davidson - SQL Server MVP wrote:

I would like to know if you reboot your SQL Server on a regular basis and why...

Funny you should ask...

Actually about a week ago our SQL 2005 server began to require a reboot about once per day. I'm no DB engineer or sys engineer so I can't tell you why. Our co-host is looking at he issue for us. I can tell you that there did not seem to be any irregularities in the logs and there's no storage issues. The websites just go down because the service fails, and I reboot it.

Drew

|||

We have our SQL server Servers rebooted every weekend if possible.

Why :

Patches

Some of our older SQL Servers are still on NT and SQL7 due to legacy application.

Development servers run applications that can have memory leaks.

If problems occure on reboot you will have a datum from when the server last rebooted successfully. Although servers are secured you can never be sure that someone has not been installing and not finished of with a reboot to ensure all that was working still works and no other errors have been introduced.

Prevents the 'worry' when a reboot is required and no ones done one for ages - we get this 'worry' with the unix server guys mostly. Rebooting apparently once introduced disk error problems due to power off on.

Cluster Servers - when failing over manually and rebooting the target before the switch ensures the servers are fully functioning and able to reboot. We have had problems with reboots in the past. -normally down to changes that have been made.

|||Hi, everybody.

Opened up a poll for this thread on http://www.sqlserver2005.de/Polls

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Awesome. Can't wait to see the results :)