Database link to sql server
I want to connect to SQL SERVER from Oracle.
How can I connect to sql server from oracle and create a database link?
what did the docs say when you looked it up?
look for heterogeneous services
ODBC Driver for sun solaris
Where can I download
. Does Sun Porvide this driver?
ODBC Driver for Unix sun solaris
I wanted to connect to sqlserver and access from unix machine.
Hope this helps...
PURPOSE ------- To outline options for connecting to foreign data stores (non-Oracle databases) from Oracle. SCOPE & APPLICATION ------------------- All users that need to access non-Oracle data from Oracle. Connecting to Foreign Data Stores --------------------------------- There are 3 main options to connect to foreign data stores from Oracle :- - Transparent and Procedural gateways - Generic Connectivity - ODBC direct connectivity using Oracle Call Interface (OCI) or JDBC It is also possible to connect to Oracle from some foreign data stores using Access Managers. Another alternative is to migrate data from the foreign data store into Oracle. 1. Transparent Gateways ----------------------- Oracle Transparent Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Applications can be developed using a consistent Oracle interface for both Oracle and Informix. Transparent gateways are available for the following foreign datastores :- Microsoft SQLServer Sybase Ingres Informix Teradata RMS Rdb EDA/SQL IBM DB2 IBM DB2/400 IBM DRDA compliant databases - for example UDB on Unix platforms All gateways require a separate licence to enable them to be used. They are not included in the licence for the RDBMS. Further information can be found at :- http://metalink.oracle.com - choose Top Tech Docs then Foreign Data Stores then Gateways http://www.oracle.com/gateways/ Documentation for each gateway is available from :- http://otn.oracle.com/documentation/content.html You will need to register if you have not already done so, but this is free. 2. Procedural Gateways ---------------------- Procedural gateways are available for APPC and IBM MQSeries. The Oracle Procedural Gateway for APPC allows users to initiate transaction program execution on remote online transaction processors (OLTPs). The Oracle Procedural Gateway for APPC provides Oracle applications seamless access to IBM mainframe data and services through Remote Procedural Call (RPC) processing. The Procedural Gateway for IBM MQSeries allows Oracle applications to integrate with other MQSeries applications. Oracle applications can send messages to other MQSeries applications or receive messages from them. With the gateway, Oracle applications access IBM MQSeries message queues through remote procedure call (RPC) processing. Both these products require a separate licence in the same way as the transparent gateways. Further information and documentation is available from the same sites as for transparent gateways. 3. Generic Connectivity ----------------------- Generic connectivity allows access to any ODBC or OLE DB data store for which a third party ODBC or OLE DB driver is available, and which meets the standards required by the Oracle software. It uses Heterogeneous Services (HS) which is part of the RDBMS kernel to do most of the processing. Generic connectivity has some limitations and has limited functionality, especially compared to the transparent gateways. However, generic connectivity is free to use, it does not need a separate licence. All that is needed is a third party ODBC or OLE DB driver, as Oracle does not provide these. Generic Connectivity is supported on Solaris, Windows NT/Win2K, HP and AIX. With the release of Oracle 10g Generic Connectivity will be available on Linux. Currently, it is not available on Tru64 or any other platform. Further information can be found at :- http://metalink.oracle.com - choose Top Tech Docs then Foreign Data Stores then Heterogeneous Services http://www.oracle.com/gateways/ http://otn.oracle.com/products/gatew...eways_fov.html - for 10g Documentation for generic connectivity is available from :- http://otn.oracle.com/documentation/content.html The relevant manuals are :- Oracle8i Distributed Database Systems - for 8.1.7 Heterogeneous Connectivity Administrator?s Guide - for 9.0.1 and 9.2 10g documentation will be available at a later date. 4. OCI and JDBC --------------- 4.1 Oracle Call Interface ------------------------- Oracle Call Interface (OCI) is a comprehensive, commercially-available interface addressing all requirements of high performance, secure, scalable middle-tier mission-critical solutions that integrate well with an Oracle server. The OCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library, that can be linked in by the application. Further information can be found at :- http://metalink.oracle.com - Top Tech Docs - Programmatic Interfaces - OCI 4.2 JDBC -------- JDBC can be used to access foreign datastores. Further information can be found at :- http://metalink.oracle.com - Top Tech Docs - Programmatic Interfaces - JDBC However, please note that :- 1) Although Oracle JDBC 2.0 compliant Thin drivers can be used to connect, this is not supported since Oracle does not certify its drivers with third party RDBMS servers. 2) Although third party JDBC drivers can be loaded into the Oracle server to connect to the non-Oracle RDBMS, this configuration is not supported since Oracle does not have access to the third party driver code. 5. Access Managers ------------------ Access Managers allow access to Oracle databases from some foreign datastores. They are available for the AS400 and for CICS on IBM zOS (OS390) mainframes. Oracle Access Manager for AS/400 (AM4AS400) provides AS/400 users with access to data that is residing on any supported Oracle platform via DB2/400 SQL or stored procedures. Oracle Access Manager for AS/400 connects to your Oracle environment through Oracle Net. The connection from your AS/400 to an Oracle server uses TCP/IP. The Access Manager for CICS (AM4CICS) allows CICS applications to issue EXEC SQL statements to an Oracle server, using the precompiler API. Documentation is available from :- http://otn.oracle.com/documentation/content.html Oracle Access Manager Installation and User's Guide for IBM AS/400 - available for 1.3.0 and 9.2 versions. Enterprise Edition System Administration Guide for OS/390 - 8i or 9i - for AM4CICS information 6. Migrating to Oracle ---------------------- There are several options for migrating information in foreign data stores to an Oracle database. 6.1 Oracle Migration Workbench ------------------------------ The Oracle Migration Workbench (OMWB) is a tool that simplifies the process of migrating third-party database systems to the Oracle platform (Oracle8i and Oracle9i). The Migration Workbench migrates the entire database schema, including triggers and stored procedures, in an integrated, environment. The Migration Workbench can currently be used to migrate the following :- Third-Party Database Supported Version -------------------- ----------------- Microsoft SQL Server 6.5, 7.0 and 2000 Microsoft Access 2.0, 95, 97 and 2000 Sybase Adaptive Server 11 and 12 Informix Dynamic Server 7.3 (Production) and 9.1 (Beta) MySQL 3.22 and 3.23 IBM DB2/400 V4R3 and V4R5 IBM DB2 UDB V6, V7.1 and V7.2 (Beta) The product is free and does not need to be licenced. Further information about the product, documentation and the downloadable version are available from :- http://technet.oracle.com/tech/migration/content.html If you are not already registered for OTN/TECHNET then you can do so on the site. 6.2 Migration Toolkits ---------------------- Migration toolkits are also available for some foreign datastores. These are available, along with FAQs and White Papers, at :- http://otn.oracle.com/tech/migration...s/content.html PLEASE NOTE: The software is provided on an as is basis, with no support from Oracle Support Services or the Migration Technology Group. 6.3 Other Options ----------------- If OMWB or the toolkits cannot be used for the migration then other options include a manual migration. This could be done using a transparent gateway or generic connectivity to copy tables, or by loading data into flat files and loading into the Oracle database using SQL*Loader. Oracle Consulting can help you to do this.
Last edited by kris123; 02-28-2005 at 04:06 AM.
HI, Thanks for the information. That was quite help.
We are planning to go with ODBC for solaris. As gateway license cost a lot.
Is ODBC for solaris a free software ? I went through docs and I found that datafirect provides the tool but I need to purchase the tool.
Kindly let any one know if I can get ODBC for solaris free download ?
Subject: Re : ODBC Driver to connect from Oracle 9i (solaris) to MS SQL Server
Oracle does not make any odbc drivers for the unix platforms. Another vendor for this type of odbc driver would be Data Direct.
There are no doubt other vendor who provide them (like Merant).
Last edited by kris123; 02-28-2005 at 09:52 PM.
Click Here to Expand Forum to Full Width