Help with cascading SELECT

Hello
I have the following scenario that I need to address with which I would
appreciate some help if anyone has the time:
(Simply) I have an asp.net application:
I have 2 tables:
Table 1 is called Categories and is used to hold hierarchical data (Tiers) -
these Tiers can be altered by the users. There may be 10 Categories in Tier
1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will have
sub-categories of Tier 2 etc. etc. through to Tier 5
Table 2 is called Jobs and holds information pertaining to a specific task
that is categorised by Table 1, so this would have single entries that you
should be able to back-track through to Tier 1 of Table 1
This structure is to simply organise data in a highly retrievable structure.
So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs
that are under that category regardless of the sub-category structure
underneath, a click on a category in Tier 2 will filter these, a click on a
sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you ge
t
the picture)
When a category or a job is defined and entered by the user a unique numeric
reference is automatically assigned to that record - it also records the
unique reference of the category above in a field called "Parent_Ref". I
should therefore be able to take the parent record from a Job (which is the
end of the line) and track this back to a sub-category in Tier 3, 4, or 5,
this category record will also have a parent which goes back to Tier 2 and
this will have a recod that tracks back to Tier 1
When the user initially clicks on a Tier 1 Category I need to do a search
for all Table 1 records that have the unique reference of that Tier 1
category in their "Parent_Ref" field.... for all returned records I need to
then do a search through Table 2 for all Jobs that are associated directly
with that category. This will give me 0 to x returns from Jobs. I then need
to requery and find all records from Tier 3 in Table 1 that have one of the
unique references from the Tier 2 records that were just returned in their
"Parent_Ref" field then retrieve all Jobs that relate to these retrieved
records and loop through all the Categories in Table 1 until no more returns
are possible
I hope this is clear !?! I'm sure this is a standard scenario, I have
researched the Select options in transact SQL but can not see the light. I
appreciate that this will probably need to be a stored procedure that will b
e
triggered by my asp.net application where I simply pass the unique ref in to
SQL and let it do the search, but I do not know how to cascade the queries i
n
the above fashion
Thanks
StuartStuart
Can you post DDL+ sample data + expected result?
Like
CREATE TABLE jobs
(
...
...
)
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:133CA7C6-2A90-46C9-94A3-07C51F277741@.microsoft.com...
> Hello
> I have the following scenario that I need to address with which I would
> appreciate some help if anyone has the time:
> (Simply) I have an asp.net application:
> I have 2 tables:
> Table 1 is called Categories and is used to hold hierarchical data
> (Tiers) -
> these Tiers can be altered by the users. There may be 10 Categories in
> Tier
> 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will
> have
> sub-categories of Tier 2 etc. etc. through to Tier 5
> Table 2 is called Jobs and holds information pertaining to a specific task
> that is categorised by Table 1, so this would have single entries that you
> should be able to back-track through to Tier 1 of Table 1
> This structure is to simply organise data in a highly retrievable
> structure.
> So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs
> that are under that category regardless of the sub-category structure
> underneath, a click on a category in Tier 2 will filter these, a click on
> a
> sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you
> get
> the picture)
> When a category or a job is defined and entered by the user a unique
> numeric
> reference is automatically assigned to that record - it also records the
> unique reference of the category above in a field called "Parent_Ref". I
> should therefore be able to take the parent record from a Job (which is
> the
> end of the line) and track this back to a sub-category in Tier 3, 4, or 5,
> this category record will also have a parent which goes back to Tier 2 and
> this will have a recod that tracks back to Tier 1
> When the user initially clicks on a Tier 1 Category I need to do a search
> for all Table 1 records that have the unique reference of that Tier 1
> category in their "Parent_Ref" field.... for all returned records I need
> to
> then do a search through Table 2 for all Jobs that are associated directly
> with that category. This will give me 0 to x returns from Jobs. I then
> need
> to requery and find all records from Tier 3 in Table 1 that have one of
> the
> unique references from the Tier 2 records that were just returned in their
> "Parent_Ref" field then retrieve all Jobs that relate to these retrieved
> records and loop through all the Categories in Table 1 until no more
> returns
> are possible
> I hope this is clear !?! I'm sure this is a standard scenario, I have
> researched the Select options in transact SQL but can not see the light. I
> appreciate that this will probably need to be a stored procedure that will
> be
> triggered by my asp.net application where I simply pass the unique ref in
> to
> SQL and let it do the search, but I do not know how to cascade the queries
> in
> the above fashion
> Thanks
> Stuart|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||
"Uri Dimant" wrote:

> Stuart
> Can you post DDL+ sample data + expected result?
> Like
> CREATE TABLE jobs
> (
> ...
> ....
> )
>
>
> "Stuart" <Stuart@.discussions.microsoft.com> wrote in message
> news:133CA7C6-2A90-46C9-94A3-07C51F277741@.microsoft.com...
>
>|||Stuart
'?
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:147912F5-39C6-4868-B368-ED57D26CC465@.microsoft.com...
>
> "Uri Dimant" wrote:
>|||Thanks very much - seems to be just the thing ! - but in your experience
would this function in an acceptable way with a max of 1000 entries in the
table ?
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>
> ML
> --
> http://milambda.blogspot.com/|||Sorry Uri - I spent some time composing a complete response to your request
only to have this interface bomb out when I posted it ! I appreciate your
time, but the answer from ML actually answers my question...
"Uri Dimant" wrote:

> Stuart
> '?
>
>
> "Stuart" <Stuart@.discussions.microsoft.com> wrote in message
> news:147912F5-39C6-4868-B368-ED57D26CC465@.microsoft.com...
>
>|||The performance of the function depends on two facts:
1) the number of rows in the table; and
2) the average depth of hierarchies.
The best way to know would be to test it on your system. It should peform
adequately with several thousands of rows with an average depth of about ten
levels. When I say "adequatly" that does not mean "perfectly". In SQL 2005
the function should be redesigned using a recursive common table expression
(rCTE), which might improve performance, since it's built into the SQL Serve
r
engine and requires less CPU time to execute.
ML
http://milambda.blogspot.com/

Help to Configure SQL 2005 to listen on a specific Port

I am trying to configure my port for my SQL 2005 named instance. When
I look under the TCP/IP properties dialog box on the IP Address tab
in the see several IP addresses in format IP1,IP2,IP3 and finally
IPALL. Which one am I suppose to configure? When I look at the IP
addresses I don't see the virtual IP address for my SQL Server
Instance. Can anyone help me on this.
I looked at BOL and it says "Right-click each address, and then click
Properties to identify the IP address that you wish to configure." But
right cliking on the address does not give me the properties option.
The server that I am working with is in a Cluster.
ThanksThe multiple IP addresses indicate you have multiple network connections.
You can set a different port for each connection OR you can set the same
port on all connections. I would use the 'ALL' option on a cluster.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> The multiple IP addresses indicate you have multiple network connections.
> You can set a different port for each connection OR you can set the same
> port on all connections. I would use the 'ALL' option on a cluster.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "shub" <shubt...@.gmail.com> wrote in message
> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>
> >I am trying to configure my port for my SQL 2005 named instance. When
> > I look under the TCP/IP properties dialog box on the IP Address tab
> > in the see several IP addresses in format IP1,IP2,IP3 and finally
> > IPALL. Which one am I suppose to configure? When I look at the IP
> > addresses I don't see the virtual IP address for my SQL Server
> > Instance. Can anyone help me on this.
> > I looked at BOL and it says "Right-click each address, and then click
> > Properties to identify the IP address that you wish to configure." But
> > right cliking on the address does not give me the properties option.
> > The server that I am working with is in a Cluster.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for your help. As far as deciding the port number itself do you
follow any guidelines or is there any guidelines by Microsoft?|||For whatever reason, SQL2005 Configuration Manager does NOT list the virtual
server IP address used by the SQL instance. I have no idea why.
But if ListenAll is enabled, you can use Configuration Manager to set TCP
Port under IP All on the Ip Address tab to a port of your choice, and upon
restart the instance the port will be used. Also, you only need to do this on
one of the nodes, and the cluster service will replicate the setting to the
other nodes.
Linchi
"shub" wrote:
> I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||I either use the autoconfig port and lock it in or I pick something that
isn't in use.
I don't recall any guidelines for this from Microsoft.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193279519.381435.155240@.i13g2000prf.googlegroups.com...
> On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> The multiple IP addresses indicate you have multiple network connections.
>> You can set a different port for each connection OR you can set the same
>> port on all connections. I would use the 'ALL' option on a cluster.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>>
>> >I am trying to configure my port for my SQL 2005 named instance. When
>> > I look under the TCP/IP properties dialog box on the IP Address tab
>> > in the see several IP addresses in format IP1,IP2,IP3 and finally
>> > IPALL. Which one am I suppose to configure? When I look at the IP
>> > addresses I don't see the virtual IP address for my SQL Server
>> > Instance. Can anyone help me on this.
>> > I looked at BOL and it says "Right-click each address, and then click
>> > Properties to identify the IP address that you wish to configure." But
>> > right cliking on the address does not give me the properties option.
>> > The server that I am working with is in a Cluster.
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks for your help. As far as deciding the port number itself do you
> follow any guidelines or is there any guidelines by Microsoft?
>

Help setting up Net Send operator

I get this error every time I try to test Net Send operator:
Failed to Net Send to the address "xxxxxxx".
I've tried multiple addresses, including my network sign-in name, computer
name.
Please help, thank you in advance for your time!
Leon ShargorodskyIs the messenger service installed and running on the machine you are trying
to use net send?
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
> I get this error every time I try to test Net Send operator:
> Failed to Net Send to the address "xxxxxxx".
> I've tried multiple addresses, including my network sign-in name, computer
> name.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||I'm able to use Net Send after enabling and starting Messenger service!
Thanks a lot for your help, Simon!
"Simon Worth" wrote:
> Is the messenger service installed and running on the machine you are trying
> to use net send?
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
> > I get this error every time I try to test Net Send operator:
> >
> > Failed to Net Send to the address "xxxxxxx".
> >
> > I've tried multiple addresses, including my network sign-in name, computer
> > name.
> >
> > Please help, thank you in advance for your time!
> >
> > Leon Shargorodsky
>
>

Help setting up Net Send operator

I get this error every time I try to test Net Send operator:
Failed to Net Send to the address "xxxxxxx".
I've tried multiple addresses, including my network sign-in name, computer
name.
Please help, thank you in advance for your time!
Leon Shargorodsky
Is the messenger service installed and running on the machine you are trying
to use net send?
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
> I get this error every time I try to test Net Send operator:
> Failed to Net Send to the address "xxxxxxx".
> I've tried multiple addresses, including my network sign-in name, computer
> name.
> Please help, thank you in advance for your time!
> Leon Shargorodsky
|||I'm able to use Net Send after enabling and starting Messenger service!
Thanks a lot for your help, Simon!
"Simon Worth" wrote:

> Is the messenger service installed and running on the machine you are trying
> to use net send?
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
>
>

Help setting up Net Send operator

I get this error every time I try to test Net Send operator:
Failed to Net Send to the address "xxxxxxx".
I've tried multiple addresses, including my network sign-in name, computer
name.
Please help, thank you in advance for your time!
Leon ShargorodskyIs the messenger service installed and running on the machine you are trying
to use net send?
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
> I get this error every time I try to test Net Send operator:
> Failed to Net Send to the address "xxxxxxx".
> I've tried multiple addresses, including my network sign-in name, computer
> name.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||I'm able to use Net Send after enabling and starting Messenger service!
Thanks a lot for your help, Simon!
"Simon Worth" wrote:

> Is the messenger service installed and running on the machine you are tryi
ng
> to use net send?
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:3AC999C9-A96E-4D79-AAD1-775D7B59FF92@.microsoft.com...
>
>