Reading data from SQL Server from MS Access

Wow ! I have the way to pull data from SQL server .. sitting in the MS Access application. Thanks to the ODBC linking views available.

Hierarchical queries (or recursive queries) in SQL Server 2005/2008

The question is ... how do you run hierarchical queries in SQL server ? I was trying to find something similar to CONNECT BY..PRIOR clause in Oracle. We stepped in to CTE (Common Table Expression). Awesome feature !

* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)

WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
    SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr    
    WHERE mgr_id is null


    UNION ALL
-- Recursive member definition
    SELECT e.mgr_id, e.emp_id,Level + 1
    FROM test_emp_mgr AS e
    
    INNER JOIN DirectReports AS d
        ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;