something stupid. However, everything I've read so far contradicts what I'm
seeing. Here's the issue;
I setup an FTS index in the Adventureworks DB using the Address1 field in
the Address table to query against some sample code I found. I've set up
this index every possible way (auto track, manual track, etc.), but I cannot
get this index to repopulate in realtime consistantly. In other words, if I
go into the table and change a row in the Address table, the index does not
change most of the time. I've seen it change sometimes but I can't find a
pattern for why that happens.
I've used SSMS exclusively to enable FTS on the database and table as well
as defining the index itself. After the change is made to the tables data,
I run the query to search for matches and the results do not reflect the
change I just made. I tried to "force" it by using the SMSS menus and doing
a "Start Incremental Population", nothing happens. I've tried doing a Start
Full Population", and nothing happens UNLESS I have previously set the "Track
Changes" option to OFF during setup. In this one situation I can manually
force the index to rebuild by having Track Changes Automatically turned off
and then executing a "Start Full Population". Of course, this is not a
solution. I need this index to stay current on its own.
What am I doing wrong? Am I missing something fundamental? Is there some
good reading someone can point me to?
Maz
Sorry, I think there's some more info I should mention so no one goes down
the wrong road;
1) I'm using SQl 2005 Standard Edition
2) I've checked teh Crawl Logs and they show nothing around the time I make
the changes. I do see an entry when I do a Full index build. It shows 19000
records indexed with 0 errors.
3) The index seems to be updated when I change the row the first time,
however, if I try to change it again (and put the keyword in the row again)
the index doesn't seem to be updated. This means, that row displays with the
changed word, but, it's ranking still reflects the row before the change.
i.e. the index didn't get updated.
4) The query I'm executing to do the search and rank is as follows;
USE AdventureWorks
GO
SELECT top 100 AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
Street WEIGHT(0.9),
View WEIGHT(0.1)
) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
Maz
"Spicy Mikey" wrote:
> I have to start by saying I'm new to FTS in SQL Server and could be doing
> something stupid. However, everything I've read so far contradicts what I'm
> seeing. Here's the issue;
> I setup an FTS index in the Adventureworks DB using the Address1 field in
> the Address table to query against some sample code I found. I've set up
> this index every possible way (auto track, manual track, etc.), but I cannot
> get this index to repopulate in realtime consistantly. In other words, if I
> go into the table and change a row in the Address table, the index does not
> change most of the time. I've seen it change sometimes but I can't find a
> pattern for why that happens.
> I've used SSMS exclusively to enable FTS on the database and table as well
> as defining the index itself. After the change is made to the tables data,
> I run the query to search for matches and the results do not reflect the
> change I just made. I tried to "force" it by using the SMSS menus and doing
> a "Start Incremental Population", nothing happens. I've tried doing a Start
> Full Population", and nothing happens UNLESS I have previously set the "Track
> Changes" option to OFF during setup. In this one situation I can manually
> force the index to rebuild by having Track Changes Automatically turned off
> and then executing a "Start Full Population". Of course, this is not a
> solution. I need this index to stay current on its own.
> What am I doing wrong? Am I missing something fundamental? Is there some
> good reading someone can point me to?
>
> --
> Maz
|||Use change tracking. In SQL 2005 SP 1 a population is not automatically set
off after you create your full-text index the way it would occur in RTM. You
need to enable the fulltext index, and you may need to start the full
population. Then change tracking should kick 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
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...[vbcol=seagreen]
> Sorry, I think there's some more info I should mention so no one goes down
> the wrong road;
> 1) I'm using SQl 2005 Standard Edition
> 2) I've checked teh Crawl Logs and they show nothing around the time I
> make
> the changes. I do see an entry when I do a Full index build. It shows
> 19000
> records indexed with 0 errors.
> 3) The index seems to be updated when I change the row the first time,
> however, if I try to change it again (and put the keyword in the row
> again)
> the index doesn't seem to be updated. This means, that row displays with
> the
> changed word, but, it's ranking still reflects the row before the change.
> i.e. the index didn't get updated.
> 4) The query I'm executing to do the search and rank is as follows;
> USE AdventureWorks
> GO
> SELECT top 100 AddressLine1, KEY_TBL.RANK
> FROM Person.Address AS Address INNER JOIN
> CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",
> Street WEIGHT(0.9),
> View WEIGHT(0.1)
> ) ' ) AS KEY_TBL
> ON Address.AddressID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK desc
> GO
>
> --
> Maz
>
> "Spicy Mikey" wrote:
|||Hi Hilary
Thanks for the quick response. You suggested there are differences in
behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
believe I've installed SP1 yet, but, I will make sure I have SP1 installed so
we're all on the same version. Regardless, can you rephrase and elaborate on
your answer? I'm not sure I understand what you are suggesting I do.
You suggest three things;
1) You say to use change tracking. That is one of the scenerio's I tried
and it didn't seem to help. In 2005 we seem to have three options for Change
tracking -- Auto, Manual, and None. I've tried all three. The documentation
says Auto should provide near realtime updates to the index
2) You say to "enable the fulltext index". I do not see an option for that.
I see how I need to enable full text indexing for the database before
creating a FT index. That has been done already
3) You say to kick off the full index population. Not sure what you mean.
The full population kicks off automatically when you first create the index
(with Auto selected). Also, I've tried to manually perform a Full population
index. That doesn't help
One more thing, the wizard is not able to create it's catalog maintenance
job at the end of the wizard steps. The job gets created but I get an error
40 creating the job step that says Named Pipes cannot connect to the server.
It suggests I may not have remote access turned on. However, I have Named
Pipes and TCP access turned on, and, in fact can connect to the database just
fine with the application software using OLEDB. That doesn't seem to be the
problem. Any suggestions on this? Can the two problems be related? This is
a very vanilla installation of SQL on Windows 2003. I haven't changed
anything yet.
Maz
"Hilary Cotter" wrote:
> Use change tracking. In SQL 2005 SP 1 a population is not automatically set
> off after you create your full-text index the way it would occur in RTM. You
> need to enable the fulltext index, and you may need to start the full
> population. Then change tracking should kick 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
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:7C90F276-A695-4D0D-9DA4-1FD6E7792A22@.microsoft.com...
>
>
|||Yes there are differences. Essentially when you create a full-text index
through the wizard in SQL 2005 RTM it automatically kicks off change
tracking (if you have accepted the defaults). This involves a
full-population after/during which changes are tracked. As customers
complained to MS about the locking which occurred during the population (at
least this is what I assume), SP1 changes this behavior. Full-text indexes
built through the wizard are not enabled, you must enable them and then
start change tracking - which starts the full-population.
I have attempted to answer your questions inline.
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
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Hi Hilary
> Thanks for the quick response. You suggested there are differences in
> behaviour between RTM and SP1. I'm using version 9.00.1399.06. I don't
> believe I've installed SP1 yet, but, I will make sure I have SP1 installed
> so
> we're all on the same version. Regardless, can you rephrase and elaborate
> on
> your answer? I'm not sure I understand what you are suggesting I do.
> You suggest three things;
> 1) You say to use change tracking. That is one of the scenerio's I tried
> and it didn't seem to help. In 2005 we seem to have three options for
> Change
> tracking -- Auto, Manual, and None. I've tried all three. The
> documentation
> says Auto should provide near realtime updates to the index
Use Auto
> 2) You say to "enable the fulltext index". I do not see an option for
> that.
> I see how I need to enable full text indexing for the database before
> creating a FT index. That has been done already
right click on your table, select Full-Text Index and then see if the option
to Enable Full-Text Index is enabled. If not, it probably is already
enabled.
> 3) You say to kick off the full index population. Not sure what you mean.
> The full population kicks off automatically when you first create the
> index
> (with Auto selected). Also, I've tried to manually perform a Full
> population
> index. That doesn't help
>
Yes, with RTM this was the behavior after creating a full-text index.
> One more thing, the wizard is not able to create it's catalog maintenance
> job at the end of the wizard steps. The job gets created but I get an
> error
> 40 creating the job step that says Named Pipes cannot connect to the
> server.
> It suggests I may not have remote access turned on. However, I have Named
> Pipes and TCP access turned on, and, in fact can connect to the database
> just
> fine with the application software using OLEDB. That doesn't seem to be
> the
> problem. Any suggestions on this? Can the two problems be related? This
> is
> a very vanilla installation of SQL on Windows 2003. I haven't changed
> anything yet.
>
I am not sure why this is. It almost sounds like the account you are
creating the full-text index under does not have rights. Are you logged on
as the sa/administrator.[vbcol=seagreen]
> --
> Maz
>
> "Hilary Cotter" wrote:
|||Thanks again Hilary for sticking with me on this and offering more suggestions.
Unfortunately, nothing works. I have a hard time believing this is due to
my own stupidity, yet, I also have a hard time believing that this FTS
feature flat out doesn't work. For my sake, and anyone else following this
thread, can you do me a favor and do a simple test on your end to see if you
can duplicate this behavior?
1) Load the AdventureWorks database, enable FTS for Auto track, and setup an
FTS index on the Address1 field in the Person.Address table
2) Next, execute this query in SMSS
USE AdventureWorks
GO
SELECT AddressLine1, KEY_TBL.RANK
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", Street
WEIGHT(0.9), View WEIGHT(0.1)) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK desc
GO
4) Note the row that gets returned for "1111Bay Street". Open the table and
change that record in the table to be "1111 X Street", then run that query
again. You should see the record excluded from the query's top results.
That's what we expect. That seems to work fine.
5) Now, add the word "Bay" back into that row and run the query a third
time. I expect to see that row reappear in the top results. However, that's
not what happens. The row is way down the list near the bottom indicating to
me that the index did not get the last change and still has that row indexed
with the "X".
I've done this 10 times. I've eliminated every stupid mistake possible.
Either my expectations are wrong OR Microsoft has a serious bug in this
feature.
If this works for you then I AM stupid and I'll just need to open a ticket
with MS and do some one on one troubleshooting with someone in the SQL team.
Thanks for sticking with me!
Mike
"Hilary Cotter" wrote:
> Yes there are differences. Essentially when you create a full-text index
> through the wizard in SQL 2005 RTM it automatically kicks off change
> tracking (if you have accepted the defaults). This involves a
> full-population after/during which changes are tracked. As customers
> complained to MS about the locking which occurred during the population (at
> least this is what I assume), SP1 changes this behavior. Full-text indexes
> built through the wizard are not enabled, you must enable them and then
> start change tracking - which starts the full-population.
> I have attempted to answer your questions inline.
> --
> 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
>
> "Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
> news:1D9391B9-E15F-40C0-851C-2446876B5D85@.microsoft.com...
> Use Auto
>
> right click on your table, select Full-Text Index and then see if the option
> to Enable Full-Text Index is enabled. If not, it probably is already
> enabled.
> Yes, with RTM this was the behavior after creating a full-text index.
> I am not sure why this is. It almost sounds like the account you are
> creating the full-text index under does not have rights. Are you logged on
> as the sa/administrator.
>
>
|||This may sound stupid, but - have you given FTS a couple of seconds to finish
its updates?
ML
http://milambda.blogspot.com/
|||Oh yea, it still hasn't updated, so, I suppose you can say I gave it 3 days
The strange thing is that it only involves adding index words back into the
row. For some reason the index gets updated immediately when I take a key
word away but if I immediately add it back in, the index doesn't reflect that
second change.
I'm not saying it couldn't be something I'm doing wrong, however, assume all
the stupid mistakes have been considered. It's probably something more
complicated then can be addressed on a message board. I'll probably have to
open a ticket with MS. I'll post the answer here for everyone's benefit
Maz
"ML" wrote:
> This may sound stupid, but - have you given FTS a couple of seconds to finish
> its updates?
>
> ML
> --
> http://milambda.blogspot.com/
No comments:
Post a Comment