BUG REPORT: SQL Server ISNUMERIC() not reliable in all cases

I have no idea how to submit this bug report to Microsoft, and I'm sick of
searching the site and ending up in the same place all the time. So hopefull
y
somebody can fwd this to them or one of their developers may stumble across
it.
Quote from Documentation:
ISNUMERIC
Determines whether an expression is a valid numeric type.
Syntax
ISNUMERIC ( expression )
Arguments
expression
Is an expression to be evaluated.
Return Types
int
Remarks
ISNUMERIC returns 1 when the input expression evaluates to a valid integer,
floating point number, money or decimal type; otherwise it returns 0. A
return value of 1 guarantees that expression can be converted to one of thes
e
numeric types.
The last statement is not correct. A return of '1' does not guarantee this.
Try:
ISNUMERIC('5') and you get 1, which is correct.
ISNUMERIC('5.0') and you get 1, which is correct.
ISNUMERIC('5,0') and you get 1, which is correct.
[may depend on your config. settings, using commas as decimal markers]
But the following also return 1, which is NOT correct:
ISNUMERIC('.')
ISNUMERIC(',')
as these cannot be converted to integers.
Thanks
Richard McSharry> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
Nobody said these could be converted to integers. "A return value of 1
guarantees that expression can be converted to *ONE* of these numeric
types" (my emphasis). Those strings WILL convert to MONEY:
SELECT CAST('.' AS MONEY)
SELECT CAST(',' AS MONEY)
ISNUMERIC is pretty useless most of the time but this is not a bug.

> I have no idea how to submit this bug report to Microsoft
Contact Product Support: http://support.microsoft.com/
However, you may want to post here first (Not everything that you think
is a bug will be :-) )
David Portas
SQL Server MVP
--|||select cast('.' as money)
Works for me.
From BOL:
A return value of 1 guarantees that the expression can be converted to ONE
of these numeric types.
Well, there is error in this statement. It should say ...to ONE OR MORE of
these numeric types.
"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So
> hopefully
> somebody can fwd this to them or one of their developers may stumble
> across
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> ISNUMERIC returns 1 when the input expression evaluates to a valid
> integer,
> floating point number, money or decimal type; otherwise it returns 0. A
> return value of 1 guarantees that expression can be converted to one of
> these
> numeric types.
>
> The last statement is not correct. A return of '1' does not guarantee
> this.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry|||"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So
> hopefully
> somebody can fwd this to them or one of their developers may stumble
> across
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> ISNUMERIC returns 1 when the input expression evaluates to a valid
> integer,
> floating point number, money or decimal type; otherwise it returns 0. A
> return value of 1 guarantees that expression can be converted to one of
> these
> numeric types.
>
> The last statement is not correct. A return of '1' does not guarantee
> this.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry
They can be converted to MONEY.|||Perhaps I'm missing something, but why is '.' a valid Money type value? I se
e
that it casts to .0000, but why? What is the logic behind allowing a period
(or
comma) by itself as a value?
Thomas
"Hyper" <Hyper@.discussions.microsoft.com> wrote in message
news:348D7AD8-0F29-44AC-B5BF-E07E09CF415C@.microsoft.com...
>I have no idea how to submit this bug report to Microsoft, and I'm sick of
> searching the site and ending up in the same place all the time. So hopefu
lly
> somebody can fwd this to them or one of their developers may stumble acros
s
> it.
> Quote from Documentation:
> ISNUMERIC
> Determines whether an expression is a valid numeric type.
> Syntax
> ISNUMERIC ( expression )
> Arguments
> expression
> Is an expression to be evaluated.
> Return Types
> int
> Remarks
> ISNUMERIC returns 1 when the input expression evaluates to a valid integer
,
> floating point number, money or decimal type; otherwise it returns 0. A
> return value of 1 guarantees that expression can be converted to one of th
ese
> numeric types.
>
> The last statement is not correct. A return of '1' does not guarantee this
.
> Try:
> ISNUMERIC('5') and you get 1, which is correct.
> ISNUMERIC('5.0') and you get 1, which is correct.
> ISNUMERIC('5,0') and you get 1, which is correct.
> [may depend on your config. settings, using commas as decimal markers]
> But the following also return 1, which is NOT correct:
> ISNUMERIC('.')
> ISNUMERIC(',')
> as these cannot be converted to integers.
> Thanks
> Richard McSharry|||>> Perhaps I'm missing something, but why is '.' a valid Money type
value? <<
It is a proprietary data type, so they can do anything they wish with
it. Yet another reason never to use a proprietary data type. I would
guess this is part of the old "Sybase Code Museum" that SQL Server
still has in it.|||Is the IsNumeric function part of the official ISO SQL specification? If so,
does that specification define the rules that determine a numeric value? Kee
p in
mind that in this case, even if you did not use a proprietary data type,
IsNumeric would still return what appears to be a bogus result.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113862850.783170.196580@.l41g2000cwc.googlegroups.com...
> value? <<
> It is a proprietary data type, so they can do anything they wish with
> it. Yet another reason never to use a proprietary data type. I would
> guess this is part of the old "Sybase Code Museum" that SQL Server
> still has in it.
>

Breakpoint doesn't work

I've got a breakpoint in a script task. The script task executes successfully but doesn't break.

Short and sweet this one. Anyone any idea why it might not?

-JamieHey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...|||

JAson_scoobyjw wrote:

Hey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...

Nah, I know this has worked in the past and I've read posts today from people that have had it working.

-Jamie|||We don't support breakpoints in script data flow component in this release.

The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.

Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).|||

Michael Entin SSIS wrote:

We don't support breakpoints in script data flow component in this release.

The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.

Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).

Hi Michael,
Yeah, I am using Sept CTP/IDW 16 and I do have PreCompile=TRUE.

The package is at the office and I am currently at home so I'll check this out (i.e. set PreCompile=FALSE) on monday.

-Jamie|||http://blogs.conchango.com/jamiethomson/archive/2005/10/15/2271.aspx|||I found that if you are in debug at a breakpoint on a looping bit of code. If you press F5 the code then continues and doesn't break on the break point again, even though the statement with the breakpoint is executed again.

I thought F5 ran the code and if a breakpoint is found it should stop?|||Is it true that breakpoints won't work in a script component?

I just wrote a script source component to reorder the columns in incoming CSV's based on the column names in the first row.

I set some break points to debug, but they get ignored every time I try to run it. My "precompile" flag is set to false per some earlier posts, but that doesn't seem to affect the issue.|||SSIS does not currently support breakpoints in script components.

You will need to put some logging information in your script component see my post http://www.sqljunkies.com/WebLog/simons/archive/2005/08/03/SSIS_Script_Component_Debugging.aspx