from this forum "discussion" (in fact it's a monologue): http://forum.hibernate.org/viewtopic.php?p=2398068#2398068

• the criteria

Criteria criteria = getActorDao().createCriteria();
criteria.add(Restrictions.or(
Restrictions.ilike("name", "adi"),
Restrictions.ilike("description", "adi")
));
criteria.setProjection(Projections.projectionList()
.add(Projections.groupProperty("name"), "name")
.add(Projections.groupProperty("description"), "description")
);
criteria.setResultTransformer(new AliasToBeanResultTransformer(ActorTestDTO.class));
List result = criteria.list();

• the generated SQL (show_sql=true):

select this_.name as y0_, this_.description as y1_
from Actor this_
left outer join Action this_1_ on this_.id=this_1_.id
left outer join Location this_2_ on this_.id=this_2_.id
left outer join UserGroup this_3_ on this_.id=this_3_.id
left outer join WebUser this_4_ on this_.id=this_4_.id
where (lower(y0_) like ? or lower(y1_) like ?)
group by this_.name, this_.description

• the Problem
  This generated SQL works on HSQLDB but NOT on MS-SqlServer.
  The wrong code:
  where (lower(y0_) like ? or lower(y1_) like ?)

The correct code:
where (lower(this_.name) like ? or lower(this_.description) like ?)

I verified this by issuing the sql directly in SQL-server.