I'm confused over the last sentence in this quote from the SQL Server 2005 Books online:
Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers and data modification statements such as INSERT, UPDATE, and DELETE. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution and primarily with the SELECT statement.
I have a SELECT query that is bombing on a CAST(myString AS FLOAT). I can't find the culprit causing this error and reckoned I could narrow the search using SET ROWCOUNT and progressively increasing the number of rows to be processed until the error occurs. Problem is the query takes ~5 minutes to run when it usually completes in a number of seconds (without the CAST). Should the last sentence in the quote above rather read "This option should be used with caution as it sucks ass."
So I'm still looking for a way to efficiently identify the row causing errors in a SELECT query. Any suggestions? Note I've tried the WHERE ISNUMERIC, REPLACE(..,',',''), LTRIM(RTRIM(...)), etc options to ensure myString can be converted to a FLOAT but it is difficult to know what else to cater for if you don't know what is causing the problem. Any suggestions?