Hello,

I'm trying to execute a recursive query in SqlHelper. The following code works without SqlHelper:

string sql = string.Format(@"
    WITH UmbracoResults (nodeId, alias, masterContentType) AS
        (
            -- Base case
            SELECT c.nodeId, c.alias, c.masterContentType FROM cmsContentType c
            WHERE nodeId = 1062

            UNION ALL

            -- Recursive step
            SELECT c.nodeId, c.alias, c.masterContentType
            FROM cmsContentType c
                INNER JOIN UmbracoResults -- Note the reference to CTE table name
                    ON c.nodeId = UmbracoResults.masterContentType
        )

    SELECT * FROM UmbracoResults");

//Get the data from the database.
SqlConnection sqlConn = new SqlConnection(GlobalSettings.DbDSN);
sqlConn.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, sqlConn);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
sqlConn.Close();

This is just a sample, but it works. If I try this in SqlHelper I get an exception:

string sql = string.Format(@"
    WITH UmbracoResults (nodeId, alias, masterContentType) AS
        (
            -- Base case
            SELECT c.nodeId, c.alias, c.masterContentType FROM cmsContentType c
            WHERE nodeId = 1062

            UNION ALL

            -- Recursive step
            SELECT c.nodeId, c.alias, c.masterContentType
            FROM cmsContentType c
                INNER JOIN UmbracoResults -- Note the reference to CTE table name
                    ON c.nodeId = UmbracoResults.masterContentType
        )

    SELECT * FROM UmbracoResults");

using (IRecordsReader dr = SqlHelper.ExecuteReader(sql))
{
    while (dr.Read())
    {
        //Do stuff
    }
}

This give the following exception:

Exception: {"Umbraco Exception (DataLayer): SQL helper exception in ExecuteReader"}
InnerException:    {"Incorrect syntax near '('."}    System.Exception {System.Data.SqlClient.SqlException}

I would like this query to work with SqlHelper because it's for a package and the code using the first sample propably won't work on all databases.

Jeroen