6 Replies Latest reply: Mar 13, 2013 8:56 AM by User353235 RSS

    ORA-28500 and ORA-02063 when querying sql server tables.

    User353235
      Hi,

      I am getting below error when querying (also with oracle reports) MS SQL 2008 database talbes which are having more rows. There is no issue with tables with less number of rows.
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      ORA-02063: preceding line from SQLSERVER

      When i am querying "select * from "Areas"@sqlserver;", it is retriving 195 rows and then displaying above error. This table contains 10716 rows.

      Below are our environment details:

      Oracle database 11G RAC (2 nodes) on RHEL 5 64 Bit, MS SQL 2008

      ODBC.INI
      [oracle@proddb1 ~]$ cat /etc/odbc.ini
      [ODBC Data Sources]
      mssql=MS SQL Server

      [mssql]
      Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
      Database=Ksa_Fakieh_SFA
      LogonID=OracleUser
      Password=0r@cle999
      Server=SJSQLV01.fakiehgroup.com
      QuotedId=YES
      AnsiNPW=YES
      [ODBC]
      IANAAppCodePage=4
      Trace=0
      UseCursorLib=0
      UseCursorLib=0


      INIT FILE:

      [oracle@proddb1 ~]$ cat $ORACLE_HOME/hs/admin/initDG4ODBC.ora
      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC

      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = mssql
      HS_FDS_TRACE_LEVEL = off
      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
      HS_FDS_SUPPORT_STATISTICS=FALSE
      HS_LANGUAGE = AMERICAN_AMERICA.AR8MSWIN1256
      HS_NLS_NCHAR=UCS2
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/etc/odbc.ini
      #


      Listener.ora:

      # ###############################################################

      #

      TRACE_FILE_LISTENER_PRODDB1 = fakieh1

      LISTEN_DG4ODBC =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = proddb1.fakiehgroup.com)(PORT = 1511))
      )
      )
      SID_LIST_LISTEN_DG4ODBC =
      (SID_LIST =
      (SID_DESC =
      (ORACLE_HOME = /u01/oracle/fakieh/db/tech_st/11.1.0)(SID_NAME=DG4ODBC)
      (ENV="LD_LIBRARY_PATH=/usr/lib64:/u01/oracle/fakieh/db/tech_st/11.1.0/lib")
      (PROGRAM=dg4odbc)
      )
      )

      #
      # Definition for RAC Database listener
      #

      LISTENER_PRODDB1 =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = proddb1-vip.fakiehgroup.com)(PORT = 1521)(IP = FIRST)))
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = proddb1)(PORT = 1521)(IP = FIRST)))
      )
      )

      SID_LIST_LISTENER_PRODDB1 =
      (SID_LIST =
      (SID_DESC = (ORACLE_HOME = /u01/oracle/fakieh/db/tech_st/11.1.0)(SID_NAME = fakieh1))
      )

      STARTUP_WAIT_TIME_LISTENER_PRODDB1 = 0
      CONNECT_TIMEOUT_LISTENER_PRODDB1 = 10
      TRACE_LEVEL_LISTENER_PRODDB1 = OFF

      LOG_DIRECTORY_LISTENER_PRODDB1 = /u01/oracle/fakieh/db/tech_st/11.1.0/network/admin
      LOG_FILE_LISTENER_PRODDB1 = fakieh1
      TRACE_DIRECTORY_LISTENER_PRODDB1 = /u01/oracle/fakieh/db/tech_st/11.1.0/network/admin
      TRACE_FILE_LISTENER_PRODDB1 = fakieh1
      ADMIN_RESTRICTIONS_LISTENER_PRODDB1 = ON
      SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_PRODDB1 = OFF


      IFILE=/u01/oracle/fakieh/db/tech_st/11.1.0/network/admin/fakieh1_proddb1/listener_ifile.ora


      Thank You,
      Mohammed.
        • 1. Re: ORA-28500 and ORA-02063 when querying sql server tables.
          Mkirtley-Oracle
          Mohammed,
          Without seeing trace information it is difficult to say why this problem is happening.
          However, could you add the following to the gateway initDG4ODBC.ora file -

          HS_RPC_FETCH_REBLOCKING= OFF
          HS_FDS_FETCH_ROWS = 1

          and try the select again from anew SLQPLUS session and see if the same problem happens ?

          Regards,
          Mike
          • 2. Re: ORA-28500 and ORA-02063 when querying sql server tables.
            User353235
            Hi Mike,

            Many thanks for your support. It resolved the issue.

            I have another issue that the Arabic data in the tables shows junk characters. when queried from oracle side.

            My current initDG4ODBC.ora file has below entries.

            # This is a sample agent init file that contains the HS parameters that are
            # needed for the Database Gateway for ODBC

            #
            # HS init parameters
            #
            HS_FDS_CONNECT_INFO = mssql
            HS_FDS_TRACE_LEVEL = off
            HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
            HS_FDS_SUPPORT_STATISTICS=FALSE
            HS_LANGUAGE = AMERICAN_AMERICA.AR8MSWIN1256
            HS_NLS_NCHAR=UCS2
            HS_RPC_FETCH_REBLOCKING= OFF
            HS_FDS_FETCH_ROWS = 1

            #
            # ODBC specific environment variables
            #
            set ODBCINI=/etc/odbc.ini
            #############################

            SQL> select * from v$nls_parameters;

            PARAMETER VALUE
            ---------------------------------------------------------------- ----------------------------------------------------------------
            NLS_LANGUAGE AMERICAN
            NLS_TERRITORY AMERICA
            NLS_CURRENCY $
            NLS_ISO_CURRENCY AMERICA
            NLS_NUMERIC_CHARACTERS .,
            NLS_CALENDAR GREGORIAN
            NLS_DATE_FORMAT DD-MON-RR
            NLS_DATE_LANGUAGE AMERICAN
            NLS_CHARACTERSET AR8MSWIN1256
            NLS_SORT BINARY
            NLS_TIME_FORMAT HH.MI.SSXFF AM
            NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
            NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
            NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
            NLS_DUAL_CURRENCY $
            NLS_NCHAR_CHARACTERSET AL16UTF16
            NLS_COMP BINARY
            NLS_LENGTH_SEMANTICS BYTE
            NLS_NCHAR_CONV_EXCP FALSE

            19 rows selected.


            Please suggest.

            Thank you,
            Mohammed.
            • 3. Re: ORA-28500 and ORA-02063 when querying sql server tables.
              Mkirtley-Oracle
              Mohammed,
              Thanaks for lettus know the select problem is resolved.
              What is the character set used by the SQL*Server database ? What is returned from the following in the SQL*server database -

              SELECT databasepropertyex('<db_name>', 'Collation')

              In a Latin database it will return something like -

              Latin1_General_CI_AS

              and then use that value in the following select -

              SELECT COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage');

              it should give a result similar to -

              1252

              Obviously, the results will be different in your database.

              Regards,
              Mike
              • 4. Re: ORA-28500 and ORA-02063 when querying sql server tables.
                User353235
                Hi Mike,

                below are the results form sql server database.

                SELECT databasepropertyex('<db_name>', 'Collation')
                Arabic_CI_AS

                SELECT COLLATIONPROPERTY('Arabic_CI_AS', 'CodePage');
                1256

                Thank You,
                Mohammed.
                • 5. Re: ORA-28500 and ORA-02063 when querying sql server tables.
                  Mkirtley-Oracle
                  Mohammed,
                  The NLS settings look like they should match, so to test further could you do the following -

                  - on SQL*Server create a simple table with 1 column, for example testcolumn
                  - paste the create statement used into the thread
                  - insert a Latin character - for example A - and an Arabic character so there are 2 rows
                  - issue the following select using query analyzer or similar -
                  select testcolumn, ascii(testcolumn) from testable
                  - and paste the results.

                  - from Oracle run the following -

                  select "testcolumn", dump("testcolumn") from "testtable"@dg4odbc_db_link ;

                  and paste the results.

                  Regards,
                  Mike
                  • 6. Re: ORA-28500 and ORA-02063 when querying sql server tables.
                    User353235
                    Hi Mike,

                    - on SQL*Server
                    Created table 'testchar' and inserted tow rows as below:

                    CREATE TABLE testchar ( data varchar(255));

                    INSERT INTO testchar (data) values ('A');

                    INSERT INTO testchar (data) values ('ح');

                    - from Microsof SQL Server Management Studio
                    select data, ascii(data) from testchar;

                    data     (No column name)
                    A     65
                    ح     205

                    - from Oracle
                    select "data", dump("data") from "testchar"@sqlserver
                    data     DUMP("DATA")
                    A     Typ=1 Len=1: 65
                    ط     Typ=1 Len=2: 216,173

                    Thank You,
                    Mohammed.