How do I use TOP instead of them LIMIT keyword in CakePHP with MSSQL?

CakePHP constructs all its queries ending with "LIMIT X", which is the MySQL syntax, not plain SQL and Microsoft SQL Server won't accept it. Even if yo开发者_如何转开发u're using the ODBC driver it'll generate queries like:

SELECT "Content"."id" AS "Content_dot_id", 
"Content"."name" AS "Content_dot_name" 
FROM "contents" AS "Content"  WHERE (1=1) LIMIT 1

Which gives:

37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'LIMIT'.

Because you want:

SELECT TOP 1 "Content"."id" AS "Content_dot_id", 
"Content"."name" AS "Content_dot_name"
FROM "contents" AS "Content"  WHERE (1=1) 

The code that takes on the "LIMIT" is in dbo_source.php rather than one of the DB-specific files (I think it's function limit($limit, $offset = null)).

Is there some setting I can toggle to switch to the TOP syntax instead of LIMIT?

The resolution that I found from a Google cached version of an old CakePHP trouble ticket was "Use the MSSQL dbo".

So what you need to do is to configure CakePHP to use the MSSql Driver and it will start using the "TOP" syntax. Here is a blog post talking about how to do that: http://www.johnmckinzie.com/2007/06/29/sql-server-2005-and-cakephp/