Set Rowcount sticking to all queries

Databases
1

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.

2

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