Hello -

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.

Please help.
Thanks in advance.

xo wrote:
that we can run on our servers to see the error you are getting. If the
problem occurs when using variables, that would enable you to easily create
a repro script:

declare @v1 <somedatatype>, @v2 <someotherdatatype>;
set @v1=<somevalue>;
set @v2=cast(@v1 as <someotherdatatype>);

Replace the <...>s with the actual data types and values required to
reproduce your symptom and post it here.

On Aug 12, 11:56*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
The error seems to be caused by the last line. Add'l info - FLODS_ID
is (PK, numeric (18,0), not null) and OBJECT_ID (varchar(80), null)

Below is the sql -

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,
q.NAME AS QUEUE_NAME, c.SUBJECT, c.REASON, c.TYPE,
e1.FORM_NAME, e1.ADDITIONAL_REQUEST_DETAILS, e1.AUTHFIELD, e1.CANCEL,
e1.CANCELDESC,
e1.CANCELREASON,
e1.WHICH_CORNER_ISSUE_CLOSEST_OR_SIDE_OF_STREET, e1.XCOORD, e1.YCOORD,
e1.TXTSTREETNAME, e1.TXTSTREETNAME2,
e1.TXTSTREETNUMBER, e1.TXTZIP, e1.TYPE_DETAILS,
e1.VIEWMODE, e1.TXTADDRESS, e1.TXTCITY, e1.TXTEMAIL, e1.TXTLANDMARK,
e1.TXTNAME,
e1.TXTPHONE, e1.NATURE_OF_REQUEST, e1.POSTSUBMITCASEID,
e1.REQUEST_TYPE, e1.SUBMIT, e1.SUPERVISOR_NAME, e1.TKMISSADDR,
e1.DEPARTMENT_SERVICE_LEVEL, e1.DISTRICTQUEUE,
e1.ESCALATION_INSTRUCTIONS, e1.ID, e1.LOCATION_DESCRIPTION,
e1.LOCATION_TYPE,
e1.CANCELREASONLABEL, e1.CMBLOC, e1.CNN, e1.CREATEDATE,
e1.CREATETIME, e1.CUSTOMER_REQUESTED_TO_BE_CONTACTED,
ISNULL(P.BUILDING_NAME, ' ') AS BUILDING_NAME,
ISNULL(P.ADDRESS_NUMBER, ' ') AS ADDRESS_NUMBER,
ISNULL(P.PRIM_STREET_NAME, ' ')
AS PRIMARY_STREET, ISNULL(P.SEC_STREET_NAME, ' ') AS
CROSS_STREET, ISNULL(P.ZIP_CODE, ' ') AS ZIP_CODE,
CAST(ISNULL(LEFT(P.PAO_USER_DEFINED_TEXT_8, 2), '00')
AS SMALLINT) AS SUPERVISOR_DISTRICT, ISNULL(P.PAO_USER_DEFINED_1, ' ')
AS JURISDICTION,
ISNULL(P.SAO_USER_DEFINED_TEXT_3, ' ') AS NEIGHBORHOOD,
ISNULL(P.PAO_USER_DEFINED_TEXT_5, ' ') AS POLICE_DISTRICT,
ISNULL(P.SAO_USER_DEFINED_2, ' ') AS REC_AND_PARK_NSA,
ISNULL(P.SAO_USER_DEFINED_3, ' ') AS SRO,
CAST(ISNULL(P.PAO_USER_DEFINED_2, '0')
AS DECIMAL) AS LATITUDE,
CAST(ISNULL(P.PAO_USER_DEFINED_3, '0') AS DECIMAL) AS LONGITUDE
FROM dbo.FLODS_SEWERISSUES_E00 AS e1 INNER JOIN
dbo.FLODS_CASE_ENQUIRY_F00 AS f ON e1.CASE_ENQUIRY_ID =
f.CASE_ENQUIRY_ID INNER JOIN
dbo.FLODS_CASE_ENQUIRY_D00 AS d ON f.CASE_ENQUIRY_ID =
d.FLODS_ID INNER JOIN
dbo.FLODS_CASE_QUEUE_D00 AS q ON f.QUEUE_ID =
q.FLODS_ID INNER JOIN
dbo.FLODS_CLASSIFICATION_D00 AS c ON
f.CLASSIFICATION_ID = c.FLODS_ID LEFT OUTER JOIN
dbo.FLODS_PROPERTY_D00 AS P ON CAST(P.FLODS_ID AS
VARCHAR(18)) = ISNULL(f.OBJECT_ID, '0')

Thanks in advance

xo (xo5555ox (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

On Aug 12, 1:06*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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

xo (xo5555ox (AT) gmail (DOT) com) writes:
I know perfectly well what the error is. It's a very common question on the
newsgroups.

My previous post presented the cure for the problem, so know you know
how to fix your query! (And let's hope that isnumeric does not find
any false positives!



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

xo wrote:
<snip>
Thanks for the attempt to provide extra information. Unfortunately it was
both too much and not enough information.
Please provide:
1. A CREATE TABLE script to allow us to create a table with only the
relevant columns. Do not include any columns that are not needed to recreate
the symptom.
2. A script to insert some sample data into the table. The sample data
should include the row that causes the error message.
3. The sql statement that uses the test table to reproduce the symptom

This of course will require you to find the row(s) containing the data that
causes the error. Doing so might lead you to the answer to your question. I
suggest you remove tables and columns from the sql you provided until you
can run it without error. Then add back in a column/table at a time until
the error does occur. Then try limiting the results using a WHERE clause
until you identify the row(s) containing the problem data.

On Aug 12, 1:25*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
I am bound by the decision maker not to change the structure of the
view but to find out why it is working on the old 32-bit SQL server
2005 but not working the new 64-bit SQL server. Thanks

xo (xo5555ox (AT) gmail (DOT) com) writes:
And you have gotten the answer: by chance. It could start failing on the
old server tomorrow.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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