Embedding reporting services as a standalone product

Hi,
In the application that I am working on, we currently use Crystal Reports as
the embedded reporting engine. I am trying to evaluate if we should move
over to Microsoft Reporting Services. However, one thing confuses me. It
seems Microsoft Reporting Services will work only as a server under IIS.
This will not work for our customers. I am wondering if Reporting Services
has any embedded component much like Crystal Reports that we could use.
Thank you in advance for your help.
SunnyVersion 2 will have a web form and a winform that does not require the
server (works with the server if there but does not require it).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sansanee" <sansanee@.nospam.com> wrote in message
news:%23LD40RJ3EHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the application that I am working on, we currently use Crystal Reports
as
> the embedded reporting engine. I am trying to evaluate if we should move
> over to Microsoft Reporting Services. However, one thing confuses me. It
> seems Microsoft Reporting Services will work only as a server under IIS.
> This will not work for our customers. I am wondering if Reporting Services
> has any embedded component much like Crystal Reports that we could use.
> Thank you in advance for your help.
> Sunny
>

Embedded images aren't showed

Hi,
When I access the Product Catalog of SampleReports in ReportServer
using the server name in the URL, that is,
http://MY_SERVER_NAME/ReportServer?/SampleReports/Product Catalog, I
can't see any images embedded in the report, I get a red "X" instead.
But when I access the same URL using LOCALHOST, once I'm using IE in
the server directly, I can see all images embedded.
My environment is RS SP1, IE 6.0.2900.2180 and WinXP SP2.
I don't think it's a problem with the Security or Privacy
configuration of IE because both LOCALHOST and MY_SERVER_NAME are in
Local Intranet Zone wich has Low Security. Besides, I've tried to
reduced the Privacy to "Accept All Cockies" as well, though anything
seems to help.
I'd rather keep the images embedded than put them into the database or
in a External link.
What else should I try?
Thanks in advance,
Vinicius BellinoYou might try uploading the image to the same folder in Report Server
that the report file resides in. You can then set the image's property to
'Hide in list view' so that users only see the hyperlink for the report
and not the image file as well.
GeoSynch
"Vinicius Bellino" <vbellino@.uol.com.br> wrote in message
news:93d12908.0505021244.253fd001@.posting.google.com...
> Hi,
> When I access the Product Catalog of SampleReports in ReportServer
> using the server name in the URL, that is,
> http://MY_SERVER_NAME/ReportServer?/SampleReports/Product Catalog, I
> can't see any images embedded in the report, I get a red "X" instead.
> But when I access the same URL using LOCALHOST, once I'm using IE in
> the server directly, I can see all images embedded.
> My environment is RS SP1, IE 6.0.2900.2180 and WinXP SP2.
> I don't think it's a problem with the Security or Privacy
> configuration of IE because both LOCALHOST and MY_SERVER_NAME are in
> Local Intranet Zone wich has Low Security. Besides, I've tried to
> reduced the Privacy to "Accept All Cockies" as well, though anything
> seems to help.
> I'd rather keep the images embedded than put them into the database or
> in a External link.
> What else should I try?
> Thanks in advance,
> Vinicius Bellino

Embedded HTML in RS 2005?

Reporting Services is a great product, especially considering that is a 1.x
release. Unfortunately, not being able to render embedded HTML (in report
data) is a serious limitation. This is a critical need for our business.
I would like to know from a MSFT source if this will be supported in SQL
Server 2005? What is the timeframe for this?
Thanks,
Jeremy Carter
ING Investment ManagementWe are aware that embedded rich text is very important for many of our
customers. However we didn't have time to add this in SQL Server 2005
Reporting Services. This will be added in a future version.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JerC" <JerC@.discussions.microsoft.com> wrote in message
news:1E2BB6D8-2A77-4616-A578-879C33F2DABA@.microsoft.com...
> Reporting Services is a great product, especially considering that is a
> 1.x
> release. Unfortunately, not being able to render embedded HTML (in report
> data) is a serious limitation. This is a critical need for our business.
> I would like to know from a MSFT source if this will be supported in SQL
> Server 2005? What is the timeframe for this?
> Thanks,
> Jeremy Carter
> ING Investment Management
>

Eliminating a table scan from a query

I'm doing a simple join from an inventory table to a product table to look up
the product name. Both the inventory table and product table have indexes on
the shared key. Nevertheless its using a table-scan against the product
table, which is taking up a lot of time (over 1/2 of the outer query this is
part of). Here it is:
SELECT position, value, a.productId, a.productname
FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
Looking at the output of Query Analyzer, it is using the index on positions
for the bank, then table scanning products.
Any ideas?
Maury
ABS(position) > 0.001
Don't have functions etc on the column side of the query. In most cases it will prohibit the
optimizer from using a column. Unless I misunderstand ABS, you should be able to write it as:
position > 0.001 OR position < -0.001
Above doesn't mean that the plan *will* change but at least the optimizer has a chance now to use an
index on that column. Whether or not that happens depends on a lot of factors, like type of join,
join density, number of rows in the tables, selectivity of each restriction, accuracy of statistics
etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:D2261870-06AC-429B-9870-5CC6ACAA14E2@.microsoft.com...
> I'm doing a simple join from an inventory table to a product table to look up
> the product name. Both the inventory table and product table have indexes on
> the shared key. Nevertheless its using a table-scan against the product
> table, which is taking up a lot of time (over 1/2 of the outer query this is
> part of). Here it is:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury
|||> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury
Hi,
That is why it is taking such a long time. It should be doing an index
join on products. For some reason optimizer decided to chose the index
on position. It is very smart but not smart enough.
|||"Tibor Karaszi" wrote:

> position > 0.001 OR position < -0.001
No luck, unfortunately.
Oh wow, I was just able to eliminate the scan! I have no idea why this
works, but when I added a criterion on the products by joining against
another table of product types, then it worked instantly.
Now here's something interesting... when I use ABS in _that_ query it
behaves as you mention above and the table scan comes back. Very odd!
Maury
|||Ok, this is just too weird.
In order to try to give the optimizer a little more to work with, I added a
join against a product type table. This table has a column that can be used
to find all products of a particular type (pumbing, etc.), and has seven
unique values. Here is the new version of the query:
SELECT position, value, a.productId, a.productname
FROM tblPositions p
INNER JOIN tblproducts a ON p.productId= a.productId
INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
WHERE p.bank not in (300, 400, 1866)
AND (position > 0.01 OR position < -0.01)
Ok, ready for this? If the value on assetType is > 1 the table scan
disappears and the query runs instantly. If the value is >0 or >=1, the table
scan re-appears!
So is there some way to force the system to use the plan I want?
Maury
|||It should not surprise you that you get different execution plans when you change the query, and
also when you change search criteria. This is what a cost-based optimizer is all about. Perhaps
there is a significant change in selectivity between > 0 and > 1, explaining why different execution
plans are chosen. Yes, there are several methods to force different aspects of a plan. For example
an index hint. I suggest you read about "optimizer hints" in Books Online for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added a
> join against a product type table. This table has a column that can be used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury
|||One more thing. Try rebuilding the indexes and updating statistics on the
tables to see if the optimizer can get better heuristics to work with. It
isn't that the optimizer isn't 'smart enough' as someone suggested. It is
indeed PLENTY smart - but it only has a limited set of data to work with and
that comes from the statistics information. It will usually switch from
index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
the number of rows will match your join/where clauses.
TheSQLGuru
President
Indicium Resources, Inc.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
> a
> join against a product type table. This table has a column that can be
> used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the
> table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury
|||"TheSQLGuru" wrote:
> that comes from the statistics information. It will usually switch from
> index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
> the number of rows will match your join/where clauses.
Ahhh, a useful number I'll try to remember.
Maury

Eliminating a table scan from a query

I'm doing a simple join from an inventory table to a product table to look up
the product name. Both the inventory table and product table have indexes on
the shared key. Nevertheless its using a table-scan against the product
table, which is taking up a lot of time (over 1/2 of the outer query this is
part of). Here it is:
SELECT position, value, a.productId, a.productname
FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
Looking at the output of Query Analyzer, it is using the index on positions
for the bank, then table scanning products.
Any ideas?
MauryABS(position) > 0.001
Don't have functions etc on the column side of the query. In most cases it will prohibit the
optimizer from using a column. Unless I misunderstand ABS, you should be able to write it as:
position > 0.001 OR position < -0.001
Above doesn't mean that the plan *will* change but at least the optimizer has a chance now to use an
index on that column. Whether or not that happens depends on a lot of factors, like type of join,
join density, number of rows in the tables, selectivity of each restriction, accuracy of statistics
etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:D2261870-06AC-429B-9870-5CC6ACAA14E2@.microsoft.com...
> I'm doing a simple join from an inventory table to a product table to look up
> the product name. Both the inventory table and product table have indexes on
> the shared key. Nevertheless its using a table-scan against the product
> table, which is taking up a lot of time (over 1/2 of the outer query this is
> part of). Here it is:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury|||> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on positions
> for the bank, then table scanning products.
> Any ideas?
> Maury
Hi,
That is why it is taking such a long time. It should be doing an index
join on products. For some reason optimizer decided to chose the index
on position. It is very smart but not smart enough.|||"Tibor Karaszi" wrote:
> position > 0.001 OR position < -0.001
No luck, unfortunately.
Oh wow, I was just able to eliminate the scan! I have no idea why this
works, but when I added a criterion on the products by joining against
another table of product types, then it worked instantly.
Now here's something interesting... when I use ABS in _that_ query it
behaves as you mention above and the table scan comes back. Very odd!
Maury|||Ok, this is just too weird.
In order to try to give the optimizer a little more to work with, I added a
join against a product type table. This table has a column that can be used
to find all products of a particular type (pumbing, etc.), and has seven
unique values. Here is the new version of the query:
SELECT position, value, a.productId, a.productname
FROM tblPositions p
INNER JOIN tblproducts a ON p.productId= a.productId
INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
WHERE p.bank not in (300, 400, 1866)
AND (position > 0.01 OR position < -0.01)
Ok, ready for this? If the value on assetType is > 1 the table scan
disappears and the query runs instantly. If the value is >0 or >=1, the table
scan re-appears!
So is there some way to force the system to use the plan I want?
Maury|||It should not surprise you that you get different execution plans when you change the query, and
also when you change search criteria. This is what a cost-based optimizer is all about. Perhaps
there is a significant change in selectivity between > 0 and > 1, explaining why different execution
plans are chosen. Yes, there are several methods to force different aspects of a plan. For example
an index hint. I suggest you read about "optimizer hints" in Books Online for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added a
> join against a product type table. This table has a column that can be used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||One more thing. Try rebuilding the indexes and updating statistics on the
tables to see if the optimizer can get better heuristics to work with. It
isn't that the optimizer isn't 'smart enough' as someone suggested. It is
indeed PLENTY smart - but it only has a limited set of data to work with and
that comes from the statistics information. It will usually switch from
index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
the number of rows will match your join/where clauses.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
> a
> join against a product type table. This table has a column that can be
> used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the
> table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||"TheSQLGuru" wrote:
> that comes from the statistics information. It will usually switch from
> index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
> the number of rows will match your join/where clauses.
Ahhh, a useful number I'll try to remember.
Maury

Eliminating a table scan from a query

I'm doing a simple join from an inventory table to a product table to look u
p
the product name. Both the inventory table and product table have indexes on
the shared key. Nevertheless its using a table-scan against the product
table, which is taking up a lot of time (over 1/2 of the outer query this is
part of). Here it is:
SELECT position, value, a.productId, a.productname
FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
Looking at the output of Query Analyzer, it is using the index on positions
for the bank, then table scanning products.
Any ideas?
MauryABS(position) > 0.001
Don't have functions etc on the column side of the query. In most cases it w
ill prohibit the
optimizer from using a column. Unless I misunderstand ABS, you should be abl
e to write it as:
position > 0.001 OR position < -0.001
Above doesn't mean that the plan *will* change but at least the optimizer ha
s a chance now to use an
index on that column. Whether or not that happens depends on a lot of factor
s, like type of join,
join density, number of rows in the tables, selectivity of each restriction,
accuracy of statistics
etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:D2261870-06AC-429B-9870-5CC6ACAA14E2@.microsoft.com...
> I'm doing a simple join from an inventory table to a product table to look
up
> the product name. Both the inventory table and product table have indexes
on
> the shared key. Nevertheless its using a table-scan against the product
> table, which is taking up a lot of time (over 1/2 of the outer query this
is
> part of). Here it is:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on position
s
> for the bank, then table scanning products.
> Any ideas?
> Maury|||> SELECT position, value, a.productId, a.productname
> FROM tblPositions p INNER JOIN tblProducts a ON p.productId= a.productId
> WHERE p.bank not in (300, 400, 1866) and ABS(position) > 0.001
> Looking at the output of Query Analyzer, it is using the index on position
s
> for the bank, then table scanning products.
> Any ideas?
> Maury
Hi,
That is why it is taking such a long time. It should be doing an index
join on products. For some reason optimizer decided to chose the index
on position. It is very smart but not smart enough.|||"Tibor Karaszi" wrote:

> position > 0.001 OR position < -0.001
No luck, unfortunately.
Oh wow, I was just able to eliminate the scan! I have no idea why this
works, but when I added a criterion on the products by joining against
another table of product types, then it worked instantly.
Now here's something interesting... when I use ABS in _that_ query it
behaves as you mention above and the table scan comes back. Very odd!
Maury|||Ok, this is just too weird.
In order to try to give the optimizer a little more to work with, I added a
join against a product type table. This table has a column that can be used
to find all products of a particular type (pumbing, etc.), and has seven
unique values. Here is the new version of the query:
SELECT position, value, a.productId, a.productname
FROM tblPositions p
INNER JOIN tblproducts a ON p.productId= a.productId
INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
WHERE p.bank not in (300, 400, 1866)
AND (position > 0.01 OR position < -0.01)
Ok, ready for this? If the value on assetType is > 1 the table scan
disappears and the query runs instantly. If the value is >0 or >=1, the tabl
e
scan re-appears!
So is there some way to force the system to use the plan I want?
Maury|||It should not surprise you that you get different execution plans when you c
hange the query, and
also when you change search criteria. This is what a cost-based optimizer is
all about. Perhaps
there is a significant change in selectivity between > 0 and > 1, explaining
why different execution
plans are chosen. Yes, there are several methods to force different aspects
of a plan. For example
an index hint. I suggest you read about "optimizer hints" in Books Online fo
r more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
a
> join against a product type table. This table has a column that can be use
d
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the ta
ble
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||One more thing. Try rebuilding the indexes and updating statistics on the
tables to see if the optimizer can get better heuristics to work with. It
isn't that the optimizer isn't 'smart enough' as someone suggested. It is
indeed PLENTY smart - but it only has a limited set of data to work with and
that comes from the statistics information. It will usually switch from
index usage to table scan if it estimates more than roughly 10-15% (IIRC) of
the number of rows will match your join/where clauses.
TheSQLGuru
President
Indicium Resources, Inc.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1503F046-4007-4228-93D8-D6BA32D1DD7E@.microsoft.com...
> Ok, this is just too weird.
> In order to try to give the optimizer a little more to work with, I added
> a
> join against a product type table. This table has a column that can be
> used
> to find all products of a particular type (pumbing, etc.), and has seven
> unique values. Here is the new version of the query:
> SELECT position, value, a.productId, a.productname
> FROM tblPositions p
> INNER JOIN tblproducts a ON p.productId= a.productId
> INNER JOIN tblSecType t ON t.sectypeId = p.sectypeId AND t.assetType > 0
> WHERE p.bank not in (300, 400, 1866)
> AND (position > 0.01 OR position < -0.01)
> Ok, ready for this? If the value on assetType is > 1 the table scan
> disappears and the query runs instantly. If the value is >0 or >=1, the
> table
> scan re-appears!
> So is there some way to force the system to use the plan I want?
> Maury|||"TheSQLGuru" wrote:
> that comes from the statistics information. It will usually switch from
> index usage to table scan if it estimates more than roughly 10-15% (IIRC)
of
> the number of rows will match your join/where clauses.
Ahhh, a useful number I'll try to remember.
Maury

Eliminate duplicates from datasource

Hi All,

I have a SP, which i run inside a for loop.

I am running the SP for all the products in a listbox.

So for each product i am having the feature extracted through the SP

But some features are the same for 2, 3 products.

So in the datatable, i am getting the featrues repeated.

IS there any way to eliminate the duplicates from datatable, from server side?

Hope i am not confusing.

Eg: product1 -- test1, test2, test3

product2 -- test2, test4

so the datatable has -- test1, test2, test3, test2, test4

-- i have to eliminate one test2 from this.

Any ideas?

Thanks

Can you just use a SELECT DISTINCT in your stored procedure or whatever you generate your datatable from?

Jeff

Effective Implementation

Hi,
I am in the process of evaluating the existing Implementation of Fulltext
Index in our product(sql server 2000).We have full text index (text type) in
a table with out timestamp column.The existing settings for creation and
maintenance are ,
sp_fulltext_catalog 'test_catalog', 'create'
sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
sp_fulltext_column 'test', 'value', 'add'
sp_fulltext_table 'test', 'activate'
sp_fulltext_catalog 'test_catalog', 'start_incremental'
I think the above setting should populate the Index.
And then they have a process which is invoked every 2 minutes and does the
following.My contention is this will ne never invoked ..If yes , then how do
i implement incremental indexing ...If no , pls tell me the scenarions under
which this will get invoked...(assume usingChangeTracking=true)
boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
if (usingChangeTracking) {
//check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
turn it on
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextChangeTrackingOn')") == 0) {
KanaPrint.println("Start change tracking for Microsoft FullText
Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_change_tracking'"});
}
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextBackgroundUpdateIndexOn')") == 0) {
KanaPrint.println("Start background updating for Microsoft
FullText Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_background_updateindex'"});
}
return;
}
Rect
Rect,
If I correctly understand that "test_catalog" is the true name of your FT
Catalog for your FT-enabled table "test", then there is a difference between
the code examples you provided. Specifically, your code runs an Incremental
Population against the FT Catalog "Test", while the Change Tracking with
Update Index in Background is operating on the FT-enabled table "Test"
within the FT Catalog "test_catalog":
sp_fulltext_catalog 'test_catalog', 'start_incremental'
-- vs.
sp_fulltext_table 'test', 'Start_change_tracking'
sp_fulltext_table 'test', 'Start_background_updateindex'
Additionally, as your table does not have a timestamp column, when you start
an Incremental Population or enable Change Tracking, what is executed is a
Full Population (see BOL title "sp_fulltext_table" and under
start_change_tracking - "If the table does not have a timestamp, start a
full population of the full-text index". Furthermore, it is not necessary to
invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
is changed.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type)
in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how
do
> i implement incremental indexing ...If no , pls tell me the scenarions
under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
>
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
hangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Thanks John for your reply.
Does that mean the followig scripts run once , would do the job.Pls
confirm... I did some cut & paste job to hide my original table name..I am
attaching here the original scripts..
sp_fulltext_database 'enable'
sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
'kc_rawtext_pk'
sp_fulltext_column 'kc_rawtext', 'value', 'add'
sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
--Not required right ?..
And then one time invokation of the following..
sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
Also tell me if i am wrong , restart of mssearch will cause full population
of the catalogs...?
Rect
sp_fulltext_table 'kc_rawtext', 'activate'
Pls review the same and give your feedback..
"John Kane" wrote:

> Rect,
> If I correctly understand that "test_catalog" is the true name of your FT
> Catalog for your FT-enabled table "test", then there is a difference between
> the code examples you provided. Specifically, your code runs an Incremental
> Population against the FT Catalog "Test", while the Change Tracking with
> Update Index in Background is operating on the FT-enabled table "Test"
> within the FT Catalog "test_catalog":
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
> -- vs.
> sp_fulltext_table 'test', 'Start_change_tracking'
> sp_fulltext_table 'test', 'Start_background_updateindex'
> Additionally, as your table does not have a timestamp column, when you start
> an Incremental Population or enable Change Tracking, what is executed is a
> Full Population (see BOL title "sp_fulltext_table" and under
> start_change_tracking - "If the table does not have a timestamp, start a
> full population of the full-text index". Furthermore, it is not necessary to
> invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
> is changed.
> Regards,
> John
>
> "Rect" <Rect@.discussions.microsoft.com> wrote in message
> news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> in
> do
> under
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
> hangeTracking"));
>
>
|||Awaiting your feedback John.
Rect
"Rect" wrote:

> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Rect,
Yes, that is what I mean. Specifically that running the "Change Tracking"
and "Update Index in Background" should be only run once. However, I'd also
recommend that you alter the table and add a timestamp column and then set
the CT with UIiB first without running a Full Population as setting the CT
with UIiB will run automatically run a Full Population on an un-populated FT
Catalog or if it is populated, it will run an Incremental Population, but in
your case without a timestamp column it will run a Full Population.
There is no need to run both an Incremental Population when you have CT with
UIiB set, unless you have massive (>50%) updates to the table. See BOL title
"Maintaining Full-Text Indexes" for more details on when to use turn off
UIiB and use an Incremental or Full Population with Change Tracking.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:72C752D9-99A7-4B29-B21E-628B31F3E70B@.microsoft.com...
> Thanks John for your reply.
> Does that mean the followig scripts run once , would do the job.Pls
> confirm... I did some cut & paste job to hide my original table name..I am
> attaching here the original scripts..
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
> sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
> 'kc_rawtext_pk'
> sp_fulltext_column 'kc_rawtext', 'value', 'add'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
> --Not required right ?..
> And then one time invokation of the following..
> sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
> sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
> Also tell me if i am wrong , restart of mssearch will cause full
population[vbcol=seagreen]
> of the catalogs...?
> Rect
>
> sp_fulltext_table 'kc_rawtext', 'activate'
>
> Pls review the same and give your feedback..
>
>
>
> "John Kane" wrote:
FT[vbcol=seagreen]
between[vbcol=seagreen]
Incremental[vbcol=seagreen]
start[vbcol=seagreen]
a[vbcol=seagreen]
necessary to[vbcol=seagreen]
it[vbcol=seagreen]
Fulltext[vbcol=seagreen]
type)[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
how[vbcol=seagreen]
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC[vbcol=seagreen]
FullText[vbcol=seagreen]
'test',[vbcol=seagreen]
'test',[vbcol=seagreen]
|||Thanks John.I shall remove the code for start_incermental portion and rest
will be run as one time activity for the fresh set-ups.Altering the table to
have timestamp column will be big exercise as many of the customers are using
this option already , as it will involve code changes also on the application
front.
Regards
Rect
"Rect" wrote:

> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>