Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #593

Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005

Show all headers | View raw

xo (xo5555ox@gmail.com) writes:
> I have a strange problem when casting value under 64-bit sql server
> 2005 vs under 32-bit sql server 2005.
> 
> I got two servers.  One has 32-bit SQL server 2005 standard edition
> (version 9.00.3073.00) on Windows NT 5.2 (3790) with NT INTEL x86.
> The second server has 64-bit SQL server Enterprise edition (version
> 9.00.4035.00) on Windows NT 5.2 (3790) with NT AMD64.
> 
> The problem came when I tried to cast value under 32-bit SQL server
> 2005, the result gets queried out.  But when the same sql got run,  it
> prompted conversion error - "Error converting data type varchar to
> numeric." under the 64-bit server.
> 
> I tried cleaned up the data and it still gives the same error.
 
This is has nothing to do with 32- or 64-bit per se. You just happen to
get different execution plans on the two machines. If you have a query like:

SELECT cast(str AS int)
FROM   tbl
WHERE  isnumeric(str) = 1

The query may or may not fail, depending on when isnumeric is evaluated. You 
could argue that from how SQL is defined, the query should never fail, but 
that is not how Microsoft has designed it. (And they are not alone. I once
read a blogpost about this problem on Oralce.)

The almost safe way is to do:

SELECT CASE WHEN isumeric(str) = 1 THEN cast(str AS int) END
FROM   tbl
WHERE  isnumeric(str) = 1

I say "almost safe", because isnumeric may approve of values that cannot be 
cast to int.

The next version of SQL Server 100% safe way:

SELECT try_convert(int, str)
FROM   tbl
WHERE  try_convert(int, str) IS NOT NULL

try_convert returns NULL if the convert fails. This function is long-
awaited!



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 xo <xo5555ox@gmail.com> - 2011-08-12 10:33 -0700
  Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-08-12 14:56 -0400
    Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 xo <xo5555ox@gmail.com> - 2011-08-12 13:05 -0700
      Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-08-12 16:56 -0400
  Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 Erland Sommarskog <esquel@sommarskog.se> - 2011-08-12 22:06 +0200
    Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 xo <xo5555ox@gmail.com> - 2011-08-12 13:17 -0700
      Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 Erland Sommarskog <esquel@sommarskog.se> - 2011-08-12 22:25 +0200
        Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 xo <xo5555ox@gmail.com> - 2011-08-12 14:31 -0700
          Re: Problem with casting value under 64-bit sql server 2005 vs under 32-bit sql server 2005 Erland Sommarskog <esquel@sommarskog.se> - 2011-08-13 12:59 +0200

csiph-web