13 Replies Latest reply: Mar 20, 2013 9:14 AM by jstem1177 RSS

    Problem Connect DG Oracle 11gR2 - SQL Server

    jstem1177
      Hello All,

      I've read through the forum threads and the documentation. As far as I can see I've got everything right, however running into the typical error:
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      [Oracle][ODBC SQL Server Driver][DBNETLIB]Invalid connection. {08001,NativeErr = 14}[Oracle][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConnectParams()). {01000,NativeErr = 14}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
      ORA-02063: preceding 2 lines from MSSQL2008
      28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"
      *Cause:    The cause is explained in the forwarded message.
      *Action:   See the non-Oracle system's documentation of the forwarded
                 message.
      Error at Line: 3 Column: 46
      The documentation also seems like plug & play so I must have missed something, if my config files are correct.


      My environment consists of :
      ====================
      Localhost: Oracle 11.2.0.3 on Windows 7 x64
      Remote Host: SQL Server 2005, 2008, SQLExpress --> each running on their own static ports

      Oracle Gatways software 11gR2 is installed on the localhost on port 1522.


      I'm trying to connect to the adventure works database on the SQL server but keep getting the aforementioned error. Here are my config files.

      ORACLE_HOME=c:\oracle\product\11.2.0\dbhome_1
      ORACLE_GATEWAY=c:\oracle\product\11.2.0\gtwhome_1

      ORACLE_GATEWAY --> init4dgmsql.ora
      # This is a customized agent init file that contains the HS parameters
      # that are needed for the Database Gateway for Microsoft SQL Server
      
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO=[SVR-DEV-CLU-01:1434]/SQL2008/AdventtureWorks2008R2
      HS_FDS_TRACE_LEVEL=ON
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER
      ORACLE_GATEWAY --> listener.ora
      # listener.ora Network Configuration File: C:\oracle\product\11.2.0\gtwhome_1\network\admin\listener.ora
      # Generated by Oracle configuration tools.
      
      LISTENER_GTW =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
          )
        )
      
      SID_LIST_LISTENER_GTW=
        (SID_LIST=
            (SID_DESC=
               (SID_NAME=dg4msql)
               (ORACLE_HOME=C:\oracle\product\11.2.0\gtwhome_1)
               (PROGRAM=dg4msql)
            )
        )
      
      ADR_BASE_LISTENER_GTW = C:\oracle\product\11.2.0\gtwhome_1
      ORACLE_HOME --> tnsnames.ora
      dg4msql  =
        (DESCRIPTION=
          (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
          (CONNECT_DATA=
               (SID=dg4msql)
          )
          (HS=OK)
        ) 
      Local database --> db link
      CREATE PUBLIC DATABASE LINK MSSQL2008 CONNECT TO "ORACLE_GATEWAY" IDENTIFIED BY "Dbsoft1234" USING 'dg4msql';
      select AddressLine1, City from Person.Address@MSSQL2008;
      I have validated there there sis no dynamic ports and each database is running on its own static port, in this case 1434. I have done a tnsping the LISTENER_GTW responds correctly. I have done a ping to the server to validate server-name, this is ok also.


      SQL Server 2008:
      Server Name: SQL2008\SQL2008
      Instance Name: SQL2008
      Port: 1434
      Database: AdvebtureWorks2008R2    (case sensitive)
      Failure also on:

      SQL Server 2005
      Server Name: SVR-CLU-DEV-01\SQL2005
      Instance Name: SQL2005
      Dataabse: master
      Thanks in advance for any assistance.

      Jan S.
        • 1. Re: Problem Connect DG Oracle 11gR2 - SQL Server
          Mkirtley-Oracle
          Hi,
          The configuration files look okay, except for the init4dgmsql.ora value for HS_FDS_CONNECT_INFO.

          The recommended settings are in the format -

          HS_FDS_CONNECT_INFO=<SQL Server>:<port>//<database>
          HS_FDS_CONNECT_INFO=<SQL Server>/<instance>/<database>

          You have set it as -

          HS_FDS_CONNECT_INFO=[SVR-DEV-CLU-01:1434]/SQL2008/AdventtureWorks2008R2

          so what happens if you set -

          HS_FDS_CONNECT_INFO=[SVR-DEV-CLU-01]/SQL2008/AdventtureWorks2008R2

          or

          HS_FDS_CONNECT_INFO=[SVR-DEV-CLU-01:1434]//AdventtureWorks2008R2

          One other point, you posted the SQL*Server information as -

          Server Name: SQL2008\SQL2008
          Instance Name: SQL2008
          Port: 1434
          Database: AdvebtureWorks2008R2 (case sensitive)

          so, is the database name AdventtureWorks2008R2 or AdvebtureWorks2008R2 ?
          Please make sure you use the correct name.

          Regards,
          Mike
          • 2. Re: Problem Connect DG Oracle 11gR2 - SQL Server
            jstem1177
            Hello Mike,

            The adventure works "double tt" was just a typo in the forum. However I did play around with with the different variations. Managed to get a connection to the SQL 2005 which runs off 1433.
            [SVR-DEV-CLU-01.dbsoft.local]/SQL2005/master
            How ever when I do a select * from sys.all_objects I get a protocol violation in SQL Developer.

            I will will create a few more instances of dg4msql to test across the variable versions and ports.

            Will update the post as soon as I've got it nailed down.

            Thanks

            Jan S.
            • 3. Re: Problem Connect DG Oracle 11gR2 - SQL Server
              Mkirtley-Oracle
              Jan,
              Thanks for letting us know youhave been able to connect using the syntax.

              Do you also get a problem when youissue the select froma SQLPLUS session ?
              There is a known problem with selects if the RDBMS has the parameters -

              cursor_sharing=similar
              or
              cursor_sharing=force

              If it is set to -

              cursor_sharing=exact

              then there is no problem.
              Could you check what setting you have ?

              If this is your problem then the solution is one of the following -

              1. In the SQLPLUS session issue -

              ALTER SESSION SET CURSOR_SHARING=EXACT;

              or use a hint in the select -

              select /*+ CURSOR_SHARING_EXACT */ * from all_objects@gtw_link where object_id=1;

              2. Change the RDBMS cursor_sharing parameter to "EXACT" in the RDBMS instance.

              3. If using 11.2.0.3 then apply patch for unpublished bug 9877960.
              If the patch is not available for your platform then contact Oracle Support.

              Regards,
              Mike
              • 4. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                jstem1177
                Hello Mike,

                I have no issues with with SQL*Plus, only with SQL Developer, but I have not issues with Toad nor with Pl/SQL Developer.

                On the other hand, it looks as though I might be having problems selecting from schema tables, even when I connect as sa.

                All 3 statements fails with with table does not exist, however the statement runs with out an error in MSSQL Studio.
                select "AddressLine1" from Person.Address@MSSQL2008;
                select "AddressLine1" from "Person.Address"@MSSQL2008;
                select "AddressLine1" from "Person"."Address"@MSSQL2008;
                I' was looking through the documentation and I only found a small statement concerning case sensitivity, but no mention of schema table translation.


                Thanks

                Jan
                • 5. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                  Mkirtley-Oracle
                  Jan,
                  I would expect the first 2 statements to fail as the syntax needs to be -

                  select "col_name" from "owner"."table_name"@db_link ;

                  What error does this fail with -

                  select "AddressLine1" from "Person"."Address"@MSSQL2008;

                  and are you running it from SQLPLUS ?
                  Does the user defined in the MSSQL2008 link have permissions on the table "Address" owned by "Person" ?

                  If the permissions are correct then set up gateway debug tracing by adding -

                  HS_FDS_TRACE_LEVEL=debug

                  in the gateway init<sid>.ora and check the SQL that is sent from the gateway.
                  It should be the last 'hgopars' entry before you see an error or fetch statements if it works.

                  Regards,
                  Mike

                  Edited by: mkirtley on Mar 15, 2013 2:32 PM
                  • 6. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                    Mkirtley-Oracle
                    Jan,
                    I have also tested the select from sys.all_objects.
                    I had no problem using the following -

                    - SQL*Developer 3.2.09 Build MAIN-09.23
                    - connected to RDBMS 11.2.0.2
                    - DG4MSQL 11.2.0.3
                    - Dg4ODBC 11.2.0.3

                    What version of SQL*Developer are you using ?

                    Regards,
                    Mike
                    • 7. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                      jstem1177
                      Hello Mike,

                      So I'm back at it. I've changed a login user to not use SA and made sure the new LOGIN/USER ORACLE_GATEWAY has select, insert,delete on the table "Person"."Address".


                      Here is the error I get when i try to select:
                      SQL> select "AddressLine1", "City" from "Person"."Address"@MSSQL2008;
                      select "AddressLine1", "City" from "Person"."Address"@MSSQL2008
                                                                  *
                      ERROR at line 1:
                      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                      [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                      ORA-02063: preceding 2 lines from MSSQL2008
                      This is an AdventureWorks database on Oracle 2008 R2. However I've got my doubts on the permissions. So I'm investigating that. Looks like the entire is completely different then oracle.

                      Jan S.
                      • 8. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                        Mkirtley-Oracle
                        Jan,
                        The error you now see is coming from SQL*Server and looks to be associated with returning NULL data.
                        See this -

                        http://support.microsoft.com/kb/158618

                        I can't find any similar problems for this using Dg4MSQL so coudl you please get a Dg4MSQL 255 level trace by setting -

                        HS_FDS_TRACE_LEVEL=255

                        in the gateway init file and running the select from a new SQPLUS session, then ending the session and posting the trace, or loading it to an external site where we can view it.

                        Regards,
                        Mike
                        • 9. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                          jstem1177
                          Hello mike,

                          I validated all the grants and permissions, but I'm still getting the Indicator variable error.
                          SQL> select "AddressLine1", "City" from "Person"."Address"@MSSQL2008;
                          select "AddressLine1", "City" from "Person"."Address"@MSSQL2008
                                                                      *
                          ERROR at line 1:
                          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                          [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied
                          {22002}
                          ORA-02063: preceding 2 lines from MSSQL2008
                          Same SQL woks correctly on SQL Server
                          use AdventureWorks2008R2
                          go
                          select "AddressLine1", "City" from "Person"."Address"
                          • 10. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                            Mkirtley-Oracle
                            Jan,
                            It is n't a permission problem but looks to be something about the way NULL data is handled whichis why we need to see a trace file to try and see whatus haoppening withthe gateay and NULL data.
                            Is this with the Dg4MSQL ?

                            Regards,
                            Mike
                            • 11. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                              jstem1177
                              Hello Mike,

                              Found the trace files.

                              Here is what I have for the last:
                              Oracle Corporation --- MONDAY    MAR 18 2013 14:50:31.582
                              
                              
                              Heterogeneous Agent Release 
                              11.2.0.1.0 
                              
                              
                              
                              
                              Oracle Corporation --- MONDAY    MAR 18 2013 14:50:31.581
                              
                                  Version 11.2.0.1.0
                              
                              Entered hgogprd
                              HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
                              Entered hgosdip
                               setting HS_OPEN_CURSORS to default of 50
                               HOSGIP returned value of "RECOVER" for HS_FDS_RECOVERY_ACCOUNT
                               HOSGIP returned a value for HS_FDS_RECOVERY_PWD
                               setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
                               setting HS_IDLE_TIMEOUT to default of 0
                               setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
                               setting HS_NLS_NCHAR to default of "UCS2"
                               setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
                               setting HS_FDS_DATE_MAPPING to default of "DATE"
                               setting HS_RPC_FETCH_REBLOCKING to default of "ON"
                               setting HS_FDS_FETCH_ROWS to default of "100"
                               setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
                               setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
                               setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
                               setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
                               setting HS_FDS_MAP_NCHAR to default of "TRUE"
                               setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
                               setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
                               setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
                               setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
                               setting HS_FDS_QUERY_DRIVER to default of "FALSE"
                               setting HS_FDS_SUPPORT_STATISTICS to default of "TRUE"
                               Parameter HS_FDS_QUOTE_IDENTIFIER is not set
                               setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
                               setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
                               setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
                               setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
                               setting HS_FDS_DELAYED_OPEN to default of "TRUE"
                               setting HS_FDS_WORKAROUNDS to default of "0"
                              Exiting hgosdip, rc=0
                               ORACLE_SID is "ms2008"
                               Product-Info:
                                Port Rls/Upd:1/0 PrdStat:0
                                Agent:Oracle Database Gateway for MSSQL
                                Facility:hsa
                                Class:MSSQL, ClassVsn:11.2.0.1.0_0008, Instance:ms2008
                              Exiting hgogprd, rc=0
                              hostmstr: 8795972120576:      HOA After hoagprd
                              hostmstr: 8795972120576:      HOA Before hoainit
                              Entered hgoinit
                              HOCXU_COMP_CSET=1
                              HOCXU_DRV_CSET=178
                              HOCXU_DRV_NCHAR=1000
                              HOCXU_DB_CSET=178
                              HOCXU_SEM_VER=112000
                              Entered hgolofn at 2013/03/18-14:50:31
                              RC=-1 from HOSGIP for "PATH"
                              PATH from environment is "C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
                              Setting PATH to "C:\oracle\product\11.2.0\gtwhome_1\dg4msql\driver\lib;C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
                              Exiting hgolofn, rc=0 at 2013/03/18-14:50:31
                              HOSGIP for "HS_OPEN_CURSORS" returned "50"
                              HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                              HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                              HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                              HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
                              HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
                              HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
                              HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
                              HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
                              treat_SQLLEN_as_compiled = 1
                              Exiting hgoinit, rc=0 at 2013/03/18-14:50:31
                              hostmstr: 8795972120576:      HOA After hoainit
                              hostmstr: 8795972120576:      HOA Before hoalgon
                              Entered hgolgon at 2013/03/18-14:50:31
                               reco:0, name:OracleGateway, tflag:0
                              Entered hgosuec at 2013/03/18-14:50:31
                               uencoding=UTF16
                              Entered shgosuec at 2013/03/18-14:50:31
                              Exiting shgosuec, rc=0 at 2013/03/18-14:50:31
                               shgosuec() returned rc=0
                              Exiting hgosuec, rc=0 at 2013/03/18-14:50:31
                              HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
                              HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
                              HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
                              HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
                              HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
                              HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
                              HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
                              HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
                              HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
                              HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
                               using OracleGateway as default value for "HS_FDS_DEFAULT_OWNER"
                              HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                              Entered hgocont at 2013/03/18-14:50:31
                               HS_FDS_CONNECT_INFO = "[SQL2008.dbsoft.local]/SQL2008/AdventureWorks2008R2"
                               RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                              Entered hgogenconstr at 2013/03/18-14:50:31
                               dsn:[SQL2008.dbsoft.local]/SQL2008/AdventureWorks2008R2, name:OracleGateway
                               optn:
                              Entered hgocip at 2013/03/18-14:50:31
                               dsn:[SQL2008.dbsoft.local]/SQL2008/AdventureWorks2008R2
                              Exiting hgocip, rc=0 at 2013/03/18-14:50:31
                              Entered shgogohn at 2013/03/18-14:50:31
                               ohn is 'OraGtw11g_home1'
                              Exiting shgogohn, rc=0 at 2013/03/18-14:50:31
                              Entered hgocont_OracleCsidToIANA at 2013/03/18-14:50:31
                               Returning 2252
                              Exiting hgocont_OracleCsidToIANA at 2013/03/18-14:50:31
                              ##>Connect Parameters (len=277)<##
                              ## DRIVER=Oracle 11g dg4msql-OraGtw11g_home1;
                              ## SERVER=SQL2008.dbsoft.local\SQL2008;
                              ## Database=AdventureWorks2008R2;
                              #! UID=OracleGateway;
                              #! PWD=*
                              ## AnsiNPW=Yes;
                              ## QuotedId=Yes;
                              ## IANAAppCodePage=2252;
                              ## OctetSizeCalculation=1;
                              ## ArraySize=100;
                              ## PadVarbinary=0;
                              ## SupportNumericPrecisionGreaterThan38=1;
                              Exiting hgogenconstr, rc=0 at 2013/03/18-14:50:31
                              Entered hgopoer at 2013/03/18-14:50:31
                              hgopoer, line 233: got native error 5701 and sqlstate 01000; message follows...
                              [Oracle][ODBC SQL Server Driver][SQL Server]Changed database context to 'AdventureWorks2008R2'. {01000,NativeErr = 5701}[Oracle][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
                              Exiting hgopoer, rc=0 at 2013/03/18-14:50:31
                              hgocont, line 2686: calling SqlDriverConnect got sqlstate 01000
                              Entered hgolosf at 2013/03/18-14:50:31
                              ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFFFF 0x03FF 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                            0x0000 0x0000 0xFE00 0x3F5F 
                              Exiting hgolosf, rc=0 at 2013/03/18-14:50:31
                              DriverName:HGmsss23.dll, DriverVer:06.00.0073 (b0059, U0054)
                              DBMS Name:Microsoft SQL Server, DBMS Version:10.50.4000
                              Exiting hgocont, rc=0 at 2013/03/18-14:50:31 with error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
                              SQLGetInfo returns Y for SQL_CATALOG_NAME
                              SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
                              Exiting hgolgon, rc=0 at 2013/03/18-14:50:31
                              hostmstr: 8795947909120:      HOA After hoalgon
                              RPC Calling nscontrol(0), rc=0
                              hostmstr: 8795947909120: RPC Before Upload Caps
                              hostmstr: 8795947909120:      HOA Before hoaulcp
                              Entered hgoulcp at 2013/03/18-14:50:31
                              Entered hgowlst at 2013/03/18-14:50:31
                              Exiting hgowlst, rc=1 at 2013/03/18-14:50:31
                              SQLGetInfo returns Y for SQL_PROCEDURES
                              SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
                              TXN Capable:2, Isolation Option:0x2f
                              SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
                              SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
                              SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN
                               0 instance capabilities will be uploaded
                              Exiting hgoulcp, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoaulcp
                              hostmstr: 8795946860544: RPC After Upload Caps
                              hostmstr: 8795946860544: RPC Before Upload DDTR
                              hostmstr: 8795946860544:      HOA Before hoauldt
                              Entered hgouldt at 2013/03/18-14:50:32
                               NO instance DD translations were uploaded
                              Exiting hgouldt, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoauldt
                              hostmstr: 8795946860544: RPC After Upload DDTR
                              hostmstr: 8795946860544: RPC Before Begin Trans
                              hostmstr: 8795946860544:      HOA Before hoabegn
                              Entered hgobegn at 2013/03/18-14:50:32
                               tflag:0 , initial:1
                               hoi:0x12ee28, ttid (len 22) is ...
                                00: 4F52434C 2E336539 32343765 342E312E  [ORCL.3e9247e4.1.]
                                10: 312E3534 3130                        [1.5410]
                                               tbid (len 19) is ...
                                00: 4F52434C 5B312E31 2E353431 305D5B31  [ORCL[1.1.5410][1]
                                10: 2E345D                               [.4]]
                              Exiting hgobegn, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoabegn
                              hostmstr: 8795946860544: RPC After Begin Trans
                              hostmstr: 8795946860544: RPC Before Describe Table
                              hostmstr: 8795946860544:      HOA Before hoadtab
                              Entered hgodtab at 2013/03/18-14:50:32
                               count:1
                                table: Person.Address
                               Allocate hoada[0] @ 000000000546E0C0
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:0, octet:120, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:1, octet:120, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:4(City): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:60, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:30, sign:1, radix:10
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopoer at 2013/03/18-14:50:32
                              hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                              [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                              Exiting hgopoer, rc=0 at 2013/03/18-14:50:32
                              hgodtab, line 585: calling SQLFetch got sqlstate 22002
                              Entered hgodafr, cursor id 0 at 2013/03/18-14:50:32
                               Free hoada @ 000000000546E0C0
                              Exiting hgodafr, rc=0 at 2013/03/18-14:50:32
                              The hoada for table Person.Address follows...
                              hgodtab, line 904: NO hoada to print
                              Exiting hgodtab, rc=28500 at 2013/03/18-14:50:32 with error ptr FILE:hgodtab.c LINE:585 ID:Fetching result set for tble descr
                              hostmstr: 8795946860544:      HOA After hoadtab
                              hostmstr: 8795946860544: RPC After Describe Table
                              hostmstr: 8795946860544: RPC Before SQL Bundling
                              hostmstr: 8795946860544:      HOA Before hoxpars
                              Entered hgopars, cursor id 1 at 2013/03/18-14:50:32
                               type:0
                              SQL text from hgopars, id=1, len=32 ...
                                   00: 53454C45 4354202A 2046524F 4D202250  [SELECT * FROM "P]
                                   10: 6572736F 6E222E22 41646472 65737322  [erson"."Address"]
                              Exiting hgopars, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoxpars
                              hostmstr: 8795946860544: RPC After SQL Bundling
                              hostmstr: 8795946860544: RPC Before SQL Bundling
                              hostmstr: 8795946860544:      HOA Before hoxopen
                              Entered hgoopen, cursor id 1 at 2013/03/18-14:50:32
                              hgoopen, line 87: NO hoada to print
                               Deferred open until first fetch.
                              Exiting hgoopen, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoxopen
                              hostmstr: 8795946860544:      HOA Before hoxdscr
                              Entered hgodscr, cursor id 1 at 2013/03/18-14:50:32
                               Allocate hoada @ 000000000546E068
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:120, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:4(City): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:60, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:15/0, nullbl:0, octet:30, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:7(SpatialLocation): dtype:-4 (LONGVARBINARY), prc/scl:2147483647/0, nullbl:1, octet:30, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:8(rowguid): dtype:-11 (GUID), prc/scl:36/0, nullbl:0, octet:30, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              Entered hgopcda at 2013/03/18-14:50:32
                               Column:9(ModifiedDate): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:0, octet:30, sign:1, radix:0
                              Exiting hgopcda, rc=0 at 2013/03/18-14:50:32
                              hgodscr, line 910: Printing hoada @ 000000000546E068
                               MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
                               hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x20:NEGATIVE_HOADADTY)
                              DTY               NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
                                4 INTEGER       N          4          4   0/  0    0   0   0 AddressID
                               12 VARCHAR       N        120        120 128/ 60 1000   0  40 AddressLine1
                               12 VARCHAR       Y        120        120 128/ 60 1000   0  40 AddressLine2
                               12 VARCHAR       N         60         60 128/ 30 1000   0  40 City
                                4 INTEGER       N          4          4   0/  0    0   0   0 StateProvinceID
                               12 VARCHAR       N         30         30 128/ 15 1000   0  40 PostalCode
                               -4 LONGVARBINARY Y          0          0   0/  0    0   0  20 SpatialLocation
                                1 CHAR          N         36         36   0/  0    0   0   0 rowguid
                               91 DATE          N         16         16   0/  0    0   0   0 ModifiedDate
                              Exiting hgodscr, rc=0 at 2013/03/18-14:50:32
                              hostmstr: 8795946860544:      HOA After hoxdscr
                              hostmstr: 8795946860544: RPC After SQL Bundling
                              hostmstr: 8795946860544: RPC Before Get Statistics
                              hostmstr: 8795946860544:      HOA Before hoxtcis
                              Entered hgotcis at 2013/03/18-14:50:32
                               Calling SQLStatistics for Person.Address
                               IndexType=SQL_TABLE_STAT: cardinality=19614
                               New Index:PK_Address_AddressID, type=1, ASCENDING, UNIQUE, cardinality=19614
                                ordinal position = 1
                               New Index:AK_Address_rowguid, type=3, ASCENDING, UNIQUE, cardinality=19614
                                ordinal position = 1
                               New Index:IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode, type=3, ASCENDING, UNIQUE, cardinality=19614
                                ordinal position = 1
                                ordinal position = 2
                                ordinal position = 3
                                ordinal position = 4
                                ordinal position = 5
                               New Index:IX_Address_StateProvinceID, type=3, ASCENDING, NON-UNIQUE, cardinality=19614
                                ordinal position = 1
                               Calling SQLColumns for Person.Address
                               Column "AddressID": dtype=4, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=4
                               Column "AddressLine1": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=94
                               Column "AddressLine2": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=184
                               Column "City": dtype=-9, colsize=60, decdig=0, char_octet_length=60, cumulative avg row len=229
                               Column "StateProvinceID": dtype=4, colsize=10, decdig=0, char_octet_length=60, cumulative avg row len=233
                               Column "PostalCode": dtype=-9, colsize=30, decdig=0, char_octet_length=30, cumulative avg row len=255
                              Entered hgopoer at 2013/03/18-14:50:32
                              hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                              [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                              Exiting hgopoer, rc=0 at 2013/03/18-14:50:32
                              hgotcis, line 806: calling SQLFetch got sqlstate 22002
                              Exiting hgotcis, rc=28500 at 2013/03/18-14:50:32 with error ptr FILE:hgotcis.c LINE:806 ID:Fetch from SQLColumns resultset
                              hostmstr: 8795946860544:      HOA After hoxtcis
                              hostmstr: 8795946860544: RPC After Get Statistics
                              hostmstr: 8795946860544: RPC Before Commit Trans
                              hostmstr: 8795946860544:      HOA Before hoxclse
                              Entered hgoclse, cursor id 1 at 2013/03/18-14:50:37
                              Exiting hgoclse, rc=0 at 2013/03/18-14:50:37
                              hostmstr: 8795946860544:      HOA After hoxclse
                              hostmstr: 8795946860544:      HOA Before hoadafr
                              Entered hgodafr, cursor id 1 at 2013/03/18-14:50:37
                               Free hoada @ 000000000546E068
                              Exiting hgodafr, rc=0 at 2013/03/18-14:50:37
                              hostmstr: 8795946860544:      HOA After hoadafr
                              hostmstr: 8795946860544:      HOA Before hoacomm
                              Entered hgocomm at 2013/03/18-14:50:37
                               keepinfo:0, tflag:1
                                 00: 4F52434C 2E336539 32343765 342E312E  [ORCL.3e9247e4.1.]
                                 10: 312E3534 3130                        [1.5410]
                                               tbid (len 19) is ...
                                 00: 4F52434C 5B312E31 2E353431 305D5B31  [ORCL[1.1.5410][1]
                                 10: 2E345D                               [.4]]
                               cmt(0):
                              Entered hgocpctx at 2013/03/18-14:50:37
                              Exiting hgocpctx, rc=0 at 2013/03/18-14:50:37
                              Exiting hgocomm, rc=0 at 2013/03/18-14:50:37
                              hostmstr: 8795946860544:      HOA After hoacomm
                              hostmstr: 8795946860544: RPC After Commit Trans
                              hostmstr: 8795946860544: RPC Before Logoff
                              hostmstr: 8795946860544:      HOA Before hoalgof
                              Entered hgolgof at 2013/03/18-14:50:37
                               tflag:1
                              Exiting hgolgof, rc=0 at 2013/03/18-14:50:37
                              hostmstr: 8795948294144:      HOA After hoalgof
                              hostmstr: 8795948294144: RPC After Logoff
                              hostmstr: 8795948294144: RPC Before Exit Agent
                              hostmstr: 8795948294144:      HOA Before hoaexit
                              Entered hgoexit at 2013/03/18-14:50:37
                              Exiting hgoexit, rc=0
                              hostmstr: 8795966349312:      HOA After hoaexit
                              hostmstr: 8795966349312: RPC After Exit Agent
                              I do notice that there is a problems. From what you suggest and what I found on MSDN:
                              Sets the length/indicator buffer to SQL_NULL_DATA and proceeds to the next column if the data is NULL. If the data is NULL and no length/indicator buffer was bound, SQLFetch returns SQLSTATE 22002 (Indicator variable required but not supplied) for the row and proceeds to the next row. For information about how to determine the address of the length/indicator buffer, see "Buffer Addresses" in SQLBindCol. 
                              However I do have any idea how this si possible as there is not row with a null field.

                              Sincerely

                              Jan S.
                              • 12. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                                Mkirtley-Oracle
                                Jan,
                                Thanks for the trace.
                                It shows the problem happens at 2 points - on getting th information about the table -

                                Entered hgodtab at 2013/03/18-14:50:32
                                count:1
                                table: Person.Address
                                ...
                                ...column information
                                ...
                                Entered hgopoer at 2013/03/18-14:50:32
                                hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                                [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                                Exiting hgopoer, rc=0 at 2013/03/18-14:50:32
                                hgodtab, line 585: calling SQLFetch got sqlstate 22002

                                after getting the table column information using SQLStatistics and indexes -


                                Calling SQLStatistics for Person.Address
                                ....
                                ....
                                Calling SQLColumns for Person.Address
                                Column "AddressID": dtype=4, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=4
                                Column "AddressLine1": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=94
                                Column "AddressLine2": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=184
                                Column "City": dtype=-9, colsize=60, decdig=0, char_octet_length=60, cumulative avg row len=229
                                Column "StateProvinceID": dtype=4, colsize=10, decdig=0, char_octet_length=60, cumulative avg row len=233
                                Column "PostalCode": dtype=-9, colsize=30, decdig=0, char_octet_length=30, cumulative avg row len=255
                                Entered hgopoer at 2013/03/18-14:50:32
                                hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                                [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                                Exiting hgopoer, rc=0 at 2013/03/18-14:50:32

                                This is before the SQL has been issued to actually select the data.
                                To check if it is a problem with the statistics could you add the following to the gateway init<sid..ora file -

                                HS_FDS_SUPPORT_STATISTICS=FALSE

                                and run the select from a new SQLPLUS session.

                                Regards,
                                Mike
                                • 13. Re: Problem Connect DG Oracle 11gR2 - SQL Server
                                  jstem1177
                                  Hello Mike,

                                  I did as suggested and looks like there are still some problems. Even when i add the property in the init<sid>.ora to ignore the statistics
                                  HS_FDS_SUPPORT_STATISTICS=FALSE
                                  Oracle Corporation --- WEDNESDAY MAR 20 2013 10:05:08.697
                                  
                                  
                                  Heterogeneous Agent Release 
                                  11.2.0.1.0 
                                  
                                  
                                  
                                  
                                  Oracle Corporation --- WEDNESDAY MAR 20 2013 10:05:08.696
                                  
                                      Version 11.2.0.1.0
                                  
                                  Entered hgogprd
                                  HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
                                  Entered hgosdip
                                   setting HS_OPEN_CURSORS to default of 50
                                   HOSGIP returned value of "RECOVER" for HS_FDS_RECOVERY_ACCOUNT
                                   HOSGIP returned a value for HS_FDS_RECOVERY_PWD
                                   setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
                                   setting HS_IDLE_TIMEOUT to default of 0
                                   setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
                                   setting HS_NLS_NCHAR to default of "UCS2"
                                   setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
                                   setting HS_FDS_DATE_MAPPING to default of "DATE"
                                   setting HS_RPC_FETCH_REBLOCKING to default of "ON"
                                   setting HS_FDS_FETCH_ROWS to default of "100"
                                   setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
                                   setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
                                   setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
                                   setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
                                   setting HS_FDS_MAP_NCHAR to default of "TRUE"
                                   setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
                                   setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
                                   setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
                                   setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
                                   setting HS_FDS_QUERY_DRIVER to default of "FALSE"
                                   HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
                                   Parameter HS_FDS_QUOTE_IDENTIFIER is not set
                                   setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
                                   setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
                                   setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
                                   setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
                                   setting HS_FDS_DELAYED_OPEN to default of "TRUE"
                                   setting HS_FDS_WORKAROUNDS to default of "0"
                                  Exiting hgosdip, rc=0
                                   ORACLE_SID is "ms2008"
                                   Product-Info:
                                    Port Rls/Upd:1/0 PrdStat:0
                                    Agent:Oracle Database Gateway for MSSQL
                                    Facility:hsa
                                    Class:MSSQL, ClassVsn:11.2.0.1.0_0008, Instance:ms2008
                                  Exiting hgogprd, rc=0
                                  hostmstr: 8795972120576:      HOA After hoagprd
                                  hostmstr: 8795972120576:      HOA Before hoainit
                                  Entered hgoinit
                                  HOCXU_COMP_CSET=1
                                  HOCXU_DRV_CSET=178
                                  HOCXU_DRV_NCHAR=1000
                                  HOCXU_DB_CSET=178
                                  HOCXU_SEM_VER=112000
                                  Entered hgolofn at 2013/03/20-10:05:08
                                  RC=-1 from HOSGIP for "PATH"
                                  PATH from environment is "C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
                                  Setting PATH to "C:\oracle\product\11.2.0\gtwhome_1\dg4msql\driver\lib;C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
                                  Exiting hgolofn, rc=0 at 2013/03/20-10:05:08
                                  HOSGIP for "HS_OPEN_CURSORS" returned "50"
                                  HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                                  HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                                  HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                                  HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
                                  HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
                                  HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
                                  HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
                                  HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
                                  treat_SQLLEN_as_compiled = 1
                                  Exiting hgoinit, rc=0 at 2013/03/20-10:05:08
                                  hostmstr: 8795972120576:      HOA After hoainit
                                  hostmstr: 8795972120576:      HOA Before hoalgon
                                  Entered hgolgon at 2013/03/20-10:05:08
                                   reco:0, name:OracleGateway, tflag:0
                                  Entered hgosuec at 2013/03/20-10:05:08
                                   uencoding=UTF16
                                  Entered shgosuec at 2013/03/20-10:05:08
                                  Exiting shgosuec, rc=0 at 2013/03/20-10:05:08
                                   shgosuec() returned rc=0
                                  Exiting hgosuec, rc=0 at 2013/03/20-10:05:08
                                  HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
                                  HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
                                  HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
                                  HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
                                  HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
                                  HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
                                  HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
                                  HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
                                  HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
                                  HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
                                   using OracleGateway as default value for "HS_FDS_DEFAULT_OWNER"
                                  HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                                  Entered hgocont at 2013/03/20-10:05:08
                                   HS_FDS_CONNECT_INFO = "[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2"
                                   RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                                  Entered hgogenconstr at 2013/03/20-10:05:08
                                   dsn:[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2, name:OracleGateway
                                   optn:
                                  Entered hgocip at 2013/03/20-10:05:08
                                   dsn:[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2
                                  Exiting hgocip, rc=0 at 2013/03/20-10:05:08
                                  Entered shgogohn at 2013/03/20-10:05:08
                                   ohn is 'OraGtw11g_home1'
                                  Exiting shgogohn, rc=0 at 2013/03/20-10:05:08
                                  Entered hgocont_OracleCsidToIANA at 2013/03/20-10:05:08
                                   Returning 2252
                                  Exiting hgocont_OracleCsidToIANA at 2013/03/20-10:05:08
                                  ##>Connect Parameters (len=277)<##
                                  ## DRIVER=Oracle 11g dg4msql-OraGtw11g_home1;
                                  ## SERVER=SQL2008.dbs.local\SQL2008;
                                  ## Database=AdventureWorks2008R2;
                                  #! UID=OracleGateway;
                                  #! PWD=*
                                  ## AnsiNPW=Yes;
                                  ## QuotedId=Yes;
                                  ## IANAAppCodePage=2252;
                                  ## OctetSizeCalculation=1;
                                  ## ArraySize=100;
                                  ## PadVarbinary=0;
                                  ## SupportNumericPrecisionGreaterThan38=1;
                                  Exiting hgogenconstr, rc=0 at 2013/03/20-10:05:08
                                  Entered hgopoer at 2013/03/20-10:05:08
                                  hgopoer, line 233: got native error 5701 and sqlstate 01000; message follows...
                                  [Oracle][ODBC SQL Server Driver][SQL Server]Changed database context to 'AdventureWorks2008R2'. {01000,NativeErr = 5701}[Oracle][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
                                  Exiting hgopoer, rc=0 at 2013/03/20-10:05:08
                                  hgocont, line 2686: calling SqlDriverConnect got sqlstate 01000
                                  Entered hgolosf at 2013/03/20-10:05:08
                                  ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFFFF 0x03FF 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 
                                                                0x0000 0x0000 0xFE00 0x3F5F 
                                  Exiting hgolosf, rc=0 at 2013/03/20-10:05:09
                                  DriverName:HGmsss23.dll, DriverVer:06.00.0073 (b0059, U0054)
                                  DBMS Name:Microsoft SQL Server, DBMS Version:10.50.4000
                                  Exiting hgocont, rc=0 at 2013/03/20-10:05:09 with error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
                                  SQLGetInfo returns Y for SQL_CATALOG_NAME
                                  SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
                                  Exiting hgolgon, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795947909120:      HOA After hoalgon
                                  RPC Calling nscontrol(0), rc=0
                                  hostmstr: 8795947909120: RPC Before Upload Caps
                                  hostmstr: 8795947909120:      HOA Before hoaulcp
                                  Entered hgoulcp at 2013/03/20-10:05:09
                                  Entered hgowlst at 2013/03/20-10:05:09
                                  Exiting hgowlst, rc=1 at 2013/03/20-10:05:09
                                  SQLGetInfo returns Y for SQL_PROCEDURES
                                  SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
                                  TXN Capable:2, Isolation Option:0x2f
                                  SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
                                  SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
                                  SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN
                                   0 instance capabilities will be uploaded
                                  Exiting hgoulcp, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoaulcp
                                  hostmstr: 8795946860544: RPC After Upload Caps
                                  hostmstr: 8795946860544: RPC Before Upload DDTR
                                  hostmstr: 8795946860544:      HOA Before hoauldt
                                  Entered hgouldt at 2013/03/20-10:05:09
                                   NO instance DD translations were uploaded
                                  Exiting hgouldt, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoauldt
                                  hostmstr: 8795946860544: RPC After Upload DDTR
                                  hostmstr: 8795946860544: RPC Before Begin Trans
                                  hostmstr: 8795946860544:      HOA Before hoabegn
                                  Entered hgobegn at 2013/03/20-10:05:09
                                   tflag:0 , initial:1
                                   hoi:0x12ee28, ttid (len 23) is ...
                                    00: 4F52434C 2E336539 32343765 342E312E  [ORCL.3e9247e4.1.]
                                    10: 32302E35 343238                      [20.5428]
                                                   tbid (len 20) is ...
                                    00: 4F52434C 5B312E32 302E3534 32385D5B  [ORCL[1.20.5428][]
                                    10: 312E345D                             [1.4]]
                                  Exiting hgobegn, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoabegn
                                  hostmstr: 8795946860544: RPC After Begin Trans
                                  hostmstr: 8795946860544: RPC Before Describe Table
                                  hostmstr: 8795946860544:      HOA Before hoadtab
                                  Entered hgodtab at 2013/03/20-10:05:09
                                   count:1
                                    table: Person.Address
                                   Allocate hoada[0] @ 000000000552FF40
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:0, octet:120, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:1, octet:120, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:4(City): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:60, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:30, sign:1, radix:10
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopoer at 2013/03/20-10:05:09
                                  hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                                  [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                                  Exiting hgopoer, rc=0 at 2013/03/20-10:05:09
                                  hgodtab, line 585: calling SQLFetch got sqlstate 22002
                                  Entered hgodafr, cursor id 0 at 2013/03/20-10:05:09
                                   Free hoada @ 000000000552FF40
                                  Exiting hgodafr, rc=0 at 2013/03/20-10:05:09
                                  The hoada for table Person.Address follows...
                                  hgodtab, line 904: NO hoada to print
                                  Exiting hgodtab, rc=28500 at 2013/03/20-10:05:09 with error ptr FILE:hgodtab.c LINE:585 ID:Fetching result set for tble descr
                                  hostmstr: 8795946860544:      HOA After hoadtab
                                  hostmstr: 8795946860544: RPC After Describe Table
                                  hostmstr: 8795946860544: RPC Before SQL Bundling
                                  hostmstr: 8795946860544:      HOA Before hoxpars
                                  Entered hgopars, cursor id 1 at 2013/03/20-10:05:09
                                   type:0
                                  SQL text from hgopars, id=1, len=32 ...
                                       00: 53454C45 4354202A 2046524F 4D202250  [SELECT * FROM "P]
                                       10: 6572736F 6E222E22 41646472 65737322  [erson"."Address"]
                                  Exiting hgopars, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoxpars
                                  hostmstr: 8795946860544: RPC After SQL Bundling
                                  hostmstr: 8795946860544: RPC Before SQL Bundling
                                  hostmstr: 8795946860544:      HOA Before hoxopen
                                  Entered hgoopen, cursor id 1 at 2013/03/20-10:05:09
                                  hgoopen, line 87: NO hoada to print
                                   Deferred open until first fetch.
                                  Exiting hgoopen, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoxopen
                                  hostmstr: 8795946860544:      HOA Before hoxdscr
                                  Entered hgodscr, cursor id 1 at 2013/03/20-10:05:09
                                   Allocate hoada @ 000000000552FF40
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:120, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:4(City): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:60, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:15/0, nullbl:0, octet:30, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:7(SpatialLocation): dtype:-4 (LONGVARBINARY), prc/scl:2147483647/0, nullbl:1, octet:30, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:8(rowguid): dtype:-11 (GUID), prc/scl:36/0, nullbl:0, octet:30, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  Entered hgopcda at 2013/03/20-10:05:09
                                   Column:9(ModifiedDate): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:0, octet:30, sign:1, radix:0
                                  Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
                                  hgodscr, line 910: Printing hoada @ 000000000552FF40
                                   MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
                                   hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x20:NEGATIVE_HOADADTY)
                                  DTY               NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
                                    4 INTEGER       N          4          4   0/  0    0   0   0 AddressID
                                   12 VARCHAR       N        120        120 128/ 60 1000   0  40 AddressLine1
                                   12 VARCHAR       Y        120        120 128/ 60 1000   0  40 AddressLine2
                                   12 VARCHAR       N         60         60 128/ 30 1000   0  40 City
                                    4 INTEGER       N          4          4   0/  0    0   0   0 StateProvinceID
                                   12 VARCHAR       N         30         30 128/ 15 1000   0  40 PostalCode
                                   -4 LONGVARBINARY Y          0          0   0/  0    0   0  20 SpatialLocation
                                    1 CHAR          N         36         36   0/  0    0   0   0 rowguid
                                   91 DATE          N         16         16   0/  0    0   0   0 ModifiedDate
                                  Exiting hgodscr, rc=0 at 2013/03/20-10:05:09
                                  hostmstr: 8795946860544:      HOA After hoxdscr
                                  hostmstr: 8795946860544: RPC After SQL Bundling
                                  hostmstr: 8795946860544: RPC Before Get Statistics
                                  hostmstr: 8795946860544:      HOA Before hoxtcis
                                  Entered hgotcis at 2013/03/20-10:05:09
                                   Calling SQLStatistics for Person.Address
                                   IndexType=SQL_TABLE_STAT: cardinality=19614
                                   New Index:PK_Address_AddressID, type=1, ASCENDING, UNIQUE, cardinality=19614
                                    ordinal position = 1
                                   New Index:AK_Address_rowguid, type=3, ASCENDING, UNIQUE, cardinality=19614
                                    ordinal position = 1
                                   New Index:IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode, type=3, ASCENDING, UNIQUE, cardinality=19614
                                    ordinal position = 1
                                    ordinal position = 2
                                    ordinal position = 3
                                    ordinal position = 4
                                    ordinal position = 5
                                   New Index:IX_Address_StateProvinceID, type=3, ASCENDING, NON-UNIQUE, cardinality=19614
                                    ordinal position = 1
                                   Calling SQLColumns for Person.Address
                                   Column "AddressID": dtype=4, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=4
                                   Column "AddressLine1": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=94
                                   Column "AddressLine2": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=184
                                   Column "City": dtype=-9, colsize=60, decdig=0, char_octet_length=60, cumulative avg row len=229
                                   Column "StateProvinceID": dtype=4, colsize=10, decdig=0, char_octet_length=60, cumulative avg row len=233
                                   Column "PostalCode": dtype=-9, colsize=30, decdig=0, char_octet_length=30, cumulative avg row len=255
                                  Entered hgopoer at 2013/03/20-10:05:10
                                  hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
                                  [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
                                  Exiting hgopoer, rc=0 at 2013/03/20-10:05:10
                                  hgotcis, line 806: calling SQLFetch got sqlstate 22002
                                  Exiting hgotcis, rc=28500 at 2013/03/20-10:05:10 with error ptr FILE:hgotcis.c LINE:806 ID:Fetch from SQLColumns resultset
                                  hostmstr: 8795946860544:      HOA After hoxtcis
                                  hostmstr: 8795946860544: RPC After Get Statistics
                                  I will try this on some other tables, just to make sure its to this single table that has this effect.

                                  Jan S.

                                  Edited by: jstem1177 on Mar 20, 2013 10:13 AM