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 SqlDataReader is returning an int, when it should be returning a tinyint

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:

SELECT TreatmentStatusFROM vwReferralWithAdmissionDischarge
WHERE ClientNumber = 138238AND CaseNumber = 1AND ProviderNumber = 89

The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.

Why?

If you are just reteieving one value you might want to use ExecuteScalar which is faster and has less overhead than ExecuteReader.

|||

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

|||

humormuch:

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

I forgot to mention, in my original post, that I was using the GetByte() method of the SqlDataReader object, but that raised the following error message:

"Specified cast is not valid"

That's why I brought up the whole thing about knowing that the column was atinyint, but that the SqlDataReader in my ASP.NET 2.0 page is returning aint value instead, which I simply donot understand. Here is the relevant code snippet:

Dim sbAs StringBuilder =New StringBuilder("SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge ")sb.Append(String.Format("WHERE ClientNumber = {0} ", lClientNumber))sb.Append(String.Format("AND CaseNumber = {0} ", byCaseNumber))sb.Append(String.Format("AND ProviderNumber = {0}", nProviderNumber))Dim cmCheckTreatmentStatusAs SqlCommand =New SqlCommand(sb.ToString(), cn)cmCheckTreatmentStatus.CommandType = CommandType.TextDim sdrCheckTreatmentStatusAs SqlDataReader = cm.ExecuteReader()sdrCheckTreatmentStatus.Read()If sdrCheckTreatmentStatus.IsDBNull(0)Then'NOOPElseDim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)'other stuff occurs hereEnd If

It is the line "Dim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)" which is raising the error.

|||

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

|||

DoctorWho:

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

No problem. I remember I have done that quite a few times...