7 Replies Latest reply on Jul 28, 2017 3:53 PM by Gaz in Oz

    ODBC SQL Server connection issue "String data, right truncation {01004}"

    Ed Heaton

      Hi,

       

      I have managed to create a partially functioning heterogeneous database link to SQL Server from Oracle.  In TOAD, pulling back character data errors out with the following:

       

       

      Pulling back numerical data is fine.

       

       

      The Oracle database version is 12.1.0.2 running on Red Hat 6.7.  I have used the following Microsoft driver:

       

      ODBC Driver 13 for SQL Server.

       

      Can anybody point me in the right direction on how to solve this frustrating issue?

       

      My heterogeneous initSCOM.ora file which is located under $ORACLE_HOME/hs/admin looks like this:

       

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO = SCOM

      HS_FDS_TRACE_LEVEL = Debug

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      #

      # ODBC specific environment variables

      #

      set ODBCINI=/etc/odbc.ini

       

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set <envvar>=<value>

       

      Listener.ora file looks like this:

       

      LISTENER =

        (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = *********))

        )

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = SCOM)

            (ORACLE_HOME = /app/oracle/12102/db)

            (PROGRAM = dg4odbc)

           )

        )

       

      Tnsnames.ora entry looks like this:

       

      SCOM =

        (DESCRIPTION =

          (ADDRESS_LIST=

            (ADDRESS =

            (PROTOCOL = TCP)

            (HOST = localhost)

            (PORT = 1521)

           )

         )

        (CONNECT_DATA = (SID = SCOM))

      (HS = OK)

      )

       

      Any assistance would be greatly appreciated,

       

      Cheers

       

      Ed

        • 1. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
          Gaz in Oz

          What is the locale/characterset of your sqlserver db?

          In the initSCOM.ora file place placing lines to define characterset...

          For example

          # NLS Parameters

          HS_FDS_REMOTE_DB_CHARSET = UCS2

          HS_NLS_NCHAR=UCS2

          HS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252

          I am not saying those specific values will help you, but it fixed a similar issue for me, with string data having v\0a\0l\0u\0e\0s\0... i.e an actual character padded with the null character.

          A kludgy work around is to use "REPLACE(col, chr(0))"

          ...and finally, you could change the locale/characterset of your sql server db.

          • 2. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
            Ed Heaton

            Hi Gaz,

             

            Thanks for the idea.  I did apply your exact settings in the off chance it would work but no joy I'm afraid.

             

            The collation of the SQL Server database is as follows:

             

            SQL_Latin1_General_CP1_CI_AS

             

            Do you think it could just be a case of getting the HS_LANGUAGE right?

             

            Some error messages in the trace file include the following:

             

            Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION

            treat_SQLLEN_as_compiled = 1

             

            hgopoer, line 240: got native error 5701 and sqlstate 01000; message follows...

            [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'OperationsManagerDW'. {01000,NativeErr = 5701}[unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'OperationsManagerDW'. {01000,NativeErr = 5701}[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}

             

            DBMS Name:Microsoft SQL Server, DBMS Version:12.00.5000

            Exiting hgocont, rc=0 at 2017/07/27-08:06:31 with error ptr FILE:hgocont.c LINE:2745 ID:SQLDriverConnect

             

            Exiting hgoulcp, rc=0 at 2017/07/27-08:06:31 with error ptr FILE:hgoulcp.c LINE:2227 ID:Translation text for Unicode literal supported. Setting HOACOPTTSTN1 capability to on

             

            Exiting hgoftch2, rc=28500 at 2017/07/27-08:08:48 with error ptr FILE:hgoftch.c LINE:1073 ID:Row error while doing array fetch

             

            Any further hints would be great!

             

            Ed

            • 3. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
              mxallen-Oracle

              Try HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

               

              Regards,

              Matt

              • 4. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
                Ed Heaton

                Hi Matt,

                 

                I tried that one after seeing it on the Oracle Base website.  Tried again after your suggestion but still have no luck.

                 

                Cheers

                 

                Ed

                • 5. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
                  Gaz in Oz

                  Do you think it could just be a case of getting the HS_LANGUAGE right?

                  Possibly, yes.

                  . All those errors in the log, was that before or after you made changes?

                  . Did you try the kludgy workaround of REPLACE(col, chr(0)) to see if it was null characters being appended?

                  . If you "DESCRIBE your_table" are the sizes of varchr2 fileds the same as in sqlserver?

                   

                  ...just a thought, what happens if you use SQL Developer? Download the "with JDK" version.

                  Oracle SQL Developer Downloads

                   

                  This 3rd party site seems to have some very good suggestions on what settings to set etc, if you are having issues.

                  Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)

                  Specifically section "6. Known Issues"

                  • 6. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
                    Ed Heaton

                    Hi Gaz,

                     

                    Those errors were in the log before yes.  I did try the kludgy workaround also to no avail.  I also tried SQL Developer and SQL*Plus.

                     

                    The Easysoft website was very useful.  I tried setting the following parameter in the odbc.ini but it did not work:

                     

                    VarMaxAsLong = Yes

                     

                    However, I am not sure if this works with the Microsoft Driver as it is not listed as a setting that can be added in.  Maybe it is just for the Easysoft one?

                     

                    I did a describe on the remote table:

                     

                    CDB01DB@CDB$ROOT> desc ManagedEntity@SCOM.WORLD;

                    Name                                                                                Null?    Type

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

                    ManagedEntityRowId                                                                  NOT NULL NUMBER(10)

                    ManagementGroupRowId                                                                NOT NULL NUMBER(10)

                    ManagedEntityGuid                                                                   NOT NULL CHAR(36 CHAR)

                    ManagedEntityTypeRowId                                                              NOT NULL NUMBER(10)

                    TopLevelHostManagedEntityRowId                                                               NUMBER(10)

                    FullName                                                                                     NVARCHAR2

                    Path                                                                                         NVARCHAR2

                    Name                                                                                         NVARCHAR2

                    DisplayName                                                                                  NVARCHAR2

                    ManagedEntityDefaultName                                                                     NVARCHAR2

                    DWCreatedDateTime                                                                   NOT NULL DATE

                     

                    In SQL Server:

                     

                     

                    It is the NVARCHAR columns that are not playing ball.

                     

                    As an aside, I tried the freetds driver and this does not pick up the NVARCHAR fields at all!

                     

                    CDB01DB@CDB$ROOT>  desc  ManagedEntity@SCOMTDS.WORLD;

                    Name                                                                                Null?    Type

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

                    ManagedEntityRowId                                                                  NOT NULL NUMBER(10)

                    ManagementGroupRowId                                                                NOT NULL NUMBER(10)

                    ManagedEntityGuid                                                                   NOT NULL CHAR(36)

                    ManagedEntityTypeRowId                                                              NOT NULL NUMBER(10)

                    TopLevelHostManagedEntityRowId                                                               NUMBER(10)

                    DWCreatedDateTime                                                                   NOT NULL DATE

                     

                    Column:6(FullName): dtype:-10 (WLONGVARCHAR), prc/scl:1073741823/0, nullbl:1, octet:2147483646, sign:1, radix:10

                    Column 6 (FullName): dtype -10 (WLONGVARCHAR): this datatype is not supported.

                    Entered hgopcda at 2017/07/28-14:25:09

                    Column:6(Path): dtype:-10 (WLONGVARCHAR), prc/scl:1073741823/0, nullbl:1, octet:2147483646, sign:1, radix:10

                    Column 6 (Path): dtype -10 (WLONGVARCHAR): this datatype is not supported.

                    Entered hgopcda at 2017/07/28-14:25:09

                    Column:6(Name): dtype:-10 (WLONGVARCHAR), prc/scl:1073741823/0, nullbl:1, octet:2147483646, sign:1, radix:10

                    Column 6 (Name): dtype -10 (WLONGVARCHAR): this datatype is not supported.

                    Entered hgopcda at 2017/07/28-14:25:09

                    Column:6(DisplayName): dtype:-10 (WLONGVARCHAR), prc/scl:1073741823/0, nullbl:1, octet:2147483646, sign:1, radix:10

                    Column 6 (DisplayName): dtype -10 (WLONGVARCHAR): this datatype is not supported.

                    Entered hgopcda at 2017/07/28-14:25:09

                    Column:6(ManagedEntityDefaultName): dtype:-10 (WLONGVARCHAR), prc/scl:1073741823/0, nullbl:1, octet:2147483646, sign:1, radix:10

                    Column 6 (ManagedEntityDefaultName): dtype -10 (WLONGVARCHAR): this datatype is not supported.

                     

                    Could it be the fact that the SQL Server database is in unicode and my Oracle database isn't be the actual issue here?

                     

                    Thanks for your input.

                     

                    Ed

                    • 7. Re: ODBC SQL Server connection issue "String data, right truncation {01004}"
                      Gaz in Oz

                      Could it be the fact that the SQL Server database is in unicode and my Oracle database isn't be the actual issue here?

                      Indeed it could.

                      There is a mismatch between what sqlverver is trying to "deliver" with what Orcle is trying to "pull" from the sqlserver db... I think the best option is to change sqlserver's locale/characterset to something Oracle is able to take. A bit vague, but remember it is a complete mismatch...

                       

                      [Edit: ...did you add "VarMaxAsLong = Yes" to the odbc.ini file?]