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

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

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

Show key headers only | View raw

On Aug 12, 1:06 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> xo (xo555...@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, esq...@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

Thanks for the respond.   I would like to provide the error message so
hopefully it will help.
If the sql is running under view -> design,  it gave a Microsoft SQL
Server Management Studio dialog box of

SQL Execution Error.

Executed SQL statement: SELECT e1.FLODS_ID, e1.LGNEF_ID,
CAST(d.CASE_REFERENCE AS DECIMAL) AS CASE_REFERENCE, d.OPEN_DT,
d.CLOSED_DT, d.STATUS, d.SLA_TARGET_DT,
CAST(f.DURATION_IN_HOURS AS INT) AS DURATION_IN_HOURS,
CAST(f.DURATION_IN_DAYS AS SMALLINT) AS DURATION_IN_DAYS,...
Error Source: .Net SqlClient Data Provider
Error Message: Error converting data type varchar to numeric.

And if I run it under Query Analyzer, it returns
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Thanks

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