Developers are calling @@ROWCOUNT after their queries to give them a total count of rows returned. They use this in the front-end screen to tell the users the total rows return whilst they are paging throw the result set.
They are finding issues with @@ROWCOUNT which returns zero for various queries based on
a) total data returned
b) total rows returned
for example, one query will return say 100 rows with XYZ where clause criteria and @@ROWCOUNT reports this correctly. We change the where clause, thus returned over 294 rows and now @@ROWCOUNT is returning zero!?
It doesnt seem to be related to the total rows returned, but also affected by the size (amount of data) returned from the query. For example, another query will report @@ROWCOUNT correctly after 1000 rows, but not after 2345 rows!?
They are using ADO recordsets via COM+ (component services). They open the record set, run the query, close then check @@ROWCOUNT. As mentioned, it works sometimes and others not. Its like ADO or SQLServer is executing another job after the SQL statement, but when tracing via PROFILER, they are executed one after the other (query then then rowcount check).
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"