Hello all, There are a number of issues in the way the SQL Server dialects try to apply limits to SQL queries. For any query that does not start with a SELECT a NotSupportedException is thrown, which prevents the following queries from being executed with limits:
1. Queries starting with WITH to define a common table expression; 2. Queries starting with comments; 3. Queries that use stored procedures to retrieve results. For scenario's 1 and 2 the SQL server dialects can be modified to become smarter in their query analysis, maybe assisted by returning more information from the SqlTokenizer that already is used by these dialects. However scenario 3 requires modification of the Loader and/or CustomLoader classes, as in this case the limits will have to be applied client-side by NHibernate. The simplest solution I can currently think of is to allow Dialect.GetLimitString() to return a null value to indicate that a limit string cannot be generated, in which case the Loader should enforce the application of limist by NHibernate itself. Any other ideas? Regards, Gerke.
