'nuther "String or binary data would be truncated" problem

  • I'm trying to insert data from an Oracle table using a linked server into a SQL Server 2005 table, using:

    insert into tblDirectory

    SELECT

    E_ID

    ,N_ID

    ,LAST_NAME_PRIMARY AS LAST_NAME

    ,FIRST_NAME_PRIMARY AS FIRST_NAME

    ,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME

    ,NAME_PRIMARY as NAME

    ,convert(datetime, birthdate) as BIRTHDATE

    ,MAIL_ADDRESS1 AS ADDRESS1

    ,MAIL_ADDRESS2 AS ADDRESS2

    ,MAIL_CITY AS CITY

    ,MAIL_STATE AS STATE

    ,MAIL_POSTAL_ZIP AS POSTAL

    ,MAIL_COUNTRY_LDESC AS COUNTRY

    ,MAIL_PHONE_NBR AS PHONE

    ,EMAIL_PREFERRED AS EMAIL_ADDR

    ,C_ID

    FROM

    dwaredb..oracleschema.oracletable A

    WHERE

    EXISTS (

    SELECT

    B.E_ID

    FROM

    dwaredb..oracleschema2.oracletable2 B

    WHERE

    B.E_ID = A.E_ID

    )

    and am receiving the error:

    Msg 8152, Level 16, State 2, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Removing the 'insert into' statement, I can select from those tables w/o a problem. I've quadruple checked that my data is not too large for any of the fields, using length statements from both the SQL Server and Oracle sides. I've seen the posts concerning the SQL Server 2000 problem with running this thru various user perms, or trying to use the Agent; I'm running this from SSMS using my sysadmin account and the user for the linked server has dbowner privs on the destination database.

    I set the connection up using the Oracle OLE DB.

    The Oracle column definitions are varchar(x) and are not exactly the same size as the SQL Server column definitions, which are varchar(x) also,

    SQL Server: N_ID(varchar(20)

    Oracle: N_ID(varchar2(60))

    is this a problem? I don't have a lot of control over those definitions. As I stated earlier, I've confirmed the data is not too large for the destination table.

    My questions are:

    1. Can someone help me trace the steps that SQL Server goes thru to convert/insert this data? Reading those other posts, it sounds like this error could be either misleading, or is referencing a system table that I'm overloading.

    2. Has anyone seen this before?

    TIA.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • dh (6/18/2009)


    I'm trying to insert data from an Oracle table using a linked server into a SQL Server 2005 table, using:

    insert into tblDirectory

    SELECT

    E_ID

    ,N_ID

    ,LAST_NAME_PRIMARY AS LAST_NAME

    ,FIRST_NAME_PRIMARY AS FIRST_NAME

    ,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME

    ,NAME_PRIMARY as NAME

    ,convert(datetime, birthdate) as BIRTHDATE

    ,MAIL_ADDRESS1 AS ADDRESS1

    ,MAIL_ADDRESS2 AS ADDRESS2

    ,MAIL_CITY AS CITY

    ,MAIL_STATE AS STATE

    ,MAIL_POSTAL_ZIP AS POSTAL

    ,MAIL_COUNTRY_LDESC AS COUNTRY

    ,MAIL_PHONE_NBR AS PHONE

    ,EMAIL_PREFERRED AS EMAIL_ADDR

    ,C_ID

    FROM

    dwaredb..oracleschema.oracletable A

    WHERE

    EXISTS (

    SELECT

    B.E_ID

    FROM

    dwaredb..oracleschema2.oracletable2 B

    WHERE

    B.E_ID = A.E_ID

    )

    and am receiving the error:

    Msg 8152, Level 16, State 2, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Removing the 'insert into' statement, I can select from those tables w/o a problem. I've quadruple checked that my data is not too large for any of the fields, using length statements from both the SQL Server and Oracle sides. I've seen the posts concerning the SQL Server 2000 problem with running this thru various user perms, or trying to use the Agent; I'm running this from SSMS using my sysadmin account and the user for the linked server has dbowner privs on the destination database.

    I set the connection up using the Oracle OLE DB.

    The Oracle column definitions are varchar(x) and are not exactly the same size as the SQL Server column definitions, which are varchar(x) also,

    SQL Server: N_ID(varchar(20)

    Oracle: N_ID(varchar2(60))

    is this a problem? I don't have a lot of control over those definitions. As I stated earlier, I've confirmed the data is not too large for the destination table.

    My questions are:

    1. Can someone help me trace the steps that SQL Server goes thru to convert/insert this data? Reading those other posts, it sounds like this error could be either misleading, or is referencing a system table that I'm overloading.

    2. Has anyone seen this before?

    TIA.

    I'd actually say this is your problem:

    SQL Server: N_ID(varchar(20)

    Oracle: N_ID(varchar2(60))

    Both fields should be declared the same, and iirc from other threads, varchar2 in Oracle is nvarchar in SQL Server, so you probably should change the definition of N_ID from varchar(20) to nvarchar(60), Any other differences should also be addressed as well.

  • Thanks for the input, Lynn. It doesn't appear to be a problem to mismatch those column definitions, based on some tests I did, though:

    create table test_n_id2

    (

    n_idvarchar(20)

    )

    ----------------------------

    insert into test_n_id2

    select

    national_id

    from

    dwaredb..oracleschema.oracletable

    ----------------------------

    oracletable:

    national_id varchar2(60 char)

    It appears that the problem was with the datetime field, 'birthdate'. When I ran an output of the table structure, using 'sp_columns', I found that the 'length' of the column was 16, but 'scale' was 3. I had done a 'convert(datetime, birthdate)' on that column, but it appears the fix was:

    substring(convert(varchar(16),birthdate), 1, 16) as BIRTHDATE

    The other forum posts that I had referenced stated that the only culprit could be a text or char field. Anyways, figured it out.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Change this:

    insert into tblDirectory

    SELECT

    E_ID

    ,N_ID

    ,LAST_NAME_PRIMARY AS LAST_NAME

    ,FIRST_NAME_PRIMARY AS FIRST_NAME

    ,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME

    ,NAME_PRIMARY as NAME

    ,convert(datetime, birthdate) as BIRTHDATE

    ,MAIL_ADDRESS1 AS ADDRESS1

    ,MAIL_ADDRESS2 AS ADDRESS2

    ,MAIL_CITY AS CITY

    ,MAIL_STATE AS STATE

    ,MAIL_POSTAL_ZIP AS POSTAL

    ,MAIL_COUNTRY_LDESC AS COUNTRY

    ,MAIL_PHONE_NBR AS PHONE

    ,EMAIL_PREFERRED AS EMAIL_ADDR

    ,C_ID

    FROM

    to this:

    --insert into tblDirectory

    SELECT

    E_ID

    ,N_ID

    ,LAST_NAME_PRIMARY AS LAST_NAME

    ,FIRST_NAME_PRIMARY AS FIRST_NAME

    ,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME

    ,NAME_PRIMARY as NAME

    ,convert(datetime, birthdate) as BIRTHDATE

    ,MAIL_ADDRESS1 AS ADDRESS1

    ,MAIL_ADDRESS2 AS ADDRESS2

    ,MAIL_CITY AS CITY

    ,MAIL_STATE AS STATE

    ,MAIL_POSTAL_ZIP AS POSTAL

    ,MAIL_COUNTRY_LDESC AS COUNTRY

    ,MAIL_PHONE_NBR AS PHONE

    ,EMAIL_PREFERRED AS EMAIL_ADDR

    ,C_ID

    into #TestTable -- new table that will be created

    FROM

    Then do this:

    ;with

    TargetTable as

    (select name, system_type_id, max_length

    from sys.columns

    where object_id = object_id(N'tblDirectory'))

    TestTable as

    (select name, system_type_id, max_length

    from tempdb.sys.columns

    where object_id = object_id(N'tempdb..#TestTable')

    select *

    from TargetTable

    inner join TestTable

    on TargetTable.name = TestTable.name

    and

    (TargetTable.system_type_id != TestTable.system_type_id

    or

    TargetTable.max_length != TestTable.max_length);

    Assuming the column aliases in the select are the same as the column names in tblDirectory, that will tell you very, very rapidly exactly which column(s) don't match.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply