A way to recursively look up hierarchal data using a lookup table

I have found the Common Table Expressions described in SQL 2005 and I
am not sure if it applies to this situation.

Here are the tables

<PRE>
<B>ManagedServer Table</B>
--IdManagedServer (PK, int, not Null)
--Name (nvarchar(256), not null)

<B>ManagedServerToManagedServer Table</B>
--IdParentManagedServer (PK, int, not null)
--IdChildManagedServer (PK, int, not null)
</PRE
The following will give you the parent

-- Get Managed Server Group Names
LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
ms.IdManagedServer = mstms.IdChildManagedServer
LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
msg.IdManagedServer

How would you go about getting all of the "parents" in the tree?
Can this be done with CTEs? Unfortuately all of the examples found are
joining on itself.(patuww@.yahoo.com) writes:
> I have found the Common Table Expressions described in SQL 2005 and I
> am not sure if it applies to this situation.
> Here are the tables
><PRE>
><B>ManagedServer Table</B>
> --IdManagedServer (PK, int, not Null)
> --Name (nvarchar(256), not null)
><B>ManagedServerToManagedServer Table</B>
> --IdParentManagedServer (PK, int, not null)
> --IdChildManagedServer (PK, int, not null)
></PRE>
> The following will give you the parent
> -- Get Managed Server Group Names
> LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
> ms.IdManagedServer = mstms.IdChildManagedServer
> LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
> msg.IdManagedServer
> How would you go about getting all of the "parents" in the tree?
> Can this be done with CTEs? Unfortuately all of the examples found are
> joining on itself.

For this type of query, it is also a good idea to post:

o CREATE TABLE statement(s) for the involved table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This makes it easy to copy and paste and post a tested solution.

Judging from the table design, it appears that a child can have many
parents, which makes sort of interesting for the output.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

A transport-level error has occurred when receiving results from the server

Hi all,

I

am trying to run a stored procedure which retreives 3 lakhs of records

and updates the data and moves few of those records to some tables.

Since the number of records are more , the time taken for the storede

procedure is around 30 minutes when i directly execute it in query

analyzer.

But I need to execute it from Visula Studio.Net 2005

(c#). Whole of the application contains only one form with a single

button.When I click on the button , this stored procure has to be

executed. But I am getting an error as shown below.

"A

transport-level error has occurred when receiving results from the

server. (provider: TCP Provider, error: 0 - The specified network name

is no longer available.)"

Database used is SqlServer 2000

How to solve this error?. Any ideas are really appreciated.

Thanks and Regards,
Sukanya.

The error occured during data operation and the remote server either temporarily offline or close connection due to invalid client operation or your the permission your client to access the remote server resources has been changed, etc.

So, to identify the problem, you need :

1) Assume you were making remote connection, ping <remoteserver>, telnet <remoteserver> <sqlport>, or net view \\<remoteserver> or see firewall setting on the remote server to check whether the network is still good to make sure remote server is still reachable, and contact your network administrator to fix those problems.

2) You can give a retry by running your client app see whether the problem went away.

3) If 1) and 2) passed, you might open sql profile to nail down which client operation to cause sql server terminate connection, and check server errorlog or application event log find out any clue.

If you were making local connection, it is probably reason 3).

HTH.

Ming.

|||Hi,

I just increased the "connect timeout" in connectionstring of app.config to 1 hour. This solved my problem.

Thanks,
Sukanya