Cannot create a row of size 11326 which is greater than the allowable maximum row size of 8060

I have the following SQl statement which join with a few #tables. I need to have all the columns from each #table. Unfortunately it consists of about 800 columns. When I execute the Select statement, I encounted the error, Cannot create a row of size 11326 which is greater than the allowable maximum row size of 8060.Some temp tables has couple varchar(max) fields and some may have 5 or 6. Does anyone know how to make the Select statement run successfully.

W

Sorry forget to post the sql statement.

Select * from #EmployeeInfo EI
left join #EmployeeTitle ET on EI.title = ET.title
left join #SpecialSkill ss on EI.Empl_ID = ss.Empl_ID
left join #workHist wh on EI.empl_id = wh.empl_Id
left join #Education ed on EI.empl_id = ed.empl_Id
order by EI.lastname, EI.firstname, EI.empl_ID

I think you work under SQL Server 2000 or DB compatibility Level 80 at least where you could find such limitation issues...

Hence, please try to change it to DB compatibility Level 90 or 100 and let me know the output

It was an good question which also prompt me for some reading. I got a blog from MSDN, hope this will help you and it also points out similar issue.

No need to paste what is written there. Please have a look, I think it make sense.
http://blogs.msdn.com/b/craigfr/archive/2009/06/24/maximum-row-size-and-query-hints.aspx

-Johnson