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 syntex problem.....I think

Have a SQL paramater query running in VBA. Fails on the line that requests information. The problem is the brackets [] that are used to input data to make the query operate between certain limits. I used Between [enter x] and [enter y] which does not work in VBA. If I use Between #01/01/2004# and #07/30/2004# it works with the fixed data. The query works just fine in the normal Access 2000 gui but not in VBA.
Can anybody help with this problem. I have looked high and low but find everything else but what to do with the brackets in a SQL VBA application.
Thanks,
DonThe use of brackets in a query is an MS-Access specific feature... VBA doesn't directly support that functionality. You will have to code the parameter entry screen and build the query in VBA.

-PatP

A native exception occurred: synchronizing data between the MS SQL Server database and PDA

ISSUE:

While synchronizing data between the PDA and MS SQL Server database, using Active Sync connection, the sync process fails at times and displays an empty error message box and occasionally it shows the following exception:

Error During Synchronization:

A native exception occurred.

ExceptionCode:0xc0000005

ExceptionAddress:0x01627b28

Reading: 0x1e000000

OK to terminate

CANCEL to debug

Moreover the .SDF file on the PDA gets corrupted. The ActiveSync connection drops as soon as the sync process fails. Later when the .SDF file is deleted and restored the synchronization is successful.

Note: The replication monitor in the SQL Server indicates that the synchronization was successful even when the above error occurs.

SCENARIO:

Steps to replicate:

1. The PDA is placed on the cradle and a manual connection is established with the SQL Server database using the guest partnership.
2. The PDA application is invoked. Some data is saved in the local .sdf file via the application.
3. The User clicks on the “Sync” menu option (Sample code below **) to start the sync process
4. Most times the sync process is successful. Occasionally the above issue is encountered.

** Sample code where the app crashes we encounter the issue:

Public Sub Synchronize (ByVal vShowMessage As Boolean)

Dim ce As SqlCeReplication = Nothing

Try

ce = New SqlCeReplication

'Set Internet properties.

ce.InternetUrl = vInternetURL ' "http://xx.xx.xx.xx/SQLCE/sscesa20.dll"

'Set Publisher properties.

ce.Publisher = vPublisher '"CGH-RSMS"

ce.PublisherLogin = vPublisherLogin '"sa"

ce.PublisherPassword = vPublisherPassword '"xxxxxx"

ce.PublisherSecurityMode = SecurityType.DBAuthentication

ce.PublisherNetwork = NetworkType.DefaultNetwork

ce.PublisherDatabase = vPublisherDatabase '"RSMS"

ce.Publication = vPub_Name '"PUB_RSMS"

'Set Subscriber properties.

ce.Subscriber = vSub_Name

ce.SubscriberConnectionString = "Data Source=" & DB_PATH & vDatabase_Name

If File.Exists(DB_PATH & vDatabase_Name) = False Then

ce.AddSubscription(AddOption.CreateDatabase)

End If

ce.Synchronize()

Catch ex As SqlCeException

If vShowMessage = True Then

MsgBox(ex.Message)

End If

Throw ex

Catch ex1 As Exception

If vShowMessage = True Then

MsgBox(ex1.Message)

End If

Throw ex1

Finally

ce = Nothing

End Try

End Sub

Asheesh, I am following up with you on this issue on the other thread titled:
"Initializing SQL Server Reconciler has failed"

Lets just work on one thread.|||

Hi Mahesh....
Ya sure i will be following you up on the Initializing SQL Server Reconciler has failed" thread.Well tahks a lot for ur help and guidance.will be in touch regarding the same.

Thanks and Warm Regards,
Asheesh.