We had an issue this week while migrating our databases from Sybase to SQL SERVER.
In sybase we would use SET ROWCOUNT 1 if we wanted to return only 1 record within a cfquery. Works just fine and it is ignored by future queries.
However if you use that syntax in SQL Server 2005, all the queries after the initial query are limited by the set rowcount statement.
It becomes a session variable. And in our case SQL Server sees our Coldfusion server as a single user so it continues to limit the records until it encounters a different value for SET ROWCOUNT.
Example:
<cfquery name="myq" datasource="mydsn">
SET ROWCOUNT 1
Select * from mytablename
vs
SELECT TOP 1 * from mytablename
We attacked this issue in two ways.
First we added to our main page header which is included on every CFM a cfquery with SET ROWCOUNT 0 (zero). This ensures that it will not carry over to the next page and gives us time to edit the CFMs with SET ROWCOUNT statements while we slowly migrate to SQL SERVER.
Second we began the process of finding all CFM files with the SET ROWCOUNT statement and converted the syntax to SELECT TOP n syntax which works in both Sybase and SQL SERVER.
Having a good Find/Replace tool is a bonus when doing these types of updates. Windows Explorer Search feature was not up to the task.
