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

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

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

Show all headers | View raw

On Aug 12, 11:56 am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> xo wrote:
> > 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 show us how to reproduce this symptom. You need to provide a script
> 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.

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

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