5 Replies Latest reply: Jun 6, 2013 5:07 AM by Kgronau-Oracle RSS

    Cannot do describe properly with DG4MSQL

    ultradumb
      I have installed Oracle gateway for Microsoft SQL Server version 11.2.0.2.0 (on AIX 6.1L 64-bit), as my understanding is that it should be faster and provide for more functionality than DG4ODBC. I am accessing Microsoft SQL Server 2008.
      However, when using the gateway, I cannot properly 'describe' tables in MSSQL. What I get is:

      --------
      SQL> desc s4user@imosprod;
      ERROR:
      ORA-12663: Services required by client not available on the server
      ORA-02063: preceding line from IMOSPROD
      --------

      Apparently, as a result, I cannot use COUNT() function in SQL, too, as it returns some binary data.
      -------
      SQL> select count(*) from s4user@imosprod;

      COUN
      ====
      B
      SQL> select dump(count(*)) from s4user@imosprod;

      DUMP(COUNT(*))
      =================
      Typ=2 Len=2: 193,67
      --------

      After doing a 'select' from the MSSQL table, I can do describe, but then all columns are displayed as type VARCHAR2.

      -------
      SQL> desc s4user@imosprod;
      Name Null? Type
      ======================================
      _sqlid                                    NOT NULL VARCHAR2(21)
      userNo VARCHAR2(4)
      userName VARCHAR2(128)
      userInitials VARCHAR2(5)
      userPassword VARCHAR2(100)
      userType VARCHAR2(10)
      userFullName VARCHAR2(100)
      userEmail VARCHAR2(128)
      userFlags VARCHAR2(4)
      userDomain VARCHAR2(100)
      ldapUserName VARCHAR2(128)
      ldapGuid VARCHAR2(36)
      imosGuid VARCHAR2(36)
      -------

      To me it seems like a data type translation issue. There is an error in trace file, which happens every time I do a query or describe for MSSQL tables:
      ----------
      DBMS Name:Microsoft SQL Server, DBMS Version:10.00.4000
      .....
      hgopoer, line 231: got native error 1007 and sqlstate 22003; message follows...
      [Oracle][ODBC SQL Server Driver][SQL Server]The number '042100421004210042110421104211042190421904219' is out of the range for numeric rep
      resentation (maximum precision 38). {22003,NativeErr = 1007}[Oracle][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '04210042100
      4210042110421104211042190421904219'. {10103,NativeErr = 102}
      Exiting hgopoer, rc=0 at 2013/06/05-09:22:19
      hgoulcp, line 1957: calling SQLGetTypeInfo got sqlstate 22003
      Exiting hgoulcp, rc=28500 at 2013/06/05-09:22:19 with error ptr FILE:hgoulcp.c LINE:1957 ID:SQLGetTypeInfo: LONGVARCHAR
      Entered hgouldt at 2013/06/05-09:22:19
      NO instance DD translations were uploaded
      Exiting hgouldt, rc=0 at 2013/06/05-09:22:19
      ------------

      However, later on in trace file, I can see proper description of columns:
      ----------
      hgodscr, line 457: Printing hoada @ 1107a8e88
      MAX:13, ACTUAL:13, BRC:100, WHT=5 (SELECT_LIST)
      hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)
      DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
      3 DECIMAL N 21 21 19/ 0 0 0 0 _sqlid
      4 INTEGER Y 4 4 0/ 0 0 0 0 userNo
      12 VARCHAR Y 128 128 0/ 0 0 0 200 userName
      12 VARCHAR Y 5 5 0/ 0 0 0 200 userInitials
      12 VARCHAR Y 100 100 0/ 0 0 0 200 userPassword
      12 VARCHAR Y 10 10 0/ 0 0 0 200 userType
      12 VARCHAR Y 100 100 0/ 0 0 0 200 userFullName
      12 VARCHAR Y 128 128 0/ 0 0 0 200 userEmail
      4 INTEGER Y 4 4 0/ 0 0 0 0 userFlags
      12 VARCHAR Y 100 100 0/ 0 0 0 200 userDomain
      12 VARCHAR Y 128 128 0/ 0 0 0 200 ldapUserName
      1 CHAR Y 36 36 0/ 0 0 0 0 ldapGuid
      1 CHAR Y 36 36 0/ 0 0 0 0 imosGuid
      Exiting hgodscr, rc=0 at 2013/06/05-09:22:19
      ----------

      Anyone got a clue on how to proceed with troubleshooting?
        • 1. Re: Cannot do describe properly with DG4MSQL
          Kgronau-Oracle
          Can you please post the create table statement of the SQL Server table to get an idea how it is defined at the SQL Server side?

          Thanks,
          Klaus
          • 2. Re: Cannot do describe properly with DG4MSQL
            ultradumb
            Sure, the script is:

            CREATE TABLE [dbo].[s4user] (
            [_sqlid] bigint NOT NULL,
            [userNo] int NULL,
            [userName] varchar(128) NULL,
            [userInitials] varchar(5) NULL,
            [userPassword] varchar(100) NULL,
            [userType] varchar(10) NULL,
            [userFullName] varchar(100) NULL,
            [userEmail] varchar(128) NULL,
            [userFlags] int NULL,
            [userDomain] varchar(100) NULL,
            [ldapUserName] varchar(128) NULL,
            [ldapGuid] uniqueidentifier NULL,
            [imosGuid] uniqueidentifier NULL)
            ON [PRIMARY]
            WITH (DATA_COMPRESSION = NONE);
            • 3. Re: Cannot do describe properly with DG4MSQL
              Kgronau-Oracle
              works for me using 11.2.0.3 and 11.2.0.2 gateway release:
              SQL> desc "s4user"@DG4MSQL_EMGTW_1122_DB
              Name Null? Type
              ----------------------------------------- -------- ----------------------------
              _sqlid                                    NOT NULL NUMBER(20)
              userNo NUMBER(10)
              userName VARCHAR2(128)
              userInitials VARCHAR2(5)
              userPassword VARCHAR2(100)
              userType VARCHAR2(10)
              userFullName VARCHAR2(100)
              userEmail VARCHAR2(128)
              userFlags NUMBER(10)
              userDomain VARCHAR2(100)
              ldapUserName VARCHAR2(128)
              ldapGuid CHAR(36)
              imosGuid CHAR(36)

              Maybe it would make sense to clean up the HS catalog first.

              Connect to your Oracle database as sysdba, then run scripts located in $ORACLE_HOME/rdbms/admin
              1. to drop the HS catalog, please execute catnohs
              2. execute a commit
              3. now recreate the catalog using caths
              4. again execute a commit.

              then exit from all sessions and open a new SQL*plus and test the database link using the gateway (the gateway will now determine that no gateway class exists in the HS catalog and upload its capabilities again into the HS catalog).

              If it still fails, please post your gateway init file.
              - Klaus
              • 4. Re: Cannot do describe properly with DG4MSQL
                ultradumb
                Done exactly as per your instruction and it worked! In fact I already did that cleanup, but I forgot to execute 'commit' after catnohs and caths scripts.

                Now things looks much better.

                ----
                SQL> desc s4user@imosprod;
                Name Null? Type
                ----------------------------------------- -------- ----------------------------
                _sqlid                                    NOT NULL NUMBER(19)
                userNo NUMBER(10)
                userName VARCHAR2(128)
                userInitials VARCHAR2(5)
                userPassword VARCHAR2(100)
                userType VARCHAR2(10)
                userFullName VARCHAR2(100)
                userEmail VARCHAR2(128)
                userFlags NUMBER(10)
                userDomain VARCHAR2(100)
                ldapUserName VARCHAR2(128)
                ldapGuid CHAR(36)
                imosGuid CHAR(36)
                ----

                So, the case is resolved, thank you very much indeed.

                PS. In the meantime I managed to install Oracle DG4MSQL gateway version *11.2.0.3* to a different Oracle home on the same AIX server (just to check if updated version can do any better) and here I am getting real errors. But this, maybe, is a subject to new thread.
                • 5. Re: Cannot do describe properly with DG4MSQL
                  Kgronau-Oracle
                  yes, please start a new thread (or log a service request at Oracle Support) for your issues and feel free to spend some points for useful/solved answer.

                  - Klaus