I've been getting a lot of access violations in SQL Server 2005 SP2 recently. I cannot say with certainty what the cause is, but I strongly suspect the presence of CROSS APPLY in queries of moderate complexity. In every case, removing the CROSS APPLY made the problem go away. Furthermore, the queries would work correctly in SQL Server 2008. I have yet to successfully use CROSS APPLY in anything other than a trivial case.
The access violation causes entries in the SQL Server error log that look like this:
2009-04-03 13:59:58.80 spid53 * Exception Address = 012828F6 Module(sqlservr+002828F6) 2009-04-03 13:59:58.80 spid53 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 2009-04-03 13:59:58.80 spid53 * Access Violation occurred writing address 00000000