No data returned - full text search.

Question

No data returned - full text search.

amodi-1156550

Valued Member

Points: 59
More actions
September 12, 2009 at 3:59 am

#209862

Hello friends,
I created a catalog, unique index and full text index as follows
create fulltext catalog ISFullTextCatalogDetails
create unique index ui_PagesMaster on Pages_Master(PageId)
create fulltext index on Pages_Master(Page)
Key Index ui_PagesMaster on ISFullTextCatalogDetails
with CHANGE_TRACKING MANUAL
Note: In the following
create fulltext index on Pages_Master(Page)
Key Index ui_PagesMaster on ISFullTextCatalogDetails
with CHANGE_TRACKING MANUAL
Column "Page"(where i want to search data) in Pages_Master table is of type "varchar(max)"
Every thing works fine, but when i search the data like:
SELECT pageId,Page,fk_pagecode FROM Pages_Master
Where contains (Page, ' "Procedures" ')
It is displaying no data???
Please let me know how can i solve this problem.
Am I missing something?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I am assuming Master Page is not Asp.net Master Page because if it is then it may not be related to SQL Server because all development used by the Master Pages is called in the Content Page within the Master Page or you must inherit from Page to call your query programmatically. The reason Master Pages are in PreINIT in the Http pipeline.

If the above is not the issue then you could drop the index and add it again and enable automatic update of the catalog. If your database is from SQL Server 2000 then you really need to drop the index because in 2000 and below you must populate the fulltext catalog manually.

Kind regards,
Gift Peddie

amodi-1156550 Valued Member Points: 59 More actions · Answer 2

Hello Gift Peddie,

I am testing the full text search on sql server, so i don' t have Master Page - content Page issue.

I just did the following:

create fulltext catalog ISFullTextCatalogDetails

create unique index ui_PagesMaster on Pages_Master(PageId)

create fulltext index on Pages_Master(Page)

Key Index ui_PagesMaster on ISFullTextCatalogDetails

with CHANGE_TRACKING MANUAL

and write a select statement as follows:

SELECT pageId,Page,fk_pagecode

FROM Pages_Master

Where contains (Page, ' "Procedures" ')

No data at all is displayed. ( not even a single row )

I don't know where am i going wrong.

Thanks.

Amodi.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

That could be you are either running SQL Server 2000 database that is migrated with compatibility 80 which means SQL Server 2005 Fulltext catalog must be populated manually or you drop the existing index and add a new one this time click auto population. You must also change the compatibility to 90 or it will not work.

I just looked at your original post your data type is Varchar(max) which means your database is not in compatibility 80 so drop the index and create a new one with auto populate enabled.

Kind regards,
Gift Peddie

amodi-1156550 Valued Member Points: 59 More actions · Answer 4

Hello Girf Peddie,

Thanks for your quick replies. I did not migrate from sql server 2000. i am using sql server 2005 without any migration. As suggested in your post i tried to drop the existing index and add a new one with auto population( even though i am using sql server 2005).

But noting works for me. 🙁

Any other suggestions?

Thanks.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

Then you need to check configuration manager to make sure fulltext service is running, if it is running then try CONTAINSTABLE predicate if that works then it is related to your code.

I think most fulltext related problems comes from cluster installations which comes with different requirements and SQL Server Express only Express advanced comes with Fulltext which must be enabled in configuration manager after installation.

One more thing right click in management studio and use the wizard to create the fulltext index.

http://msdn.microsoft.com/en-us/library/ms187787(SQL.90).aspx

Kind regards,
Gift Peddie

amodi-1156550 Valued Member Points: 59 More actions · Answer 6

Hello Gift Peddie,

Thanks again.

I just change the column type( where i am doing data search) from varchar(max) to varchar(100)

and its working.

Does sql server FTS not support varchar(max)? or

Does sql server FTS have some limit on varchar() character? or

Do i need to change some setting of Sql Server 2005?

Thanks.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

No however Varchar(max) comes with large value types out of row option which is either ON or OFF if OFF then when your data is more than 8000 Fulltext may not see that data so make sure you turn that option to ON in your table in design.

http://technet.microsoft.com/en-us/library/ms189087(SQL.90).aspx

Kind regards,
Gift Peddie

Paul White SSC Guru Points: 150467 More actions · Answer 8

Gift Peddie (9/12/2009)
No however Varchar(max) comes with large value types out of row option which is either ON or OFF if OFF then when your data is more than 8000 Fulltext may not see that data so make sure you turn that option to ON in your table in design.

:blink: Do you have a reference for that behaviour? :blink:

1. Full text search doesn't care how the data is stored.

2. Considering that the data fits in VARCHAR(100) now, it cannot have been > 8000 bytes in the first place.

Specifying MAX means you are letting SQL Server decide whether to store the data as a 'normal' varchar, nvarchar, or varbinary value; or as a LOB:

In general, if the data is 8000 bytes or less, the data will be stored exactly as for the non-LOB type - including possibly overflowing onto one or more ROW_OVERFLOW_DATA allocation units if necessary.

If the actual data length is more than 8000 bytes, SQL Server stores the data exactly as for text, ntext, and image dat types.

References: Inside Microsoft SQL Server 2005: The Storage Engine (K. Delaney 2007), Microsoft SQL Server 2008 Internals (K.Delaney et al. 2009)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

1. Full text search doesn't care how the data is stored.
2. Considering that the data fits in VARCHAR(100) now, it cannot have been > 8000 bytes in the first
place.

The size of the data in the index that worked is a very good point but my statement was related to pre SP2 2005 Nvarchar (max) was almost like NText for comparision without that option. So that is an error.

Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking.

Fulltext does not care how the data is stored so the above is almost a paradox because the fulltext engine does not use changes from these clauses.

http://technet.microsoft.com/en-us/library/ms187317(SQL.90).aspx

Kind regards,
Gift Peddie

Paul White SSC Guru Points: 150467 More actions · Answer 10

Gift Peddie,

Would you like to explain how the fact that the (deprecated since 2005) UPDATETEXT and WRITETEXT statements don't work with AUTO change tracking relates to your statement about the large values types out of row option? 😛

You will recall, of course, that Amodi's previous posts specified WITH CHANGE_TRACKING MANUAL anyway. 😀

You will further recall that UPDATETEXT and WRITETEXT do not feature in any prior posts on this thread.

The link you posted was to the 2008 version of the wrong article.

...my statement was related to pre SP2 2005 Nvarchar (max) was almost like NText for comparision without that option. So that is an error.

I'm afraid I have no idea what you mean by this. Please clarify so I may help you understand your error.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

amodi-1156550 Valued Member Points: 59 More actions · Answer 11

Hello Gift Peddie,

I got it. I works!

Thanks for your patient and solution.

Best regards,

Amodi.

amodi-1156550 Valued Member Points: 59 More actions · Answer 12

Hello friends,

This is a good exposure, while listening to you professionals.

Best Regards,

Amodi.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 13

Paul White (9/13/2009)
Gift Peddie,
Would you like to explain how the fact that the (deprecated since 2005) UPDATETEXT and WRITETEXT statements don't work with AUTO change tracking relates to your statement about the large values types out of row option? 😛
You will recall, of course, that Amodi's previous posts specified WITH CHANGE_TRACKING MANUAL anyway. 😀
You will further recall that UPDATETEXT and WRITETEXT do not feature in any prior posts on this thread.
The link you posted was to the 2008 version of the wrong article.
...my statement was related to pre SP2 2005 Nvarchar (max) was almost like NText for comparision without that option. So that is an error.
I'm afraid I have no idea what you mean by this. Please clarify so I may help you understand your error.
Paul

Will not diginify with an answer.

😉

Kind regards,
Gift Peddie

Paul White SSC Guru Points: 150467 More actions · Answer 14

That is so lame!

Hey look, we all make mistakes - some of us don't mind admitting it though.

*shrug*

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi