I assumed that you are running executing this on web application with SQL 2008.
By default, SQL 2008 setting for memory is only 256MB. Thus when it reach its limit, there is not enough memory to run the query.
Try make changes on the SQL configuration manager start up. And used -g384 and restarted it. Now it will provide 384MB for the physical memory allocation. If that's not enough try used slightly higher. Do not use too much cause it will be wasting of physical memory space.