It doesn't support UNION ALL, Views, Stored procedures, temp tables. However, it does support all type of joins, sub queries.

I understand, this is annoying, but that where the issue is....

Limitation I was referring is from front end side, which as I mentioned generates the SQL dynamically. The front end is not having this framework and does not support creating...

Using Union All is limitation and hence the problem.

This SQL is build dynamically by front end and it has limitation wherein it is not supporting Union All (views, Stored...

http://stackoverflow.com/questions/2099659/sql-server-2005-deadlock-with-nonclustered-index

above link has some more details which helped me understand this issue.

thanks WayneS.

here is some more info...

Q.This is the harder question to answer. Are these particular queries doing any scans? (It doesn't seem like they would be.)

A> Nothing unusual as such....

selecting results to file solved the problem.

However after going through the link provided by you, it seems that the limit is 33 mil records that can be dispalyed in SSMS....