6 Replies Latest reply: Nov 13, 2013 3:02 AM by Charan RSS

    DB Link to SQL server 2012 from R12 Instance

    Charan

      Dear all,

       

      I have to create a DB Link to SQL server 2012 from my DEV instance.

       

      I found the URL : Heterogeneous Database connections - Oracle to SQL Server but not much clear about that.

       

      Please help.

       

      Regards,

      Charan

        • 1. Re: DB Link to SQL server 2012 from R12 Instance
          Asif Muhammad

          Hi Charan,

           

          Yes, Correct you will have to create a hetrogenous Connectivity.

           

          Please provide your Enviornment details:

           

          Please see the following links for detail steps:

          www.myoracleworld.net - SOA-Install-SOA10G-using-11gRAC-db

          http://stackoverflow.com/questions/307636/how-do-you-setup-a-linked-server-to-an-oracle-database-on-sql-2000-2005

           

          Thanks &

          Best Regards,

          • 2. Re: DB Link to SQL server 2012 from R12 Instance
            Charan

            Hi,

             

            I have used Data direct SQL Serve Wire Protocol on my AIX. I have done the setup. But when I query a sql serve table I am facing an error.

             

            DataDirect MS SQL Server Incorrect systax near 'table_name' HY000 Native Err 102

             

            Please help.

             

            Regards,

            Charan

            • 3. Re: DB Link to SQL server 2012 from R12 Instance
              Charan

              Hi,

               

              Below is the exact error

               

              SQL> select count(*) from TABLENAME@DBLINK;

                                   *
              ERROR at line 1:
              ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
              [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL
              Server]Incorrect syntax near 'TABLENAME'. {HY000,NativeErr = 102}
              ORA-02063: preceding 2 lines from DBLINK

              • 4. Re: DB Link to SQL server 2012 from R12 Instance
                Anar Godjaev

                HI,

                 

                Cause: The Oracle database link created for   the foreign datasource has either no credentials or incorrect   credentials.


                Action:   Recreate the Oracle database link with the proper username and   password.

                 

                Note: When  resolving the ORA-28500 error it's important to   note that the username and password must be in double quotes

                 

                Heterogeneous Database connections - Oracle to SQL Server

                 

                 

                For example, this would create a viable ODBC database link between   Oracle and SQL server, being careful to encase the user name and   password in double quotes.  This is perfect for migrating to   Oracle:

                 

                create database link ODBC connect   to "sa" identified by "pencil" using 'hsodbc'.

                 

                 

                Thank you

                 


                • 5. Re: DB Link to SQL server 2012 from R12 Instance
                  Asif Muhammad

                  Hi Charan,

                   

                  Please see following notes, as it might help you:

                  ODBC Error[DataDirect][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Qtr' (Doc ID 1369800.1)

                  How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)

                  DG4ODBC Fails With Error Incorrect Syntax using 3rd party ODBC drivers (Doc ID 603662.1)

                  Error ORA-28500 [ODBC SQL Server Driver] NativeErr = 102 Using SUBSTR From DG4MSQL (Doc ID 1182280.1)

                  How to Configure DG4ODBC on Linux x86 32bit or on HP-UX RISC (DG4ODBC 11.1 only) to Connect to Non-Oracle Databases post install (Doc ID 466228.1)

                   

                  Thanks &

                  Best Regards,

                  • 6. Re: DB Link to SQL server 2012 from R12 Instance
                    Charan

                    Hi Asif,

                     

                    Thanks for you reply.

                     

                    I have enabled the Quoted Identifiers but still facing the same issue. Please see my setup below

                     

                    OS : AIS 6.1 TL 2

                    R12.1.3 on 11.2.0.3

                     

                     

                     

                     

                     

                    cd $ORACLE_HOME/hs/admin

                    $ cat initEnterprise_Access.ora

                    #
                    # HS init parameters
                    #
                    HS_FDS_CONNECT_INFO =mssqlserver
                    HS_FDS_TRACE_LEVEL = off
                    HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/odbc.so
                    #
                    # ODBC specific environment variables
                    #
                    set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini
                    #
                    # Environment variables required for the non-Oracle system
                    #
                    #set <envvar>=<value>

                    $

                     

                     


                    cat /opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini
                    [ODBC Data Sources]
                    SQL Server Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

                    [ODBC]
                    IANAAppCodePage=4
                    InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_71
                    Trace=0
                    TraceFile=odbctrace.out
                    TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

                    [mssqlserver]
                    Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so
                    HostName=10.0.9.10
                    Database=Enterprise_Access
                    PortNumber=1433
                    EnableQuotedIdentifers=1
                    $


                    listener :

                    SID_LIST_SQLDEV =
                       (SID_LIST =
                          (SID_DESC =
                           (SID_NAME=Enterprise_Access)
                          (ORACLE_HOME=/orahome/oracle/DEV/db/tech_st/11.2.0)
                          (ENV="LIBPATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:/orahome/oracle/DEV/db/tech_st/11.2.0/lib")
                          (PROGRAM=dg4odbc)
                          )
                       )

                    SQLDEV =
                       (DESCRIPTION_LIST =
                          (DESCRIPTION =
                             (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdev) (PORT = 1530))
                             )
                          )
                       )

                    DEV =
                      (DESCRIPTION_LIST =
                        (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdev)(PORT = 1525))
                        )
                      )

                    SID_LIST_DEV =
                      (SID_LIST =
                        (SID_DESC =
                          (ORACLE_HOME= /orahome/oracle/DEV/db/tech_st/11.2.0)
                          (SID_NAME = DEV)
                        )
                      )

                     


                    tns entry :


                    SQLDEV  =
                       (DESCRIPTION=
                          (ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev)(PORT=1530))
                          (CONNECT_DATA=(SID=Enterprise_Access))
                          (HS=OK)
                       )


                     

                    Regards,

                    Charan