Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server
7.0 database?
select a.name from sysobjects a where a.type = 'V' and a.status > 0
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||This is actually what I was looking for. Many thanks

Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server 7.0 database
select a.name from sysobjects a where a.type = 'V' and a.status >
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
--
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||This is actually what I was looking for. Many thanks

Gathering Field Descriptions From SAP

I am at the documentation stage of our project and I need Field Descriptions. They have been defined in SAP R/3 (DB2) and I can view them using SE11 command line parameter (It may change fom version to version, I don't know. It is the place where you view tables).
Lets say table X have 10 columns and every column's description has already been entered in SAP. In my situation there is a total amount of 200 tables which comes out appr. 2000 descriptions in return.
My question is as you may guess, Is there a way to gather this descriptions in a form of table(s)? So I can easily use this info for documentation. It maybe a third party tool or a command or a script wahatever. I really need this.

Thanks in advance.I have forgotten to tell that I am using SQL SERVER 2000 and connecting to DB2 via ODBC driver (though it is so slow...).

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THX
nicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com
|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> > I have a view on SQL server 2000.
> > Using enterprise mngr to admin it.
> >
> > In my view I would like to add a column that calculates something
> > with the other column.
> > An example could be:
> >
> > Column Table
> > a x
> > b x
> > a1 y
> > mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> >
> > It's a bit like in Ms Access, where you have the expression builder.
> >
> > THX
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Functionality Like Pivot Chart

There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?

TIA
DeanDiscover the joy of the 'matrix control' or as I like to think of it:

A design time pivot table, but much less fun to use.

You just set the groups on the columns or rows. Simple. You can even have (right click menu) subtotals.

If you need to have 'other data' displayed, you will need to fiddle about with the Inscope, and create phantom groupings.

see this:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

green bar matrix:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx|||Can the matrix control display data as a CHART?|||http://msdn2.microsoft.com/en-us/library/aa964128(SQL.90).aspx

function within view

I have a view built like this
CREATE VIEW XXX
AS
select * from XXX_Calculate()
the XXX_calculate() function is built like this
CREATE FUNCTION XXX_Calculate ()
RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
....
it contains cursors which insert values to the @.Result table.
The problem is whenever a user calls this view the function is beiing
executed again so the retrieval is slow...
Is there a hint to have it behave like normal view?
Thanx in advance.
Sorry about my poor English...P Platan:
At first i'm trying to to use cursors at all.
Because as you see it works very slow, i would offer you to think how to be
avoid usin cursor.
If your example is as well as your view', I dont see whay do you need view
at all. On many programs that use sql you can use SELECT * fron function().
I would offer you to use store procedure instead of view. because on store
procedure you can set the function result on one temporary table and use it
as you can in the store procedure. Also all other software who work with sql
server can use store procedure as well as view.
"P Platan" <pplat@.exnds.com> wrote in message
news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>I have a view built like this
> CREATE VIEW XXX
> AS
> select * from XXX_Calculate()
> the XXX_calculate() function is built like this
> CREATE FUNCTION XXX_Calculate ()
> RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
> ....
> it contains cursors which insert values to the @.Result table.
> The problem is whenever a user calls this view the function is beiing
> executed again so the retrieval is slow...
> Is there a hint to have it behave like normal view?
> Thanx in advance.
> Sorry about my poor English...
>|||I use view because it resides in another database from the one that the
function calls.
To be more specific
In the old datbase schema we had a basic table with 150 categories as fields
I the new implementation we want to normalize it and have them 'vertical'.
In order not to transfer lots of data to the other db and not load triggers
in the basic table which is accessed very heavily we created the view to the
new db which 'verticals' the categories-fields of the basic table.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uNX6wjeQGHA.5248@.TK2MSFTNGP09.phx.gbl...
>P Platan:
> At first i'm trying to to use cursors at all.
> Because as you see it works very slow, i would offer you to think how to
> be avoid usin cursor.
> If your example is as well as your view', I dont see whay do you need view
> at all. On many programs that use sql you can use SELECT * fron
> function().
> I would offer you to use store procedure instead of view. because on store
> procedure you can set the function result on one temporary table and use
> it as you can in the store procedure. Also all other software who work
> with sql server can use store procedure as well as view.
> "P Platan" <pplat@.exnds.com> wrote in message
> news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>

function call acting odd

We upgraded to SP4 last night. Prior to that upgrade, a specific function
worked fine (since 2003 as a matter of fact). Now, if I create a view using
Enterprise Manager and call this function, I get an erroneous error message
about needing to convert a data type. If I write the same query in query
analzyer, however, it works as expected. Prior views and stored procedures
that call this function continue to work as expected. Any suggestions on
what is going on here? Is this SP4 related or something else?> create a view using Enterprise Manager
I strongly recommend sticking with Query Analyzer for this kind of task. EM
is fine for look-see and administrative type tasks, but I do not think it is
the optimal tool for script or data management.
A|||Aaron,
Query Analyzer may be the better choice but the point is that EM no longer
works as it did in the past, which tells me there is a problem somewhere.
Thanks anyway.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly recommend sticking with Query Analyzer for this kind of task.
EM
> is fine for look-see and administrative type tasks, but I do not think it
is
> the optimal tool for script or data management.
> A
>
>|||> Query Analyzer may be the better choice but the point is that EM no longer
> works as it did in the past, which tells me there is a problem somewhere.
And if you service your Yugo, its behavior may change also. Doesn't mean
you should have ever had a Yugo in the first place. :-)

Fun of INSTEAD OF UPDATE trigger

Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:

> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan

Full-Text search and database view

My understanding from full-text search (with SQL 2000) is that you can only
search a table and not a view; i.e. you cannot create a full-text index for
a view.
1) Is there a way to full-text search a view?
2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?
Thanks...
Denis.
In SQL 2005 you can full text index and search views.
Currently in SQL 2000 you can't full text index views, so you will have to
materialize the view as a table, i.e. create a table which has the same
columns as a view and use replication or triggers to keep this table in sync
with the base table. Then FTI this table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> My understanding from full-text search (with SQL 2000) is that you can
only
> search a table and not a view; i.e. you cannot create a full-text index
for
> a view.
> 1) Is there a way to full-text search a view?
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> Thanks...
> Denis.
>
|||Denis,
Yes, your understanding is correct for SQL Server 2000.
1) Is there a way to full-text search a view?
A. You cannot FT Index the "contents" of a view, however, you can include
SQL FTS statements such as CONTAINS or FREETEXT in a view.
2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?
A. Depending upon your requirements (and more info on this would be
helpful), and assuming that you want to use FTS to search either multiple
columns or multiple tables & column in one SQL FTS statement, you may want
to checkout "SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
If you have further questions on how to enhance SQL FTS, feel free to leave
a comment!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> My understanding from full-text search (with SQL 2000) is that you can
only
> search a table and not a view; i.e. you cannot create a full-text index
for
> a view.
> 1) Is there a way to full-text search a view?
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> Thanks...
> Denis.
>
|||Thanks for your help, John (and Hilary)!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OttF9zhGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> Denis,
> Yes, your understanding is correct for SQL Server 2000.
> 1) Is there a way to full-text search a view?
> A. You cannot FT Index the "contents" of a view, however, you can include
> SQL FTS statements such as CONTAINS or FREETEXT in a view.
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> A. Depending upon your requirements (and more info on this would be
> helpful), and assuming that you want to use FTS to search either multiple
> columns or multiple tables & column in one SQL FTS statement, you may want
> to checkout "SQL Server FTS across multiple tables or columns" at
>
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> If you have further questions on how to enhance SQL FTS, feel free to
leave
> a comment!
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
> news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> only
> for
>

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.