April 10, 2009 at 3:46 am
Hi, all!
I have this query:
select LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS INT)-1))
On 32-bit machines it works fine (tested on 3 servers, SP2 CU#6, SP2 CU#8, SP3).
On 64-bit (3 machines, SP2 CU#6, CU#8, and SP3) it's failing (but not consistently) with the next error message:
"Invalid length parameter passed to the left function"
I am not trying to find a solution for it. I am trying to understand the root cause of the issue. Is it a sql
server bug? A configuration issue?
Please note that these queries work:
select LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS BIGINT)-1))
select LEFT(NULL,ABS(CAST(ISNULL(CHARINDEX('.',NULL),NULL) AS INT)-1))
I'm curious to know your opinions on this.
Thanks,
Monica
April 10, 2009 at 5:44 am
are you using the same ansi settings when executing on the different instances ?
What do you expect as a correct result ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 10, 2009 at 6:06 am
Yes, I was using the same ANSI_NULLS settings on all instances (default OFF).
For both ON and OFF the same thing happens.
The expected result for the query is NULL.
Monica
April 10, 2009 at 6:10 am
I tested it overhere and always got the correct results (x86 (std ed)and x64 (ee))
select LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS INT)-1))
select LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS BIGINT)-1))
select LEFT(NULL,ABS(CAST(ISNULL(CHARINDEX('.',NULL),NULL) AS INT)-1))
go 1000 -- executes the previous batch 1000 times
All nicely resulted in NULL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 10, 2009 at 6:17 am
Hi
I can confirm Monica.
On SQL Server 2008 x64 (I know 2008... but no other x64 available at the moment):
Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.
On SQL Server 2005 x86:
No error.
Greets
Flo
April 11, 2009 at 6:03 am
Is this because INT is 32-bit signed integer and BIGINT is 64-bit signed integer..?
INTEGER: The JDBC INTEGER type represents a signed 32-bit integer. This maps to a SQL Server int type.
BIGINT: The JDBC BIGINT type represents a signed 64-bit integer. This maps to a SQL Server bigint type.
"Don't limit your challenges, challenge your limits"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply