How big is the table? It shouldn't take long at all to build the indexes.
________________________________ From: jonese <gig...@gmail.com> To: discussion@acfug.org Sent: Friday, January 23, 2009 4:22:32 PM Subject: Re: [ACFUG Discuss] Full Text Search Query Question I'm thinking the index is just not finished being built and this is causing issues. i'm going to give it a day (or weekend as the case may be) to catch up and settle down... thanks for your efforts though teddy! ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese On Fri, Jan 23, 2009 at 4:18 PM, Teddy R. Payne <teddyrpa...@gmail.com> wrote: And this: SELECT * FROM ( SELECT * FROM author WHERE CONTAINS(authorLastName, N'"LYNE"') ) q1 WHERE CONTAINS(authorFirstName, N'"B"') Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Fri, Jan 23, 2009 at 4:11 PM, jonese <gig...@gmail.com> wrote: Still returns zero records. if i do select * from author WHERE CONTAINS(authorLastName, N'"LYNE"') i get 6 records, one of those has a forename of B If i do select * from author WHERE CONTAINS(authorForeName, N'"B"') i get zero records If I do select * from author WHERE CONTAINS(authorForeName, N'"*B*"') I get 5 results but none of them are just forename of B wierd..... ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese On Fri, Jan 23, 2009 at 4:05 PM, Teddy R. Payne <teddyrpa...@gmail.com> wrote: Ok, try this then if you are using SQL Server: USE WhateverYourDatabaseIs; GO DECLARE @foreName nvarchar(100) , @lastName nvarchar(100) SELECT @foreName = N'B' , @lastName = N'Lyne' SELECT * FROM author WHERE CONTAINS(authorForeName, @foreName) AND CONTAINS(authorLastName, @lastName) Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Fri, Jan 23, 2009 at 3:51 PM, jonese <gig...@gmail.com> wrote: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'N'B''. ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese On Fri, Jan 23, 2009 at 3:48 PM, Teddy R. Payne <teddyrpa...@gmail.com> wrote: SELECT * FROM author WHERE CONTAINS(authorForeName, "N'B'") AND CONTAINS(authorLastName, "N'LYNE'") Did you try the above query? Which is a slight derivation on yours. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Fri, Jan 23, 2009 at 3:42 PM, jonese <gig...@gmail.com> wrote: Teddy, Nope the quotes are all ok. the value is in single quotes after the N (yes it's nVarChar) and the double quote are for phrases. In my example i could have removed the double quotes and gotten the same results etc. The syntax appears to be A-OK because i get no errors. I'm just not getting why i can't search on MULTIPLE columns with MULTIPLE values which are in a fulltext index.. ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese On Fri, Jan 23, 2009 at 3:36 PM, Teddy R. Payne <teddyrpa...@gmail.com> wrote: What is the data type of the columns you are referencing? (authorForeName, N'"B"') The N there I believe is a reference for nVarchar if you are allowing i10n compatibility. The syntax looks wonky as I thought it was only a single quote like N'B' Then you can wrap it up in double quotes like "N'B'" So I suspect you quotes are out of order. Double quote then the N then the single quote then the literal string then single quote then double quote. Savvy? Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Fri, Jan 23, 2009 at 3:04 PM, jonese <gig...@gmail.com> wrote: I have two columns authorForeNAme and authorLastName and i'm trying to search against a Full Text index to find a specific author. i thought i could do this: SELECT * FROM author WHERE CONTAINS(authorForeName, N'"B"') AND CONTAINS(authorLastName, N'"LYNE"') But it returns zero records. however if i do select * from author WHERE authorForeName = 'B'' AND CONTAINS(authorLastName, N'"LYNE"') I get my desired results. Why doesn't the first query work as expected?___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------