More help with Contains

I had posted an earlier question about the CONTAINS predicate that uses the
full text search.
The entire process of comparing a column with a keyword variable using the
contains predicate was not only taking too long (cursors) but also breaking
the stored procedure when noise words were encountered.
I have come up with an alternative that involves copying all the data I want
to compare into a table say Table A. This table will contain three columns
ColA - The description Column
ColB - The column containing the keywords
ColC - A Bit/Flag column.
Can I do this?
Update TableA
SET ColC = 1
WHERE CONTAINS(COLA, COLB)
Can I use two columns in the contains predicate
I know I need to refresh the full text search index after I fill Table A
with data and this user needs DBO permission.
Can a user - datareader/datawriter be assigned privileges to update the full
text.
Thanks in advance
SanjaySanjay,
What is the version of SQL Server and on what OS platform do you have it
installed? Could you post the full output of -- SELECT @.@.version -- as this
is most helpful info in troubleshooting SQL FTS issues.
Yes, you can use CONTAINS an UPDATE statement's where clause. If fact, I've
been planning on writing an article entitled "Putting Full Text Search to
work" that would demonstrate how to use FTS queries with INSERT, DELETE and
UPDATE to do real database table data manipulation, and the article is still
on the drawing boards & perhaps will be posted to my blog soon...
Q. Can I use two columns in the contains predicate?
A. No. However, you can use multiple CONTAINS clauses, i.e., CONTAINS(COLA,
'search1') AND/OR CONTAINS(COLA, 'search2') in SQL Server 2000. Furthermore,
in SQL Server 2005 (Yukon) will support multiple columns in the CONTAINS
syntax.
Q. Can a user - datareader/datawriter be assigned privileges to update the
full text?
A. No, as symin privileges are required to update the Full Text
Index/Catalog via the system stored procs: sp_fulltext*. See SQL Server 2000
BOL title "sp_fulltext_catalog" - "Only members of the symin fixed server
role and the db_owner (or higher) fixed database roles can execute
sp_fulltext_catalog."
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp#AFHA.2112@.TK2MSFTNGP14.phx.gbl...
> I had posted an earlier question about the CONTAINS predicate that uses
the
> full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
breaking
> the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
want
> to compare into a table say Table A. This table will contain three columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
full
> text.
> Thanks in advance
> Sanjay
>|||Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sql Server 7
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||I setup full text searches on a table to test my solution and it DID NOT
WORK!!!
So it seems like you can't do this
Select * from TableA WHERE CONTAINS(COLA, COLB)
Where COLA & COLB are both columns in TableA
COLA has a full text search index defined on it.
Thanks for the replies though.
The details of the SQL Server are...
****************************************
***********
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
****************************************
***********
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sanjay,
I never said that you could use multiple columns in ONE contains statement
with SQL Server 2000.
What I said is that you could use MULTIPLE contains clauses in your WHERE
clause with SQL Server 2000, specifically:
CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
'search_word_here').
More than one column per contains statement is ONLY supported in SQL Server
2005 that is still in limited beta release.
Hope that helps,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I setup full text searches on a table to test my solution and it DID NOT
> WORK!!!
> So it seems like you can't do this
> Select * from TableA WHERE CONTAINS(COLA, COLB)
> Where COLA & COLB are both columns in TableA
> COLA has a full text search index defined on it.
> Thanks for the replies though.
> The details of the SQL Server are...
> ****************************************
***********
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
> May 29 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> ****************************************
***********
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
the
the
>|||I'm sorry if I gave you that impression. My original question was just that
and I finally got down to setting up the environment to see if I could
compare two columns and subsequently discovered I could not. I however have
another bainwave that involves the use of a UDF that takes the keyword as a
parameter and looks up columnA using a uniqueid to compare the value of the
row.
So esentially I plan to do this
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
I am not sure if this will work but I plan to try and see for myself.
Thanks for the reply though
Sanjay
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Sanjay,
> I never said that you could use multiple columns in ONE contains statement
> with SQL Server 2000.
> What I said is that you could use MULTIPLE contains clauses in your WHERE
> clause with SQL Server 2000, specifically:
> CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
> Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
> 'search_word_here').
> More than one column per contains statement is ONLY supported in SQL
> Server
> 2005 that is still in limited beta release.
> Hope that helps,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> the
> the
>|||Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
Do I have any other alternatives?
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
> I'm sorry if I gave you that impression. My original question was just
> that and I finally got down to setting up the environment to see if I
> could compare two columns and subsequently discovered I could not. I
> however have another bainwave that involves the use of a UDF that takes
> the keyword as a parameter and looks up columnA using a uniqueid to
> compare the value of the row.
> So esentially I plan to do this
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
>
> I am not sure if this will work but I plan to try and see for myself.
> Thanks for the reply though
> Sanjay
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||You're welcome, Sanjay,
As they say in the movies..., lets try again (or something like that ;-). If
not UDF, how about a cursor operation? I'm not sure if this will meet your
needs, but you may be able to fit it to your requirements.. The table
jtkane_Tables contains a list of keywords that is the source of the @.keyword
variable for FT Searching of the jtkane_SysComments table.
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select TableNane from jtkane_Tables order by TableNane
CREATE TABLE #table_name (tblname varchar(100))
OPEN keyword_cursor
DECLARE @.keyword varchar(100)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct SysNames from jtkane_SysComments
where contains(*, @.keyword)
INSERT INTO #table_name (tblname) VALUES (@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
select * from #table_name order by tblname
drop table #table_name
If the above doesn't fit your needs, checkout the use of
"sp_help_fulltext_catalogs_cursor" in the code example at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308.e
ntry
Enjoy!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
> Do I have any other alternatives?
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
WHERE
'search_word_here')
NOT
uses
using
data
Table
>|||The problem with cursors is that they take just too long are inefficient and
create all sorts of problems with locks( though I am yet to experience
problems with locking)
We currently have an inefficient procedure that uses cursors that I am
trying to replace hence the discourse :)
Thanks once again for the reply though!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%231gBsdbBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Sanjay,
> As they say in the movies..., lets try again (or something like that ;-).
> If
> not UDF, how about a cursor operation? I'm not sure if this will meet your
> needs, but you may be able to fit it to your requirements.. The table
> jtkane_Tables contains a list of keywords that is the source of the
> @.keyword
> variable for FT Searching of the jtkane_SysComments table.
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select TableNane from jtkane_Tables order by TableNane
> CREATE TABLE #table_name (tblname varchar(100))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(100)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select distinct SysNames from jtkane_SysComments
> where contains(*, @.keyword)
> INSERT INTO #table_name (tblname) VALUES (@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> select * from #table_name order by tblname
> drop table #table_name
> If the above doesn't fit your needs, checkout the use of
> "sp_help_fulltext_catalogs_cursor" in the code example at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308
.entry
> Enjoy!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> WHERE
> 'search_word_here')
> NOT
> uses
> using
> data
> Table
>