Problem with MsSql2000 and 2005 Dialects GetLimitString when using use_sql_comments=true

This should be fixed in 3.4 and 4.0 by NH-2977.

now method in class MsSql2000Dialect looks like this

private static int GetAfterSelectInsertPoint(SqlString sql)
{
if (sql.StartsWithCaseInsensitive("select distinct"))
{
return 15;
}
else if (sql.StartsWithCaseInsensitive("select"))
{
return 6;
}
throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
}

maybe this would be better?

private static int GetAfterSelectInsertPoint(SqlString sql)
{
int index = sql.StartsWithCaseInsensitiveIndex("select distinct");

if (index >= 15)
{
return index;
}
else
{
index = sql.StartsWithCaseInsensitiveIndex("select");
if (index >= 6)
{
return index;
}
}
throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
}

and method StartsWithCaseInsensitiveIndex in SqlString class could look like this

public int StartsWithCaseInsensitiveIndex(string value)
{
SqlString tempSql = Compact();
if (tempSql.Count == 0)
{
return -1;
}

string firstPart = tempSql.sqlParts[0] as string;
if (firstPart == null)
{
return -1;
}

string regexString = @"^(\/*[\s\w]*\/[\s])?" + value;

Match match = Regex.Match(firstPart, regexString, RegexOptions.IgnoreCase);

if (match.Success)
{
return match.Length;
}
else
{
return -1;
}
}

use_sql_comments=true is usefull with named queries when you read log files.

I actually just spent about an hour trying to figure out why I got an error message telling me, that my query should begin with SELECT or SELECT DISTINCT. I also got a NotSupportedException from somewhere inside NHibernate.
All I did was update NHibernate to 2.1.1.4000, after which every criteria with max result set, failed.
I don't think throwing a NotSupportedException is enough or even descriptive about the actual error. What is it that's not supported? How come a simple update of the assemblies makes such a huge impact on the way a configuration property works?

Fixed with 'Not supported exception.'