CodeIgniter 2 MSSQL query returns false instead of resource

Question

I'm new to CodeIgniter, one of the projects at my new job was built in CodeIgniter so I am sort of using pieces the previous programmer wrote as a basis.

Im trying to create a method in our orders_model that contains a query that I cannot get any results from.

I built the query directly in MS SQL Server Management Studio 08 and get over 200 rows there, but when I paste it into my method, the resulting resource of the query is false.

I copied this method from another one I created which works fine, even stripped out all the validation so it will simply run and return the query.

Here is the stripped down method:

public function get_part_index($pick_id, $return_data=true)
{
    $orders = $this->load->database('orders', true);

    $sql = "SELECT
                u.metraPartNumber ,
                STUFF(
                    (
                        SELECT
                            DISTINCT ', ' + ic.Box
                        FROM
                            orderScans.dbo.FullCaseScanTable AS ic JOIN upcCodeDatabase.dbo.upc AS iu
                                ON ic.FullUccCode = iu.fullUccCode
                        WHERE
                            u.PartNumber = iu.PartNumber
                            AND c.PickNumber = ic.PickNumber
                        GROUP BY
                            ic.Box FOR XML PATH('')
                    ) ,
                    1 ,
                    1 ,
                    ''
                ) AS BoxLocation
            FROM
                orderScans.dbo.FullCaseScanTable AS c JOIN upcCodeDatabase.dbo.upc AS u
                    ON c.FullUccCode = u.fullUccCode
            WHERE
                c.PickNumber = ?
                GROUP BY u.PartNumber ,
                c.PickNumber
            ORDER BY
                u.PartNumber";

    $query = $orders->query($sql, array($pick_id));

    /***********************************************
     * When I add die(var_dump($query)) here  I get (bool)false
     ***********************************************/

    if ($query !== FALSE) {
        $result['data'] = $query->result_array();
        $result['success'] = TRUE;
        $result['msg'] = NULL;
    } else {
        $result['data'] = NULL;
        $result['msg'] = "Pick ID '$pick_id' not found!";
        $result['success'] = FALSE;
    }

    return $result;
}

The only thing I can think of is perhaps the database class/adapter doesn't like something with the subquery?

Any help would be much appreciated! Thanks

Answer 1

After trying a view (which did the same exact thing...) I decided to pull the raw data and run through the data in php, putting it together how I wanted.