1 2 Previous Next 16 Replies Latest reply on Dec 20, 2010 3:30 PM by Mkirtley-Oracle

    ORACLE to SQl server dblink not working using DG4ODBC

    user13364785
      Hi,

      Please Help to relsove below issue

      oracle version :10.2.0.4.0
      AIX :5.3
      Gateway:11.2
      SQl server 2005

      I have installed gateway on SQl server machine.

      I am facing below error


      ERROR at line 1:
      ORA-00942: table or view does not exist
      [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
      'TBL_ARL_CRMUSERS'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server
      Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
      ORA-02063: preceding 2 lines from ODS


      Please help
        • 1. Re: ORACLE to SQl server dblink not working using DG4ODBC
          Kgronau-Oracle
          Are you sure the table TBL_ARL_CRMUSERS is in capital letters on the SQL Server side and it exists in the SQl Server database you connect to?
          Does it belong to the same schema or to a different user schema then the one specified in the db link?

          Commonly SQl Server objects are case sensitive and thus you have to write the name as defined on the SQL Server side and to preserver the case you have to put double quotes aroud the object name - for example:

          select * from "Tbl_Arl_TBL_ARL_CRMUSERS"@<your db link>
          • 2. Re: ORACLE to SQl server dblink not working using DG4ODBC
            user13364785
            table name is dbo.tbl_ARL_WNP_CRMUsers

            we have created separate user on sql server and given it sysadmin role in sql server 2005

            we have tired the below senacrio also

            select count(*) from "tbl_ARL_CRMUsers"@ODS;

            select count(*) from "dbo.tbl_ARL_CRMUsers"@ODS;

            But still facing problem

            Please help
            • 3. Re: ORACLE to SQl server dblink not working using DG4ODBC
              Mkirtley-Oracle
              Hi,
              What happens if you try -

              select count(*) from "dbo"."tbl_ARL_CRMUsers"@ODS;

              Regards,
              Mike
              • 4. Re: ORACLE to SQl server dblink not working using DG4ODBC
                user13364785
                Hi,

                Still facing error

                SQL> select count(*) from "dbo"."tbl_ARL_CRMUsers"@ODS;
                select count(*) from "dbo"."tbl_ARL_CRMUsers"@ODS
                *
                ERROR at line 1:
                ORA-00942: table or view does not exist
                [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
                'dbo.tbl_ARL_CRMUsers'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server
                Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
                ORA-02063: preceding 2 lines from ODS

                Please help
                • 5. Re: ORACLE to SQl server dblink not working using DG4ODBC
                  Mkirtley-Oracle
                  Hi,
                  The error is being returned from SQL*Server indicating the object can't be found.
                  Are you sure the table name and owner are correct and in the correct case and that the user in the database link has the privileges on the object ?

                  Can you select from any other objects in the SQL*Server database ? Are there any objects owned by the user in the database link that can be selected ?

                  Regards,
                  Mike
                  • 6. Re: ORACLE to SQl server dblink not working using DG4ODBC
                    user13364785
                    Please help


                    i am not able to select any other objects in the SQL*Server database
                    • 7. Re: ORACLE to SQl server dblink not working using DG4ODBC
                      Kgronau-Oracle
                      execute: select owner,table_name from all_catalog@ODS where table_name like "%ARL%";

                      and provide the output
                      • 8. Re: ORACLE to SQl server dblink not working using DG4ODBC
                        Mkirtley-Oracle
                        Hi,
                        Okay, what error do you get selecting any other object ? Does this work -

                        select * from dual@ODS ;

                        If not, post the full output displayed in SQLPLUS for the select and also for the select of another object that fails.

                        Does the connection for the System DSN work successfully using the user defined in the database link ?
                        Could you send a gateway debug trace for a failing select ?
                        Add -

                        HS_FDS_TRACE_LEVEL=debug

                        in the gateway init<sid>.ora file and issue a select from a new SQLPLUS session.

                        Regards,
                        Mike
                        • 9. Re: ORACLE to SQl server dblink not working using DG4ODBC
                          user13364785
                          SQL> select * from dual@ODS ;

                          D
                          -
                          X




                          Oracle Corporation --- MONDAY DEC 20 2010 19:45:22.026


                          Heterogeneous Agent Release
                          11.2.0.1.0




                          Oracle Corporation --- MONDAY DEC 20 2010 19:45:22.026

                          Version 11.2.0.1.0

                          Entered hgogprd
                          HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
                          Entered hgosdip
                          setting HS_OPEN_CURSORS to default of 50
                          setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
                          setting HS_FDS_RECOVERY_PWD to default value
                          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 "TRUE"
                          setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
                          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"
                          Default value of 32 assumed for HS_FDS_SQLLEN_INTERPRETATION
                          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 "ODSCRM"
                          Product-Info:
                          Port Rls/Upd:1/0 PrdStat:0
                          Agent:Oracle Database Gateway for ODBC
                          Facility:hsa
                          Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:ODSCRM
                          Exiting hgogprd, rc=0
                          Entered hgoinit
                          HOCXU_COMP_CSET=1
                          HOCXU_DRV_CSET=178
                          HOCXU_DRV_NCHAR=1000
                          HOCXU_DB_CSET=31
                          HOCXU_SEM_VER=102000
                          Entered hgolofn at 2010/12/20-19:45:22
                          Exiting hgolofn, rc=0 at 2010/12/20-19:45:22
                          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"
                          Invalid value of 32 given for HS_FDS_SQLLEN_INTERPRETATION
                          treat_SQLLEN_as_compiled = 1
                          Exiting hgoinit, rc=0 at 2010/12/20-19:45:22
                          Entered hgolgon at 2010/12/20-19:45:22
                          reco:0, name:ODSUSER, tflag:0
                          Entered hgosuec at 2010/12/20-19:45:22
                          Exiting hgosuec, rc=0 at 2010/12/20-19:45:22
                          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 ODSUSER as default value for "HS_FDS_DEFAULT_OWNER"
                          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                          Entered hgocont at 2010/12/20-19:45:22
                          HS_FDS_CONNECT_INFO = "ODSCRM"
                          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                          Entered hgogenconstr at 2010/12/20-19:45:22
                          dsn:ODSCRM, name:ODSUSER
                          optn:
                          Entered hgocip at 2010/12/20-19:45:22
                          dsn:ODSCRM
                          Exiting hgocip, rc=0 at 2010/12/20-19:45:22
                          Exiting hgogenconstr, rc=0 at 2010/12/20-19:45:22
                          Entered hgopoer at 2010/12/20-19:45:22
                          hgopoer, line 233: got native error 5701 and sqlstate 01000; message follows...
                          [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. {01000,NativeErr = 5701}[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}
                          Exiting hgopoer, rc=0 at 2010/12/20-19:45:22
                          hgocont, line 2686: calling SqlDriverConnect got sqlstate 01000
                          Entered hgolosf at 2010/12/20-19:45:22
                          Exiting hgolosf, rc=0 at 2010/12/20-19:45:22
                          DriverName:SQLSRV32.DLL, DriverVer:03.86.3959
                          DBMS Name:Microsoft SQL Server, DBMS Version:09.00.4285
                          Exiting hgocont, rc=0 at 2010/12/20-19:45:22 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 2010/12/20-19:45:22
                          Entered hgoulcp at 2010/12/20-19:45:22
                          Entered hgowlst at 2010/12/20-19:45:22
                          Exiting hgowlst, rc=0 at 2010/12/20-19:45:22
                          SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
                          TXN Capable:2, Isolation Option:0xf
                          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
                          SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
                          SQLGetInfo returns Y for SQL_COLUMN_ALIAS
                          15 instance capabilities will be uploaded
                          capno:1989, context:0x00000000, add-info: 0
                          capno:1991, context:0x0001ffff, add-info: 0
                          capno:1992, context:0x0001ffff, add-info: 0
                          capno:3042, context:0x00000000, add-info: 0, translation:"42"
                          capno:3047, context:0x00000000, add-info: 0, translation:"57"
                          capno:3049, context:0x00000000, add-info: 0, translation:"59"
                          capno:3050, context:0x00000000, add-info: 0, translation:"60"
                          capno:3066, context:0x00000000, add-info: 0
                          capno:3067, context:0x00000000, add-info: 0
                          capno:3068, context:0x00000000, add-info: 0
                          capno:3069, context:0x00000000, add-info: 0
                          capno:3500, context:0x00000001, add-info: 91, translation:"42"
                          capno:3501, context:0x00000001, add-info: 93, translation:"57"
                          capno:3502, context:0x00000001, add-info: 107, translation:"59"
                          capno:3503, context:0x00000001, add-info: 110, translation:"60"
                          Exiting hgoulcp, rc=0 at 2010/12/20-19:45:22
                          Entered hgouldt at 2010/12/20-19:45:22
                          NO instance DD translations were uploaded
                          Exiting hgouldt, rc=0 at 2010/12/20-19:45:22
                          Entered hgobegn at 2010/12/20-19:45:22
                          tflag:0 , initial:1
                          hoi:0x12f094, ttid (len 26) is ...
                          00: 4F445350 44422E64 62653064 3831332E [ODSPDB.dbe0d813.]
                          10: 312E3434 2E383639 3339 [1.44.86939]
                          tbid (len 10) is ...
                          0: 01002C00 9B530100 0104 [..,..S....]
                          Exiting hgobegn, rc=0 at 2010/12/20-19:45:22
                          Entered hgodtab at 2010/12/20-19:45:22
                          count:1
                          table: dbo.tbl_ARL_CRMUsers
                          Allocate hoada[0] @ 027977A4
                          Free hoada[0] @ 027977A4
                          Entered hgopdsc at 2010/12/20-19:45:22
                          Describing procedure dbo.tbl_ARL_CRMUsers
                          Output hoada
                          hgopdsc, line 1426: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2010/12/20-19:45:22
                          The hoada for table dbo.tbl_ARL_CRMUsers follows...
                          hgodtab, line 904: NO hoada to print
                          Exiting hgodtab, rc=0 at 2010/12/20-19:45:22
                          Entered hgopars, cursor id 1 at 2010/12/20-19:45:22
                          type:0
                          SQL text from hgopars, id=1, len=38 ...
                          00: 53454C45 4354202A 2046524F 4D202264 [SELECT * FROM "d]
                          10: 626F222E 2274626C 5F41524C 5F43524D [bo"."tbl_ARL_CRM]
                          20: 55736572 7322 [Users"]
                          Entered hgopoer at 2010/12/20-19:45:22
                          hgopoer, line 233: got native error 208 and sqlstate 42S02; message follows...
                          [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tbl_ARL_CRMUsers'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}
                          Exiting hgopoer, rc=0 at 2010/12/20-19:45:22
                          hgopars, line 526: calling SQLNumResultCols got sqlstate 42S02
                          Exiting hgopars, rc=942 at 2010/12/20-19:45:22
                          Entered hgocomm at 2010/12/20-19:46:23
                          keepinfo:0, tflag:1
                          00: 4F445350 44422E64 62653064 3831332E [ODSPDB.dbe0d813.]
                          10: 312E3434 2E383639 3339 [1.44.86939]
                          tbid (len 10) is ...
                          0: 01002C00 9B530100 0104 [..,..S....]
                          cmt(0):
                          Entered hgocpctx at 2010/12/20-19:46:23
                          Exiting hgocpctx, rc=0 at 2010/12/20-19:46:23
                          Exiting hgocomm, rc=0 at 2010/12/20-19:46:23
                          Entered hgolgof at 2010/12/20-19:46:23
                          tflag:1
                          Exiting hgolgof, rc=0 at 2010/12/20-19:46:23
                          Entered hgoexit at 2010/12/20-19:46:23
                          Exiting hgoexit, rc=0
                          • 10. Re: ORACLE to SQl server dblink not working using DG4ODBC
                            Kgronau-Oracle
                            Did you see, you connect to the master database:
                            Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. {01000,NativeErr = 5701}[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

                            Are you sure, the table you try to select from is located in the master database?
                            select owner,table_name from all_catalog@ODS where table_name like "%ARL%";

                            Else check the ODBC configuration and make sure you correctly specify the database in the ODBC connection which contains your tables.

                            Edited by: kgronau on Dec 20, 2010 3:58 PM
                            • 11. Re: ORACLE to SQl server dblink not working using DG4ODBC
                              user13364785
                              SQL> select owner,table_name from all_catalog@ODS where table_name like "%ARL%";
                              select owner,table_name from all_catalog@ODS where table_name like "%ARL%"
                              *
                              ERROR at line 1:
                              ORA-00904: "%ARL%": invalid identifier


                              Please help dear ALL.
                              • 12. Re: ORACLE to SQl server dblink not working using DG4ODBC
                                Mkirtley-Oracle
                                Hi,
                                The trace shows you are connecting to the 'master' database -

                                [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'.

                                This will be defined in the System DSN configured for the connection.

                                Is this the database that contains the table dbo.tbl_ARL_CRMUsers ?

                                Regards,
                                Mike
                                • 13. Re: ORACLE to SQl server dblink not working using DG4ODBC
                                  user13364785
                                  Hi,

                                  But i am trying to connect to CRM_LinkedDB sql server database .


                                  If it is trying to connect to master database please help me to connect it to CRM_LinkedDB sql server database


                                  Please Help Dear all
                                  • 14. Re: ORACLE to SQl server dblink not working using DG4ODBC
                                    Mkirtley-Oracle
                                    Hi,
                                    The SQL*Server database to which DG4ODBC connects is defined in the System DSN created which is then specified in the parameter -

                                    HS_FDS_CONNECT_INFO

                                    During the configuration of the system DSN you choose the database to which you connect.

                                    Regards,
                                    Mike
                                    1 2 Previous Next