This discussion is archived
5 Replies Latest reply: Jun 6, 2013 3:07 AM by kgronau RSS

Cannot do describe properly with DG4MSQL

ultradumb Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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