Using Coldfusion cfquery with Sql Server 2005
I have one query
cfquery name="myq" datasource="mydsn"
[B] set rowcount 5[/B]
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.
from your description, i'd say you've figured it out
i checked BOL but all it says is that SET statements take effect for the current session
maybe the coldfusion interface is a single session as seen from sql server
by the way, BOL also says you should be using TOP in the SELECT statement rather than setting the rowcount