The advent of HierarchyID removed the adjaceny pairs pattern (MangerID) from the employee table in AdventureWorks 2008. The recursive CTE example in BOL still uses the old AdventureWorks structure, so the code won't run in AdventureWorks 2008. I know that recursive CTE's are yesterday's news, but they're still useful as far as I'm concerned. So, if you want to learn about them and play with them in AdventureWorks 2008, here's some code to make your life easier...
(note: The new AdventureWorks 2008 does not include the adjacency pairs ManagerID column, the contacts table is changed to Person table, and the ContactID is changed to BusinessEntityID.)
-- Recreate Adjacency Pairs Pattern
ALTER TABLE HumanResources.Employee
Add ManagerID INT;
go
-- Populate the new ManagerID Column using join condition to match
UPDATE E
SET ManagerID = M.BusinessEntityID
FROM HumanResources.Employee E
JOIN HumanResources.Employee M
ON M.OrganizationNode = E.OrganizationNode.GetAncestor(1);
-- This query demos the recursive CTE
WITH OrgPath (BusinessEntityID, ManagerID, lv)
AS (
-- Anchor
SELECT BusinessEntityID, ManagerID, 1
FROM HumanResources.Employee
WHERE ManagerID IS NULL -- should only be EmployeeID 1
-- WHERE EmployeeID = 1 -- the CEO
-- Recursive Call
UNION ALL
SELECT E.BusinessEntityID, E.ManagerID, lv + 1
FROM HumanResources.Employee E
JOIN OrgPath
ON E.ManagerID = OrgPath.BusinessEntityID
)
SELECT Emp.BusinessEntityID, Emp.JobTitle,
C.FirstName + ' ' + C.LastName AS [Name],
M.FirstName + ' ' + M.LastName AS [Manager], Lv
FROM HumanResources.Employee Emp
JOIN OrgPath
ON Emp.BusinessEntityID = OrgPath.BusinessEntityID
JOIN Person.Person AS C
ON C.BusinessEntityID = Emp.BusinessEntityID
Left Join Person.Person AS M
ON Emp.ManagerID = M.BusinessEntityID
ORDER BY Lv