log backup overwrite: contains

Showing posts with label contains. Show all posts

Thursday, March 29, 2012

Does SQLSERVER support fuzzy text searching(like the function of a

functions are
SOUNDEX
DIFFERENCE
For freetext, try contains clause,
check CONTAINS in BOL
I hope this was what u were asking forThank you for your reply.
However, as I know, CONTAINS does not support the kind of fuzzy searching
described in my post.
And it is very likely that it does support it and I do not know how to use
it.
Can u show my a sample? Thank you again.
-- Original Message --
From: "Omnibuzz" <Omnibuzz@.discussions.microsoft.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Wednesday, April 19, 2006 7:00 PM
Subject: RE: Does SQLSERVER support fuzzy text searching(like the function
of a
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> д?:43C1F9E4-4CA3-478D-9064-82AA8D
9DA35D@.microsoft.com...
> functions are
> SOUNDEX
> DIFFERENCE
> For freetext, try contains clause,
> check CONTAINS in BOL
> I hope this was what u were asking for|||Thank you for your reply.
However, as I know, CONTAINS does not support the kind of fuzzy searching
described in my post.
And it is very likely that it does support it and I do not know how to use
it.
Can u show my a sample? Thank you again.
-- Original Message --
From: "Omnibuzz" <Omnibuzz@.discussions.microsoft.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Wednesday, April 19, 2006 7:00 PM
Subject: RE: Does SQLSERVER support fuzzy text searching(like the function
of a
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> д?:43C1F9E4-4CA3-478D-9064-82AA8D
9DA35D@.microsoft.com...
> functions are
> SOUNDEX
> DIFFERENCE
> For freetext, try contains clause,
> check CONTAINS in BOL
> I hope this was what u were asking for

Does SQL2005 still require all FTI keywords to match in the same column?

Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
Hi Thomas,
I am afraid SQL 2005 shows the same behavior. FTS is designed to search for text/documents, which normally are stored in the same column. Can you please explain a bit more why you need to have queries that involve several word matches for several columns?
Thanks!
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:33 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Does SQL2005 still require all FTI keywords to match in the same column?
Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
|||Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Hi Thomas,
I see your problem. Let me think about the best solutions.
Most of FTS users are focus in get great functionality to efficiently search inside a document, rather than to search parts in different documents stored in different columns. Anyway, for these cases like yours, we support multiple CONTAINS. Is true that
the performance is not as good as with one single CONTAINS but if we would allow from the beginning to have several columns look ups in a single CONTAINS, we would probably finish with similar performance even if you are just writing one clause.
The good news is the following.
-For next FTS release we have several architecture improvements that will improve dramatically the joined queries. This means that mix relational (date for instance) with FTS search will be efficient. Following your example, before look the FTS side, the
optimizer will get the few ones that pass the date filter and then these ones will be FT searched.
This improvement also will improve multiple CONTAINS queries, so you will not longer experiment pain.
-For now, the best you can do is to use computed columns. These columns will contain virtually the same data than the original columns and you can create a FT index on that column. The indexing time will take longer as you are merging 2 or more columns bu
t at query time you will be able to query efficiently and find what you look for.
Does this help?
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:34 PM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas
|||Hi Thomas,
When I say next FTS release I mean the next release, not any upgrade. And don’t worry, the next version should be no longer than 2007

In a following post I will let you know the steps to work with computed columns in SQL 2005.
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas [mailto:tomwinzig@.gmail.com]
Posted At: Friday, February 17, 2006 10:50 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas

Sunday, March 11, 2012

Does Link in Subscription Point to Live or Snapshot?

This should be an easy one...

You get an email that contains a link to a report. The email was sent automatically as part of a subscription.

Does that link point to a snapshot that was taken when the report was run, or does clicking the link cause the report to re-run altogether?

Thanks,
Dan

Hello Dan,

The link will always point to the current report. For instance, if you have an email subscription that goes out at 6am daily, and today at 8am, you open the report from the link in today's and yesterday's email, they both will take you to the most current report.

As for if it will be re-run, that depends on how you have the execution on the report setup. Whether it is 'Always run this report with the most recent data' or 'Render this report from a report execution snapshot' will determine this.

Hope this helps.

Jarret

|||

Hi Jarret,

That's exactly what I needed to know.

Thanks so much!

Dan

does Level() do anything?

enclosed is the complete rdl for a report that has a table, contains groups
and calls the level function.
Returns the current level of depth in a recursive hierarchy. it looks like
the recursive part does not work?
what am I missing here?
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.25in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>group name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="unique_name">
<Style>
<PaddingLeft>30pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>unique_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!unique_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif( Fields!unique_name.Value is Nothing,
"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>6.83334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="group_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!group_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="sub_group_name">
<Style>
<PaddingLeft>10pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>sub_group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!sub_group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!group_id.Value</Parent>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="family_name">
<Style>
<PaddingLeft>20pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>family_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!family_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox23</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif(Fields!family_name.Value=Nothing,"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox24</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!family_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!sub_group_id.Value</Parent>
</Grouping>
<Visibility>
<Hidden>= iif(Fields!family_name.Value=Nothing,True,False)</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="datasource">
<rd:DataSourceID>6f542575-d325-4bbe-8889-0ce5d6439aa6</rd:DataSourceID>
<DataSourceReference>datasource</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.95833in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="group_name">
<DataField>group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="group_id">
<DataField>group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="sub_group_name">
<DataField>sub_group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="sub_group_id">
<DataField>sub_group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="family_name">
<DataField>family_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="family_id">
<DataField>family_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_id">
<DataField>unique_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_name">
<DataField>unique_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>datasource</DataSourceName>
<CommandText>select 'group 1' as group_name, 1 as group_id, 'sub
group 1' as sub_group_name, 1 as sub_group_id, null as family_name, null as
family_id, null as unique_id, null as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
1 as unique_id, '1' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
2 as unique_id, '2' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
3 as unique_id, '3' as unique_name
union
select 'group 2' as group_name, 2 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
4 as unique_id, '4' as unique_name</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>eec2b812-624b-4368-8fcc-a696fe0d2f30</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Level function is for recursive hierarchies, which are tables that link back
to themselves. If you create a table such as...
CategoryID CategoryParent
1 0
2 1
3 1
4 0
5 4
6 4
7 4
8 2
9 2
10 2
11 9
12 10
13 12
and set your query to be
Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
Then you could use the level function to tell you how far each category node
is nested.
"letuce dance" wrote:
> enclosed is the complete rdl for a report that has a table, contains groups
> and calls the level function.
> Returns the current level of depth in a recursive hierarchy. it looks like
> the recursive part does not work?
> what am I missing here?
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1.25in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>group name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="unique_name">
> <Style>
> <PaddingLeft>30pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>unique_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!unique_name.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= iif( Fields!unique_name.Value is Nothing,
> "Null","Not Null")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>6.83334in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="group_name">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!group_id.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="sub_group_name">
> <Style>
> <PaddingLeft>10pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>sub_group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!sub_group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!group_id.Value</Parent>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="family_name">
> <Style>
> <PaddingLeft>20pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>family_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!family_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox23">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>|||ya I think I provided that example? an example that shows level() does not
work?
"Jace" wrote:
> Level function is for recursive hierarchies, which are tables that link back
> to themselves. If you create a table such as...
> CategoryID CategoryParent
> 1 0
> 2 1
> 3 1
> 4 0
> 5 4
> 6 4
> 7 4
> 8 2
> 9 2
> 10 2
> 11 9
> 12 10
> 13 12
> and set your query to be
> Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> Then you could use the level function to tell you how far each category node
> is nested.
>
> "letuce dance" wrote:
> > enclosed is the complete rdl for a report that has a table, contains groups
> > and calls the level function.
> >
> > Returns the current level of depth in a recursive hierarchy. it looks like
> > the recursive part does not work?
> >
> > what am I missing here?
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1.25in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>group name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="unique_name">
> > <Style>
> > <PaddingLeft>30pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>unique_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!unique_name.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>= iif( Fields!unique_name.Value is Nothing,
> > "Null","Not Null")</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>DataSet1</DataSetName>
> > <Width>6.83334in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="group_name">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="sub_group_name">
> > <Style>
> > <PaddingLeft>10pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!sub_group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox17">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox17</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox18">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox18</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!group_id.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>|||What query in your example is joined back to itself? I didnt see one. I
believe you have to have this for the level function to work.
"letuce dance" wrote:
> ya I think I provided that example? an example that shows level() does not
> work?
> "Jace" wrote:
> > Level function is for recursive hierarchies, which are tables that link back
> > to themselves. If you create a table such as...
> >
> > CategoryID CategoryParent
> > 1 0
> > 2 1
> > 3 1
> > 4 0
> > 5 4
> > 6 4
> > 7 4
> > 8 2
> > 9 2
> > 10 2
> > 11 9
> > 12 10
> > 13 12
> >
> > and set your query to be
> > Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> > outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> >
> > Then you could use the level function to tell you how far each category node
> > is nested.
> >
> >
> > "letuce dance" wrote:
> >
> > > enclosed is the complete rdl for a report that has a table, contains groups
> > > and calls the level function.
> > >
> > > Returns the current level of depth in a recursive hierarchy. it looks like
> > > the recursive part does not work?
> > >
> > > what am I missing here?
> > >
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <Report
> > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > <RightMargin>1in</RightMargin>
> > > <Body>
> > > <ReportItems>
> > > <Table Name="table1">
> > > <Height>1.25in</Height>
> > > <Style />
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox1">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>14</ZIndex>
> > > <rd:DefaultName>textbox1</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>group name</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox2">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>13</ZIndex>
> > > <rd:DefaultName>textbox2</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox3">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>12</ZIndex>
> > > <rd:DefaultName>textbox3</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Details>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="unique_name">
> > > <Style>
> > > <PaddingLeft>30pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>2</ZIndex>
> > > <rd:DefaultName>unique_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!unique_name.Value</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox5">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>1</ZIndex>
> > > <rd:DefaultName>textbox5</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>= iif( Fields!unique_name.Value is Nothing,
> > > "Null","Not Null")</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox6">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <rd:DefaultName>textbox6</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Details>
> > > <DataSetName>DataSet1</DataSetName>
> > > <Width>6.83334in</Width>
> > > <TableGroups>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="group_name">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>11</ZIndex>
> > > <rd:DefaultName>group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox11">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>10</ZIndex>
> > > <rd:DefaultName>textbox11</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox12">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>9</ZIndex>
> > > <rd:DefaultName>textbox12</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group1">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > > </GroupExpressions>
> > > </Grouping>
> > > </TableGroup>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="sub_group_name">
> > > <Style>
> > > <PaddingLeft>10pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>8</ZIndex>
> > > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!sub_group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox17">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>7</ZIndex>
> > > <rd:DefaultName>textbox17</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox18">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>6</ZIndex>
> > > <rd:DefaultName>textbox18</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group2">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > > </GroupExpressions>

Friday, February 24, 2012

Does a large amount 'image' data affect overall SQL performance?

Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.

Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.

Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?

If so, would moving this new table into it's own database be recommended?

Thanks

Rod

In order to determine the 'best' response to your question, you may find this article useful.

Images –Store in Database or Store in File System
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Friday, February 17, 2012

Document stored as image, problem with inflectional search

Hello,
I'm storing documents (txt, html, etc) as images in my database, and can
run regular 'contains' searches on my data just fine, but when I use
formsof(inflectional,word), it will only return results that contain the
word exactly, not any inflectional forms of it.
Is this some known issue about the way indexing is done or how SQL
processes images? Or could something be wrong on my end?
My @.@.version is:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks!
- Parhaum
Parhaum,
First of all, thank you for providing the @.@.version info as this is most
important in troubleshooting SQL FTS issues such as this one! Secondly, can
you post the exact formsof(inflectional,word) query & word or phrase that
you are having problems with along with the output of the following SQL
code?
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
There are several known words when used with the US English wordbreaker that
do not generate the correct or expected inflectional, see this thread
(http://www.webservertalk.com/archive.../t-965368.html) for more details.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Parhaum Toofanian via droptable.com" <forum@.nospam.droptable.com> wrote
in message news:141e567797a94b829e022eb7c3eb7ca2@.droptable.co m...
> Hello,
> I'm storing documents (txt, html, etc) as images in my database, and can
> run regular 'contains' searches on my data just fine, but when I use
> formsof(inflectional,word), it will only return results that contain the
> word exactly, not any inflectional forms of it.
> Is this some known issue about the way indexing is done or how SQL
> processes images? Or could something be wrong on my end?
> My @.@.version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> Thanks!
> - Parhaum
|||did you use the ms.locale metatag to indicate the type of language your html
docs are stored in?
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
"Parhaum Toofanian via droptable.com" <forum@.nospam.droptable.com> wrote
in message news:141e567797a94b829e022eb7c3eb7ca2@.droptable.co m...
> Hello,
> I'm storing documents (txt, html, etc) as images in my database, and can
> run regular 'contains' searches on my data just fine, but when I use
> formsof(inflectional,word), it will only return results that contain the
> word exactly, not any inflectional forms of it.
> Is this some known issue about the way indexing is done or how SQL
> processes images? Or could something be wrong on my end?
> My @.@.version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> Thanks!
> - Parhaum
|||First of all, thank you both for your quick replies! Regarding the locale,
I can probably check, but it's a database in my company that I'm developing
with, so I'd need to ask the DBA about it.
Second, background on the database. The table with the image data is
J_DOCU_CONTENT, and I have uploaded many files including 3 sample .txt
files with text like "park services" and "servicing the shuttle". There's
a table called T_DOCUMENT that contains some other information, but even
running the last query without the T_DOCUMENT information returns the same
result.
Trying to do an inflectional search on "service" returns 0 results. An
inflectional search on "services" returns the park services file, and so
on, as if it were a 'contains' exact match search.
:: Result of:
:: EXEC sp_help_fulltext_columns;
TABLE_OWNER<x>
TABLE_ID<id>
TABLE_NAMEJ_DOCU_CONTENT
FULLTEXT_COLUMN_NAMEDATA
FULLTEXT_COLID4
FULLTEXT_BLOBTP_COLNAMEDOCUMENT_TYPE
FULLTEXT_BLOBTP_COLID3
FULLTEXT_LANGUAGE0
:: Result of:
:: EXEC sp_help J_DOCU_CONTENT;
NameJ_DOCU_CONTENT
Owner<x>
Typeuser table
Created_datetime2005-04-14 15:38:09.013
Column_namePRIMARY_KEY
Typeint
Computedno
Length4
Prec10
Scale0
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
Column_nameVERSION
Typeint
Computedno
Length4
Prec10
Scale0
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
Column_nameDOCUMENT_TYPE
Typevarchar
Computedno
Length250
Prec
Scale
Nullableno
TrimTrailingBlanksno
FixedLenNullInSourceno
CollationSQL_Latin1_General_CP1_CI_AS
Column_nameDATA
Typeimage
Computedno
Length7000
Prec
Scale
Nullableno
TrimTrailingBlanks(n/a)
FixedLenNullInSource(n/a)
CollationNULL
No identity column defined.
No rowguidcol column defined.
constraint_type
PRIMARY KEY (clustered)
:: Result of:
:: SELECT t1.NAME FROM J_DOCU_CONTENT t0, T_DOCUMENT t1 WHERE
::(contains(t0.DATA, ' FORMSOF(INFLECTIONAL, service) ')
::AND
::(t0.PRIMARY_KEY = t1.CONTENT_ID))
NAME
(0 row(s) affected)
Message posted via http://www.droptable.com
|||Yikes. Tried to format it from the direct SQL output, and not sure now
which ended up looking worse...
Message posted via http://www.droptable.com

Document Map being truncated - why?

I've noticed that the document map for my Microsoft report contains enough entries to fill the height of the document map pane and no more. The document map pane is capable of displaying a vertical scroll bar so I'm not sure what is happening or why. I've played around with the obvious report properties such asInteractiveHeight.

Two questions:

1. Has anyone else had this problem?

2. Can I do anything to restore the missing groups to the document map?

Thanks.

BCB

It was my fault. It turns out that the vertical scroll bar that I expected was concealed beneath a second vertical scroll bar that was hiding the "real" vertical scroll bar. The bogus vertical scroll bar was caused by me setting the document map too narrow. Once I widened the document map the bogus scroll bar disappeared and - presto - the "real" vertical scroll bar was visible.

It probably sounds as clear as mud but check your document map width if you encounter this.

Tuesday, February 14, 2012

Do While Skip in Selecting...

Hello:

I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?

Here is a code snippet of what is going on:


 Do While varCount < varRecordCount
 conSqlConnect.Open()
 cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@.idnumber and date<@.date", conSqlConnect)
 cmdSelect.Parameters.Add( "@.accountnumber", "10000" )
 cmdSelect.Parameters.Add( "@.date", dtnow )
 dtrdatareader = cmdSelect.ExecuteReader()
 While dtrdatareader.Read()
 If IsDbNull(dtrdatareader("value")) = false Then
 varValue = dtrdatareader("value")
 End If
 End While
 dtrdatareader.Close()
 conSqlConnect.Close()'#####The information above is copied to another table here
'#####The record where the information was received is deleted.
 varCount = varCount + 1
 Loop

Any ideas?After playing around with this for a while, I found that the select statement is incorrect. The part where it says date<@.date... this selects more than one row, instead of just selecting one row.

I read articles that discuss using select MAX(column) but that will only return one column... how can I select a row based on a column with the maximum value?|||ok... great got it working now... just post the answer here for future reference... I went ahead and did Select Top 1 * instead of Select *... it solved everything...