Actually query is coming from analysis service package (building a dimension) and errored out there. I ran it in SSMS and received the error as indicated. In the past when I worked with large result sets usually they were placed in tempdb and/or paged. Is this a new M$ 'feature'.
PS: You know after thinking about it, I'm more disgrunted about the query failing in SSMS than getting the error in analysis services -- guess it is kind of dumb to have a dimension with 10.5m discrete values in a cube.