Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Access MS SQL Server tables from Oracle 9i

Re: Access MS SQL Server tables from Oracle 9i

From: Terminator <kramakrishnabest_at_gmail.com>
Date: 24 May 2005 02:03:14 -0700
Message-ID: <1116925394.850249.58600@o13g2000cwo.googlegroups.com> 





For the tables to be accessed we can use the generic connectivity. This
is using the hsodbc that is heterogenous Connectivity Drivers. The
procedure is here which we have created as a white paper. Please go
step by step




    
  
  1.   CREATING THE SYSTEMDSN THROUGH THE DATASOURCE:-





The following procedure enables you to define a system DSN in the
Windows98/NT/2000/2000/serverXP ODBC Data Source Administrator,



1.1. From the Start menu, choose Settings > Control Panel and select
the ODBC


      icon. If it is in the windows 98.  OR
1.1. If it is in windows NT/2000/XP then goto the Start menu, choose
Settings > Control


      Panel and select the Administrator tools and then select the Data
Sources (ODBC).



1.2. Select the system DSN tab to display the system data sources.



1.3. Click Add.



1.4. From the list of installed ODBC drivers, select the name of the
driver that the


      data source will use. For example, select SQL Server.



1.5. Click Finish.



1.6. Enter a name for the DSN and an optional description. Enter other
information


      depending on the ODBC driver. For example, for SQL Server enter
the SQL


      Server machine. Press Next.



      Note :-  The name entered for the DSN must match the value of the



      parameter HS_FDS_CONNECT_INFO that will be  specified in
initHS_SID.ora.



1.7. Then select the option (With SQL Server authentication using login
ID and password


       entered by the user)



1.8. Then select the check box (Connect to SQL server to obtain default
settings for the


       additional configuration options).



1.9.  Now in the Text Box (Login ID: ) type the MS-SQL server login and
in the Text Box


        of (Password: ) type the Password of the MS-SQL server user’s
password.


        Then press Next.



        Note :- Every time this user will be used in order to connect
to MS-SQL server.


        And tables available in this user’s schema will be available
to query. So no need of


        giving the user and password again and again to connect to the
database of MSSQL


         server.



1.10. select the check box (Change the default database to) then it
will show that


        particular users database which you have previously mentioned
in point 1.9 above.


        Select that database from the List Of Values (LOV). Then click
next and then Finish.



1.11. Then the ODBC Microsoft SQL Server Setup window will be
displayed. Here check


         the connection by pressing the test button which checks the
connectivity to


         MS-SQL Server with the authentication of User entered in point
1.9. If the


         connection is successfully then the MS-SQL username, MS-SQL
password and the


         MS-SQL servername are proper. If it is unsuccessful then check
then check the


         username, password and the server name.



1.12 .    After creating the system DSN, click OK to exit the ODBC Data
Source


           Administrator.



          Note :- Now Please keep in mind the SystemDSN name you gave
for the source


          MS-SQL Server. That was the name in the 6th point.






2. ENTRIES TO THE TNSNAMES.ORA FILE. :-



Here it is considered as the name of the HSMSQL as the systemDSN name
You created through odbc.



2.1

       HSMSQL=
           (DESCRIPTION=
                        (ADDRESS= (PROTOCOL=tcp)
                                              (HOST=oracle server IP)
                                              (PORT=1521)
                         )
                        (CONNECT_DATA =
                                         (SERVICE_NAME=HSMSQL)  ->
Needs to match with the
                                                     sid in the
listener.ora
                         )
                        (HS = OK)  HS clause goes in the description
            )





2.2  Here the HSMSQL is the name of the systemDSN created to connect to
the


       MS-SQL-SERVER through the odbc datasource administrator. The IP
is the IP of the


       Oracle server where the oracle database is installed or through
which your are trying


       to connect the ms-sql server database.



2.3	 Save and Exit.







3.	ENTRIES TO THE LISTENER.ORA   FILE. :-
Here it is considered as the name of the HSMSQL as the systemDSN name
You created through odbc.



3.1  (SID_DESC =


	(SID_NAME=HSMSQL)  Needs to match the sid in tnsnames.ora
	 (ORACLE_HOME=D:\oracle\product\10.1.0\Db_3)
	 (PROGRAM =hsodbc)  hsodbc is the executable
         )




3.2  Save and Exit.





4.	STARTING THE LISTENER :-



  Reload  the Listener, and confirm that the HSODBC service is running.




4.1 After reloading the listener the below thing should be show, now
here


      the hsmsql is the systemdsn name is assumed in the listener.ora
file

      For example :
      DOS> lsnrctl reload

      LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on
26-
      AUG-2004 10:04:03

      Copyright (c) 1991, 2004, Oracle.  All rights reserved.
     Connecting to
     (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC)))
     The command completed successfully.
     - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - -
     DOS> lsnrctl services

     LSNRCTL for 32-bit Windows: Version 9.2.0.4.0 - Production on 01-
     JUL-2004 12:00:00

     Copyright (c) 1991, 2002, Oracle Corporation.  All rights
reserved.
     Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC)


(KEY=EXTPROC0)))



  Services Summary...


  Service "HSMSQL" has 1 instance(s).


  Instance "HSMSQL", status UNKNOWN, has 1 handler(s) for this
service...


    Handler(s):

      "DEDICATED" established: 1 refused: 0
         LOCAL SERVER




  Service "ORCL" has 1 instance(s).


    Instance "ORCL", status READY, has 1 handler(s) for this service...


      Handler(s):
        "DEDICATED" established: 0 refused: 0 state: ready
           LOCAL SERVER




  The command completed successfully



4.2 Now in the 4.1 the red coloured HSMSQL service is shown as running,


      that means the service is up to listen the queries.



5. CREATING THE INITIALIZATION FILE :-



The following procedure enables you to create the Initializing the
service


by creating and customizing an initialization file for your Generic
Connectivity


agent.



5.1.     Oracle Corporation supplies sample initialization files,
inithsodbc.ora


          for ODBC agents. The sample files are stored in the
\ORACLE_HOME\hs\admin


           directory.



5.2     To create an initialization file for an ODBC, copy the
applicable sample


          initialization file from \ORACLE_HOME\hs\admin  and rename
the file to name


          initHS_SID.ora, where HS_SID is the system identifier you
want to use for the


          instance of the non-Oracle system to which the agent
connects. (Please refer to


          the point 1.6  name will SID  that is if the name was msql
here it would be


          HSMSQL. The suffix HS will be added to name of the systemdsn
name.



          Note: - The HS_SID is also used to identify how to connect to
the agent when you


          Configure the listener by modifying the listener.ora file.



5.3     The HS_SID you add to the listener.ora file must match the
HS_SID in an


          initHS_SID.ora file, because the agent spawned by the
listener searches for a


          matching initHS_SID.ora file. That is how each agent process
gets its initialization


          information.



5.4     When you copy and rename your initHS_SID.ora file, ensure it
remains in the


            \ORACLE_HOME\hs\admin directory.



5.5      Then open the file HS_SID.ora file make the below changes.


       HS_FDS_CONNECT_INFO = HSDSNNAME  odbc_data_source_name
       HS_FDS_TRACE_LEVEL = OFF

        Example: - If the name of the systemdsn was hsmsql then here it
will be:
       HS_FDS_CONNECT_INFO = HSMSQL
       HS_FDS_TRACE_LEVEL = OFF

      Then save the file and exit.


===========================================================


6.	CREATING THE DATABASE LINK TO ACCESS THE TARGET  DATABASE :-




    Now create the database link, here it is assumed that the name of
the systemDSN is


    Hsmsql.



6.1

          sql> create database link hsmsql
            2.  connect to “ms-sql user login” identified by
“ms-sql passwd”
            3.  using ‘hsmsql’;

            Database link created

6.2       To test, run a simple query of a known table on the target


datastore.



            Note :- But before executing this, the table employee
should be created first in


            the MS-SQL server.



            SQL> select * from employee_at_hsmsql;


            EmpoyeeID 	EmployeeName
            ----------
--------------------------------------------------
            1 		Ralph
            2 		Sam
            3 		Monica

            THIS MEANS THAT NOW YOU ARE ABLE TO QUERY THE REMOTE MS-SQL

            DATABASE.

             Note :- If any errors please refer to the APPENDIX B for


the common errors.




7. RESTRICTIONS ON OPERATIONS TO BE CARRIED ON THE REMOTE DATABASE FROM

ORACLE.



On the remote database the DDL (Data Definition Language) statement
cannot be executed.



7.1	Creating the table on the remote database is not possible on a
remote database.

        Example :
        SQL> create table rama_at_hsmsql
           2  (
           3  name varchar,
           4  address varchar,
           5  phone varchar);
          create table rama_at_hsmsql
           *
          ERROR at line 1:
          ORA-02021: DDL operations are not allowed on a remote


database



7.2    Dropping the existing table from remote database not possible.

         Example:
         SQL> drop table emp_at_hsmsql;
         drop table emp_at_hsmsql
          *
         ERROR at line 1:
         ORA-02021: DDL operations are not allowed on a remote database

7.3	Selective queries are not possible on the remote database.
       Example :




SQL> select * from employee_at_hsmsql


  2  where EmpoyeeID='Ralph';


where EmpoyeeID='Ralph'

       *
        ERROR at line 2:
        ORA-00904: "EMPOYEEID": invalid identifier

7.4	 Deletion of the selective rows not possible on the remote


database.


 Example:

        SQL> delete from employee_at_hsmsql
               2  where employeeid=4




   3  ;


where employeeid=4


  *

           ERROR at line 2:
           ORA-00904: "EMPLOYEEID": invalid identifier







7.5   Updation of the selective rows not possible.
SQL> update employee_at_hsmsql


  2  set employeeid=100


  3  where employeeid=1;


where employeeid=1


      *


ERROR at line 3:


ORA-00904: "EMPLOYEEID": invalid identifier




8. OPERATIONS THAT CAN BE CARRIED OUT ON THE REMOTE DATABASE FROM

ORACLE.


8.1	Inserting the new records into the existing table.
Example:-

          SQL> desc emp_at_hsmsql;
                               Name
 Null?    Type
                     ----------------------------------------- --------
----------------------------
                                empno
           VARCHAR2(1)
                                ename
           CHAR(1)

         SQL> select * from emp_at_hsmsql;
                   e e
                   - -
                   1 j

         SQL> insert into emp_at_hsmsql
                2  values ('2','P');

                1 row created.

         SQL> select * from emp_at_hsmsql;

                   e e
                   - -
                   1 j
                   2 P

         SQL> insert into employee_at_HSMSQL values(4,'Ramakrishna');

         1 row created.






8.2    Deletion of the all rows at once in the table is possible, even
it can be rollbacked.


SQL> delete from employee_at_hsmsql;



3 rows deleted.



SQL> SELECT * FROM EMPLOYEE_at_HSMSQL;


no rows selected



SQL> rollback;



Rollback complete.



SQL> SELECT * FROM EMPLOYEE_at_HSMSQL;


 EmpoyeeID EmployeeName

----------  --------------------------------------------------
           1    Ralph
           2    Sam
           3    Monica

            3 rows selected.



===========================================================



8.3 Creating the tables in the oracle from the existing tables on the
remote database. That


      is duplication of tables from MS-SQL to Oracle.



      SQL> select * from employee_at_hsmsql;


       EmpoyeeID EmployeeName
       ---------- --------------------------------------------------
                   1 Ralph
                   2 Sam
                   3 Monica

      3 rows selected.

      SQL> create table employee as select * from employee_at_hsmsql;

      Table created.

      SQL> select * from employee;





 EmpoyeeID EmployeeName

---------- --------------------------------------------------
         1 Ralph
         2 Sam
         3 Monica





3 rows selected.






		                  APPENDIX A

                       (Tables in the MS-SQL server)

A.1   Tables with data created in the MS-SQL Server.
	Example:-

SQL> create table employee (
           EmpoyeeID NUMBER(10),
           EmployeeName VARCHAR2(50)
       );

SQL> insert into employee values(1, 'Ralph');
            SQL> insert into employee values(1, 'Sam');
            SQL> insert into employee values(1, 'Monica');







SQL> create table emp (

           Empno 	VARCHAR2(1),
           Ename  CHAR(1)
           );

SQL> insert into emp values('1', 'j');




SQL> insert into emp values('2', 'P');


SQL> insert into emp values('2', 'P');






                                        APPENDIX B



  (SOME COMMON ERRORS AND THEIR SOLUTINS FOR THE ODBC)

Error (1):-


ORA-28509: unable to establish a connection to non-Oracle system
Help (1):-


Make sure the HOST parameter in the tnsnames.ora file is correct.
Make sure the PORT number is correct.


Make sure the SID name is correct in both the TNSNAMES.ORA and
LISTENER.ORA






Error (2):-


ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:



[Transparent gateway for ODBC][H001] The environment variable
<HS_FDS_CONNECT_INFO> is not set.


Help (2): -


Set HS_FDS_CONNECT_INFO in the hs{sid}init.ora file to the data source
name.


Example: HS_FDS_CONNECT_INFO = <ODBC DataSource Name>
Make sure the hs{sid}init.ora file exists in the ORACLE_HOME/hs/admin
directory and has the same name as the SID in the LISTENER.ORA.
Example: If SID=hsodbc in the listener.ora file, then the
hs{sid}init.ora file would be named ORACLE_HOME/hs/admin/inithsodbc.ora






Error (3):-


ORA-02019: connection description for remote database not found
Help (3):-


Verify that the database link you are using exists.



SQL> select db_link,host,created from user_db_links;



DB_LINK





HOST





CREATED





A.MINISOFT.LOCAL



mscard


22-MAY-03











Error (4): -


ORA-12154: TNS:could not resolve service name
Help (4):-


The name as tried by oracle could not be located in the TNSNAMES.ORA
file. This may be do to a missing entry or incomplete resolution do to
a problem in SQLNET.ORA and the default domain.
Invalid entry in tnsnames.ora, missing right parentheses.





Error (5):-


ORA-28545: NCRO Failed to make RSLV connection
Help (5):-


Verify the hostname in the tnsnames.ora file references your Oracle
server.  This should not reference the HP3000.





Error (6):-


ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:


Help (6):-


[Generic Connectivity Using ODBC][MiniSoft] [3kodbc.dll] In
(NetConnect), error 0 Unknown Error - Connect(): Cannot resolve address
() (SQL State: 08004; SQL Code: 0)



This indicates that the driver was unable to find a server entry in the
DSN or was unable to locate the DSN.



Check that the odbc.ini file referenced by init {sidname}.ora exists
and is readable by the Oracle listener process.





Error(7) with Help(7) :-


If the init {sidname}.ora file cannot be found, the following message
will be seen:



SQL> select * from customer_at_a;


select * from customer_at_a

                       *




ERROR at line 1:


ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:


[Generic Connectivity Using ODBC][H006] The init parameter
is not set. Please set it in init.ora file.
ORA-02063: preceding 2 lines from A











Error (8):-


You have set up your environment using Note#109730.1. You have properly
run caths.sql as sys or internal


But when you try to use your database link that works through HSODBC,
you get the errors:


ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: line preceding HSODBC



Help (8):-




Check if your current sqlnet.ora file contains:
SQLNET.AUTHENTICATION_SERVICES=(NTS)



If it contains this line,


it should be commented out or modified to:
SQLNET.AUTHENTICATION_SERVICES=(NONE)







                                        APPENDIX C



 (Checking for the existence of HSODBC and its libraries).



Execution of the Oracle application "hsodbc" should return a banner. If
you receive an error message, that the application cannot be run or has
a problem with a required library, review your Oracle installation to
ensure all components are correctly installed.



C:\> hsodbc


Oracle Corporation --- MONDAY    AUG 30 2004 11:45:56.599



Heterogeneous Agent Release 10.1.0.2.0 - Production Built with


   Driver for ODBC



Oracle Corporation --- FRIDAY    SEP 26 2003 08:26:47.268



Heterogeneous Agent based on the following module(s):





If a library is missing you may see the following
Note : this can be checked after all the setup is made.



SQL> select * from customer_at_a;


select * from customer_at_a

                       *




ERROR at line 1:


ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from A



Your listener.log file will show:



26-SEP-2003 08:31:13 * (CONNECT_DATA=(SID=mscard)) *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.23.118)(PORT=49349)) * establish *
mscard * 12500


TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12547: TNS:lost contact


  TNS-12560: TNS:protocol adapter error


   TNS-00517: Lost contact


    Windows Error: 32: Broken pipe






 HINTS :



( The TnsNames.ora OR Listener.ora can differ as below:-
TnsNames.ora

\ORACLE_HOME\Net80\tnsnames.ora ( In Oracle 8i)
\OracleHome\Network\tnsnames.ora ( In Oracle 9i)
\Oracle\Product\10.1\Db_1\Network\tnsnames.ora ( In Oracle 10g)


Listener.ora

\ORACLE_HOME\Net80\listener.ora ( In Oracle 8i)
\OracleHome\Network\listener.ora ( In Oracle 9i)
\Oracle\Product\10.1\Db_1\Network\ listener.ora ( In Oracle 10g)


Received on Tue May 24 2005 - 04:03:14 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US