AARGGHH Fulltext indexing woes...

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')
> ----
--
>
>
>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')
> ----
--
>
>
>

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...

a good reference for learning differnet Joins in SqlServer 200

Is there a good document for learning different joins in Sqlserver? .I have
seen some of them on the net but not very helpful .
ThanksHi Ray
Books online is the first call for anything to do with SQL Server. The
latest version can be downloaded from
http://www.microsoft.com/sql/techin.../2000/books.asp
The topics "Using Joins"
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_09_3mk3.htm
and "Join Fundermental"
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_09_610z.htm
(which if installed in the default location you can post the above links
into the GO menu option) should give you a reasonable understanding of the
types of join available and examples you can test on the example databases.
John
"RayAll" <RayAll@.microsft.com> wrote in message
news:Ol%23PD2FHFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Is there a good document for learning different joins in Sqlserver? .I
> have seen some of them on the net but not very helpful .
> Thanks
>|||Thanks John,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23BtIpENHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi Ray
> Books online is the first call for anything to do with SQL Server. The
> latest version can be downloaded from
> http://www.microsoft.com/sql/techin.../2000/books.asp
> The topics "Using Joins"
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\a
cdata.chm::/ac_8_qd_09_3mk3.htm
> and "Join Fundermental"
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\a
cdata.chm::/ac_8_qd_09_610z.htm
> (which if installed in the default location you can post the above links
> into the GO menu option) should give you a reasonable understanding of the
> types of join available and examples you can test on the example
> databases.
> John
> "RayAll" <RayAll@.microsft.com> wrote in message
> news:Ol%23PD2FHFHA.3912@.TK2MSFTNGP10.phx.gbl...
>