AARGGHH Fulltext indexing woes...

We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
------
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
------
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK = dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK = dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK = dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied = (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ------
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ------
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK => dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK => dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK => dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied => (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
>
>
>|||Hmmm...we found changing the freetext(tblStock.* to be owner qualified
fixed the problem....
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23XNPPrXlGHA.3776@.TK2MSFTNGP03.phx.gbl...
> I've discovered that the joins are what cause this. As soon as I add any
> join to the simple query below that is working, it then fails.
> HELP!!! Why is this?
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>> We're feverishly in QA mode here getting ready to rollout production to
>> SQL2K5...
>> There is this one query which is returning the following error:
>> Msg 7617, Level 16, State 1, Line 1
>> Query does not reference the full-text indexed table or indexed view.
>> I'm really at a lost here. What is wrong with this first query'
>> I don't actually do select tblStock.* in the production version but it
>> succeeds/fails consistently either way so to shorten it up....
>> Here is the DDL used to create the catalog...
>> ------
>> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
>> AUTHORIZATION dbo;
>> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
>> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
>> ALTER FULLTEXT INDEX ON tblStock ENABLE;
>> ------
>> Here is the query that does NOT work (followed by one that does...)
>>
>> SELECT DISTINCT tblStock.*
>> FROM dbo.tblStock LEFT OUTER JOIN
>> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =>> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
>> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =>> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
>> dbo.tblCategories ON
>> dbo.tblCategorizedProducts.lngCategoryFK =>> dbo.tblCategories.lngCategoryPK LEFT OUTER JOIN
>> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =>> dbo.tblToolTypes.lngToolTypePK
>> WHERE (dbo.tblPriceFile.dtmDateApplied =>> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
>> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
>> (dbo.tblStock.blnActiveItem = 1)
>> and freetext(tblStock.*,'marriage')
>> ----
>> (the next one works)
>> select distinct * from tblStock where freetext(tblStock.*,'marriage')
>> ----
>>
>>
>sql

A warm-standby database is read-only

Does anyone out there know how to get a database out of
warm-standby mode so it's not read-only and I can shrink
the database?
Thanks,
DonnaUSE master
EXEC sp_dboption 'pubs', 'read only', 'FALSE'
hth
Eric
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:1076d01c3be99$fa79b5e0$a601280a@.phx.gbl...
> Does anyone out there know how to get a database out of
> warm-standby mode so it's not read-only and I can shrink
> the database?
> Thanks,
> Donna|||RESTORE DATABASE dbname WITH RECOVERY
But you won't be able to restore anymore transaction log or diff backups
after that.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Donna" <anonymous@.discussions.microsoft.com> wrote in message
news:1076d01c3be99$fa79b5e0$a601280a@.phx.gbl...
> Does anyone out there know how to get a database out of
> warm-standby mode so it's not read-only and I can shrink
> the database?
> Thanks,
> Donna

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
Simon
Simon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.

> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
SimonSimon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft® SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon|||> I'm guessing you might use it just before doing a large data import, but does that mean you might
> switch to it before the import and switch back to full recovery once it's done? Is that a good
> idea?
Yes, this brings you the advantage of being in contold of when log backups which includes both log
recards and datapages can occur. Run in full normally, swith down the bulk logged only for this
batch and then up to full again. You are now protected against someone doing SELECT INTO at 3:15 pm
resuting in not being able to do point in time restore for the following log backup (just as an
example).
My take on this is that I weigh what bulk logged will atcually contribute. Will the operation be
faster (compared to full) to warrant bulk logged? And/or the fact that ldf file is smaller, is it
worth it? this need to be compared to the disadvantages (no point in time restore for such log
backup, and not being able to do log backup if for instance mdf file is gone). Also, you need to
think about how larger the following log backup is compared to run in full mode (either fewer log
records but also data pages, or only log records, but more of them).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the bulk logged recovery mode
> is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but does that mean you might
> switch to it before the import and switch back to full recovery once it's done? Is that a good
> idea?
> What other instances might it be used? Perhaps in a reporting database for some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon

A question on when to use bulk logged recovery mode

Hi all,
I was hoping that someone could tell me a couple of scenarios where the
bulk logged recovery mode is used and perhaps why?
I'm guessing you might use it just before doing a large data import, but
does that mean you might switch to it before the import and switch back
to full recovery once it's done? Is that a good idea?
What other instances might it be used? Perhaps in a reporting database
for some reason?
Many thanks to anyone who can advise
Kindest Regards
SimonSimon
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
BOL says
In Microsoft SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.

> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
There are some operatuions like SELECT INTO..,CREATE INDEX under BULK
logged recovery mode are minimally loggged.
A disadvantage is that you cannot restore LOG file at point of time
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the
> bulk logged recovery mode is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but
> does that mean you might switch to it before the import and switch back to
> full recovery once it's done? Is that a good idea?
> What other instances might it be used? Perhaps in a reporting database for
> some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon|||> I'm guessing you might use it just before doing a large data import, but does that mean yo
u might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
Yes, this brings you the advantage of being in contold of when log backups w
hich includes both log
recards and datapages can occur. Run in full normally, swith down the bulk l
ogged only for this
batch and then up to full again. You are now protected against someone doing
SELECT INTO at 3:15 pm
resuting in not being able to do point in time restore for the following log
backup (just as an
example).
My take on this is that I weigh what bulk logged will atcually contribute. W
ill the operation be
faster (compared to full) to warrant bulk logged? And/or the fact that ldf f
ile is smaller, is it
worth it? this need to be compared to the disadvantages (no point in time re
store for such log
backup, and not being able to do log backup if for instance mdf file is gone
). Also, you need to
think about how larger the following log backup is compared to run in full m
ode (either fewer log
records but also data pages, or only log records, but more of them).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:uz%23a2I2HHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I was hoping that someone could tell me a couple of scenarios where the bu
lk logged recovery mode
> is used and perhaps why?
> I'm guessing you might use it just before doing a large data import, but d
oes that mean you might
> switch to it before the import and switch back to full recovery once it's
done? Is that a good
> idea?
> What other instances might it be used? Perhaps in a reporting database for
some reason?
> Many thanks to anyone who can advise
> Kindest Regards
> Simon