Results 1 to 4 of 4

Thread: Set Rowcount

  1. #1
    Join Date
    Jul 2008
    Posts
    16

    Unanswered: Set Rowcount

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are some problems with ISNUMERIC - scientific notations return true but can't be converted to numeric datatypes.
    http://sqlblogcasts.com/blogs/madhiv...-function.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2008
    Posts
    16
    Another one bites the dust.

    SELECT CAST('- 0.7' AS FLOAT)

    Fix: SELECT CAST(REPLACE('- 0.7','- ','-') AS FLOAT)

    Better fix:
    CREATE FUNCTION [dbo].[REPMINUS](@text varchar(8000))
    RETURNS varchar(8000)
    BEGIN
    WHILE (CHARINDEX('- ', @text) > 0)
    SET @text = REPLACE(@text,'- ','-')
    RETURN @text
    END

    SELECT CAST(dbo.REPMINUS('- 0.7') AS FLOAT)

    Geez, who would have thought. Even Excel handles this gracefully.
    Last edited by Andre Smit; 10-29-08 at 13:08.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I forgive SQL Server for being so strict when converting data types. I don't forgive ISNUMERIC() for being so bloody loose in its definition of numeric.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •