Using Coldfusion cfquery with Sql Server 2005
I have one query
cfquery name="myq" datasource="mydsn"
[B] set rowcount 5[/B]
select ...etc...
/cfquery
cfoutput outputs all 5 records as expected.
On another web page I omit the set rowcount 5 statement for a completely different query. But I get back only 5 results when I should get back about 20. Even different users/sessions are affected so it is possibly impacting a global variable.
Is there a setting on SQL SERVER or ColdFusion that causes this error? The set rowcount seems to affect every query after the first one is run until it meets a set rowcount 0 statement.
I do not have this problem on sybase database so I am thinking possibly SQL SERVER issue.