Bug in ISNUMERIC ?

strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :

SELECT (ISNUMERIC('0E010101'))

returns "1"

but

SELECT CAST ('0E010101' AS numeric)

returns "Error converting data type varchar to numeric"

any idea?ISNUMERIC tests whether a value can be converted to a number of any type.

CAST ('0E010101' AS numeric) attempts to specifically convert the string to the SQL NUMERIC data type.|||I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.|||No, same happens with D.
I thought it could be Hexadecimal, but it doesn't seem to work with ABCFGH
?

Originally posted by Ruprect
Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

Could Be|||Originally posted by Ruprect
I think he was addressing the fact that there was an 'E' in the numeric string.

Hypothesis:
the E is recognized as the exponent value from a float.
so it includes it.

I dont know but it sounds good to me.

I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||try SELECT (ISNUMERIC('0D010101'))

Originally posted by Ruprect
I tried the following statement and it gives a 0 so i am going with my Hypothesis
SELECT (ISNUMERIC('0A010101'))|||The strange thing is that SELECT (ISNUMERIC('0DE010101')) returns 0
but
SELECT (ISNUMERIC('0D010101'))
or
SELECT (ISNUMERIC('0E010101'))
return 1

Originally posted by Tontxu
try SELECT (ISNUMERIC('0D010101'))|||Books Online ISNUMERIC
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.

i even tried different combiantions and added an e to the end it appears that it's reading the values with on char in the second position
no i need to create a script that trys a brute force on every combination

nahhh|||I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||i retracted my d statement
i thought that it could have been indicative of some type of money
danish kroners or whatever they buy their crap with.|||the only thing I know is that it's not only the second position as my initial problem was with
SELECT (ISNUMERIC('0102D121'))

my problem was :

DECLARE @.Temp nvachar(25)

SELECT @.Temp = '0000D121'
SELECT
CASE
WHEN ISNUMERIC (@.Temp) THEN CONVERT(nvarchar(20),convert(numeric,@.Temp))
ELSE @.Temp
END

the problem is that @.Temp is gonna be a colum from a table so I can predict the values and I'm trying to remove the leading '0'

Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something...|||there is a bug with converting to numeric.

try:

select convert(float,1.7256e+06)
select convert(float,'1.7256e+06')
select convert(numeric(38,19),1.7256e+06)
select convert(numeric(38,19),'1.7256e+06')

in query analyzer...

Bill|||Originally posted by rokslide
I don't know much about number but I think Ruprect is right on this one...

I don't know what that D stands for either,.. but apparently it stands for something... Great zot! No old C programmers around anymore, or what ?!?!

E is the exponent introducer for real (float*4, 7 digit precision) values. D is the introducer for double (float*8, 53 digit precision) values. M is a suffix for (packed) decimal, and even IsNumeric() fails to process M correctly.

-PatP|||A history lesson!

Do old C programmers ever die? Or does their legacy go on?|||Damn...learned something new again...|||I Win!!!!

and i guessed

we are the champions! We Are the champions!Playing in the background|||Now that you have won, Ruprect, you can keep your prize and walk away, or you can swap your prize for what is in thread number 3. ;-)|||Hold on! I demand a recount.|||winning is more than actually doing something.. it's making everyone believe that you did something and first no less.

and welll keep on fighting til the end... ba bummmm:o
we are the champions :o
we are the champions :o
:o
of the world......:o

sorry. it must be all the cough syrup i had at lunch today...

:p

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:

> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP
).
> Has anyone else seen this?
>|||Nothing yet. If you find anything out, please post. This problem is annoyi
ng.
"Bjorn L" wrote:
[vbcol=seagreen]
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
>|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
[vbcol=seagreen]
> Nothing yet. If you find anything out, please post. This problem is anno
ying.
> "Bjorn L" wrote:
>

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?
We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:

> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> Has anyone else seen this?
>
|||Nothing yet. If you find anything out, please post. This problem is annoying.
"Bjorn L" wrote:
[vbcol=seagreen]
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
[vbcol=seagreen]
> Nothing yet. If you find anything out, please post. This problem is annoying.
> "Bjorn L" wrote:

BPA - Clustered SQLServer

Are there any known issues with running BPA against a clustered SQL Server instance?
What's BPA?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||SQL Server Best Practices Analyzer.
-Sue
On Mon, 29 Mar 2004 06:36:07 -0800, Mark Allison
<marka@.no.tinned.meat.mvps.org> wrote:

>What's BPA?
|||Sue Hoegemeier wrote:
> SQL Server Best Practices Analyzer.
>
Thanks! :-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||No issue that I'm aware of.
- Christian
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:C8426F85-5337-4C92-94EB-E71C22FD98AF@.microsoft.com...
> Are there any known issues with running BPA against a clustered SQL Server
instance?