This discussion is archived
13 Replies Latest reply: Mar 20, 2013 7:14 AM by jstem1177 RSS

Problem Connect DG Oracle 11gR2 - SQL Server

jstem1177 Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points