Nested Full Text Queries

Can someone help me or provide a workaround? The object is to select all
articles which are not references to other articles. The problem I am
having is when I try to get the ReferenceCount (which is the count of
articles which reference this article) it is throwing a 42000 (Error 170:
Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
nested full text query. I have tried successfully using LIKE '%' +
a.[Message-ID] + '%' which returns correctly just slow. By the way the The
References field is a varchar(500).
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
John,
I'm not exactly sure what you're trying to achieve with this query, but it
is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
what you may want to do is an INNER JOIN between the FTS query using
CONTAINSTABLE and your table tblarticles. For example, the syntax of the
rowset-based CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
-- and an example using a Pubs database table:
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
"greene")' ) AS KEY_TBL
WHERE
FT_TBL.au_id = KEY_TBL.[KEY]
/*-- returns:
au_lname au_fname RANK
-- -- --
Greene Morningstar 80
Green Marjorie 80
Ringer Anne 64
Ringer Albert 64
(4 row(s) affected)
*/
I'd recommend altering your query to use the above CONTAINSTABLE or
FREETEXTTABLE syntax.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:oU_gc.23991$6m4.934106@.twister.southeast.rr.c om...
> Can someone help me or provide a workaround? The object is to select all
> articles which are not references to other articles. The problem I am
> having is when I try to get the ReferenceCount (which is the count of
> articles which reference this article) it is throwing a 42000 (Error 170:
> Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
> nested full text query. I have tried successfully using LIKE '%' +
> a.[Message-ID] + '%' which returns correctly just slow. By the way the
The
> References field is a varchar(500).
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
>
>
|||Thanks for replying john,
Unless I am overlooking something and please correct me if I am, you simply
provided an example of how to do a standard full text search. I do not have
a problem doing that. I thought I explained what im trying to achieve very
well.
Again, This is what im trying to do but its to slow:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
This is what I have tried:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%' "
for "CONTAINS([References], a.[Message-ID])"
"John Kane" <jt-kane@.comcast.net> wrote in message
news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
> John,
> I'm not exactly sure what you're trying to achieve with this query, but it
> is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
> what you may want to do is an INNER JOIN between the FTS query using
> CONTAINSTABLE and your table tblarticles. For example, the syntax of the
> rowset-based CONTAINSTABLE is:
> SELECT select_list
> FROM table AS FT_TBL INNER JOIN
> CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
> ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
> -- and an example using a Pubs database table:
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
> "greene")' ) AS KEY_TBL
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY]
> /*-- returns:
> au_lname au_fname RANK
> -- -- --
> Greene Morningstar 80
> Green Marjorie 80
> Ringer Anne 64
> Ringer Albert 64
> (4 row(s) affected)
> */
> I'd recommend altering your query to use the above CONTAINSTABLE or
> FREETEXTTABLE syntax.
> Regards,
> John
|||You're welcome, John,
I've given this some more thought and have done some testing with the
authors table in the Pubs database I do not have the table schema for your
tblarticles table. First of all, the syntax you are using for the CONTAINS
predicate is incorrect, and assuming you are referencing a variable in the
search clause, your query should be re-written as follows:
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT [ID], [References], [From], [Date], [Subject],
(SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.SearchStr)) AS ReferenceCount
FROM tblarticles a
WHERE [GroupID] = @.GroupID AND [References]=''
ORDER BY [Date] Desc;
Additionally, I tested the following alternative using CONTAINSTABLE with
and INNER JOIN on the a SELECT * from CONTAINSTABLE. However, this solution
does not work with count(*) or count(au_id) for the join condition.
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL INNER JOIN
(SELECT * FROM
CONTAINSTABLE(authors,au_lname, @.SearchStr)) AS KEY_TBL
ON FT_TBL.au_id = KEY_TBL.[KEY]
/* -- returns:
au_lname au_fname RANK
--- -- --
Green Marjorie 80
White Johnson 80
(2 row(s) affected)
*/
Finally, I altered the initial query and use the assignment symbol "=" for
the SELECT * from authors where contains() and this did the trick.
declare @.SearchStr varchar(8000), @.State char(2)
SET @.SearchStr = '"green" or "white"'
SET @.State = 'CA'
SELECT au_id, au_lname, au_fname, SearchCount = (SELECT count(*) FROM
authors where CONTAINS(au_lname, @.SearchStr))
FROM authors
WHERE state = @.State and contract = 0
ORDER BY au_lname DESC
/*-- returns:
au_id au_lname au_fname
SearchCount
-- --- -- --
724-08-9931 Stringer Dirk 2
893-72-1158 McBadden Heather 2
(2 row(s) affected)
*/
You should alter your query to match the above and confirm that this works
for your tblarticles table.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:qu8hc.44033$yv.952732@.twister.southeast.rr.co m...
> Thanks for replying john,
> Unless I am overlooking something and please correct me if I am, you
simply
> provided an example of how to do a standard full text search. I do not
have
> a problem doing that. I thought I explained what im trying to achieve
very
> well.
> Again, This is what im trying to do but its to slow:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
> ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> This is what I have tried:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS
ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%'
"[vbcol=seagreen]
> for "CONTAINS([References], a.[Message-ID])"
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
*think*
>
|||Again thanks John,
The problem I am having is that where you are using a predefined (or passed)
variable @.SearchStr, I am needing this to come from the outer query. This
is why you see my variable labeled as a.[Message-ID]. The "a" is a
reference to the outer SELECT statements [Message-ID] field.
My Table looks like this:
CREATE TABLE [dbo].[tblArticles] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FROM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE] [datetime] NOT NULL ,
[Subject] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message-ID] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Path] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[Article] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[References] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And for the record this gets the desired results, its just slow and does not
make use of full text.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
Which again brings me back to the original question of nesting full text
queries.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], '%' + a.[Message-ID] + '%')) AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;

> --SNIP --
>

need tool for managing database object ownership

we have a group of developers which have created and asked us (DBAs) to
create many objects in the databases including tables / stored
procedures / functions / etc.

since our company is growing, however we have an increasing amount of
objects that have either been abandoned or have several versions.

in an effort to clean of the huge amount of clutter and anytime that
something simple like a stored proc needs to change, it is almost
impossible to predict exactly where we will see negative effects of
this change.

i am looking for a system (preferably without developing our own tool)
that would keep track of history of database objects (in terms of who
created it and what purpose it has) as well as link that to all the
developers/users we might need to notify of any changes to that object.
Also, this should be linked to the application which rely on the
object.

bottom line... every object in the database needs to have at least 1
corresponding contact as well as the applications which us it.

with this information, we can much more easily maintain objects in our
DBs.

thxPossibly this can be of help. Seems it isn't released quite yet, though:

http://msdn.microsoft.com/vstudio/t...ro/default.aspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegr oups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.

In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.

The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.

A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.

"put everything under version control" is one word? Seems like 5 to
me!

8-)

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

need tool for managing database object ownership

we have a group of developers which have created and asked us (DBAs) to
create many objects in the databases including tables / stored
procedures / functions / etc.
since our company is growing, however we have an increasing amount of
objects that have either been abandoned or have several versions.
in an effort to clean of the huge amount of clutter and anytime that
something simple like a stored proc needs to change, it is almost
impossible to predict exactly where we will see negative effects of
this change.
i am looking for a system (preferably without developing our own tool)
that would keep track of history of database objects (in terms of who
created it and what purpose it has) as well as link that to all the
developers/users we might need to notify of any changes to that object.
Also, this should be linked to the application which rely on the
object.
bottom line... every object in the database needs to have at least 1
corresponding contact as well as the applications which us it.
with this information, we can much more easily maintain objects in our
DBs.
thxPossibly this can be of help. Seems it isn't released quite yet, though:
http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegroups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx
>|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.
The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.
A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Possibly this can be of help. Seems it isn't released quite yet, though:
http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegroups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx
>|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.
The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.
A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.
"put everything under version control" is one word? Seems like 5 to
me!
8-)
Paul...
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work
!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.
"put everything under version control" is one word? Seems like 5 to
me!
8-)
Paul...
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work
!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.